locked
How to force client application to pass connection information. RRS feed

  • Question

  • Recently we run this query: "select * from sys.dm_exec_sessions", and we found some of entries for client connection do not include OS USER, Machine Name, and Program Name.

     

    Does anyone know how to force client connection to pass these information to the server?

     

     

    Thanks

    Friday, August 1, 2008 6:25 PM

All replies

  • Hi,

    Welcome to MSDN Smile

    SPID's 1 to 50 are allocated as system SPID's and it won't have OS USER, Machine Name, and Program Name details included. If the SPID you are checking is above 50 then it should atleast have loginname details, this column will display SQL logins too. 
    Saturday, August 2, 2008 2:38 AM
  • These sessions are not system sessions. They are client sessions from remote machine. We saw these:

     

    57 7/31/2008 248 4 ODBC 0x1CB6BB83EC3B7E41A47F0D2B4EAA73D9 sysdba sleeping 0x 470 2 496 1416 4 17:31.3 17:31.3 231 21 26243 1 2.147E+09 us_english mdy 7 0 0 1 0 0 0 0 0 4 -1 0 1 0 0x1CB6BB83EC3B7E41A47F0D2B4EAA73D9 sysdba NULL NULL NULL
    58 7/29/2008 344 4 ODBC 0x1CB6BB83EC3B7E41A47F0D2B4EAA73D9 sysdba sleeping 0x 330 2 342 783 4 51:32.1 51:32.1 82 0 11815 1 2.147E+09 us_english mdy 7 0 0 1 0 0 0 0 0 2 -1 0 0 0 0x1CB6BB83EC3B7E41A47F0D2B4EAA73D9 sysdba NULL NULL NULL

     

    Missing host_name, program_name, Nt_domain, NT_user_name

     

     

    Is there a way for us to force client provide these information?

     

    Thanks.

    Monday, August 4, 2008 4:10 PM
  • SPID's 57 & 58 are logged in with SQL authenticated ID "sysdba" and not with Windows authenticated ID, hence you won't get Nt_domain & NT_User_name details. If the program has a name it will surely display in program_name column else it won't. 



    I'm not sure about missing host_name. If you have enabled success & failed login in login auditing under server properties, then you can check error log for the client machine IP address logged in with sysdba, the message in errorlog will be like 


    Login succeeded for user 'username'. [CLIENT: ipaddress]



    With the help of the IP address you can find out the host_name.
    Tuesday, August 5, 2008 1:26 AM