Post reply

Username of client app - SQL authentication

  • November 11, 2005 at 3:02 am

    #89895

    HI

    I have an client app which connects to SQL server Server via SQL Authentication. This Client app is developed by a third party and so we dont have the code. Now i have a trigger on a table which writes changes to another table - this is for auditing purposes. Now i need to know who made the change from the client app.

    But if i use any of the SQL functions(SUSER-SNAME(), SYSTEM_USER, CURRENT_USER, USER() etc etc) it gives me NT AUTHORITY\SYSTEM. If i use HOST_NAME() then it gives me the name of the server and not the client.

    How would i get the name of the person logged into the client machine? or even the name of the client machine? any suggestions?

    Does anybody know how the table sysprocesses gets populated or which Stored procedure is used to populate it?

    All help will be appreciated

    Regards,

    Scholes

  • November 14, 2005 at 8:00 am

    This was removed by the editor as SPAM

  • November 15, 2005 at 5:53 am

    #603872

    How do you know the application is using SQL Authentication?

  • November 15, 2005 at 10:22 am

    #603985

    looks like your client app is not using SQL authentication and instead you have something runing as local system account ( a service, maybe) which is the one that is login in.

    to prove it just make sure your WINDOWS credentials is an SA in SQL Server and remove the  BUILTIN\Administrators account

    Try to use the app and let us know what happen

     


    * Noel

  • November 16, 2005 at 1:03 am

    #604100

    I think not client directly connect to DB Server. It may be a service or some middleware in an application server do. The application Server and DB server are either the same server or one that has trusted connection with DB Server. The Username/Pasword entered at client may not be SQL Server login so you can not use SQL functions such as SYSTEM_USER or HOST_NAME... to get information you want.

  • November 17, 2005 at 1:07 am

    #604358

    Hi

    if i delete BUILTIN\ADMINISTRATORS then the app does does not work. It gives an error. So what does this mean??? will i ever be able to get the usrname???? any workarounds?

  • November 17, 2005 at 1:42 am

    #604361

    The way I see it, you have an application that is logging into the server using a single account (Admin Account), which means all users of the application use the same SQL account.

    Effectively, this means your application is managing security based on... we don't know what, but unless your application passes the logged on user name into SQL, your are not going to be able to differentiate one user from another from within SQl.

     

  • November 17, 2005 at 12:42 pm

    #604508

    As already pointed out this proves two things:

    1. The local service installed by the application is using one and only one login for all users

    2. That login has sysadmin credentials

    If you can not workout a solution with the VENDOR (third party app) there is nothing you can do. at least without hacking the back end app but you probably don't want to do that anyways

     


    * Noel

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply