Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

How to get machine name of a user, who is connected on an instance through Remote Desktop and used windows authentication login. I need it for each query she has executed. sys.dm_exec_connections just provide me connectino type i.e. NTLM. And windows event id 4624, gives me all the information i need BUT how to relate this information with my session id in SQL Server ???

share|improve this question

2 Answers 2

This information is not exposed to SQL Server. All SQL Server sees is the hostname of the machine they actually connected from (e.g. the RDP host).

Since they are connected using Windows Authentication, can you not infer from their login name who they are and what machine they are probably connecting from? You can find this information here:

SELECT session_id, login_name
  FROM sys.dm_exec_sessions;

You may be able to go out externally to Active Directory and somehow determine what machine(s) that user is currently logged into, or perhaps get their connection information from the Windows host, I'm not too sure, but you're going well beyond the built-in capabilities of SQL Server at that point, and I have no idea how you would then correlate that information with a specific session_id.

Another alternative would be to - instead of using RDP (assuming you are doing so because the server is in a different domain) - let the user connect via their local SSMS. Here is a way to do that across domains:

http://stackoverflow.com/questions/9671904/connect-to-sql-via-windows-authentification-over-vpn/9672170#9672170

In this case, the sys.dm_exec_sessions.host_name and sys.dm_exec_connections.client_net_address should be very meaningful.

share|improve this answer

I am pretty sure that you can't grab the hostname of the client using T-SQL, it will just return the hostname of the machine that they RDP on to.

In any case, you can provide any information you like for the hostname in the connection so I wouldn't use it for any reliable auditing or security.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.