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 ???
asked Jan 10, 2013 at 15:41
Add a comment
|
2 Answers
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:
In this case, the sys.dm_exec_sessions.host_name and sys.dm_exec_connections.client_net_address should be very meaningful.
answered Jan 10, 2013 at 16:03
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.
