August 18, 2016 at 4:11 am
Morning All,
I have a problem whereby I need to identify the user logged in indirectly to a SQL Server.
By indirectly what I mean is the user logs on to their desktop. Launches an application that connects to a SQL Server via a Service Account; not via the AD user they logged in as. The usual sp_who2 only shows the hostname (app server) and service account that has connected.
Does anyone know of a way I can find the actual user behind this?
Cheers,
Alex
August 18, 2016 at 6:30 am
Hi,
I don't think you can get the domain user name, but you can get its IP by looking to sys.dm_exec_connections (client_net_address).
Nicolas.
August 18, 2016 at 6:36 am
Unfortunately that is giving the IP of the Application Server running the Service.
It's 3 tiers, User -> AppServer -> SqlServer
I can't get back to either domain user or the PC on their desktop.
August 18, 2016 at 6:52 am
- Sometimes (usually not) an application will populate the Program property of the connection with something like the user's login name, so when qyerying the SQL Server connections DMV, examine this column closely for any type of distinctive coding beyond just the application name.
- Also, sometimes when an application is making stored procedure calls, it will pass the user's app or domain login name as an input parameter, and this too would show up in a T-SQL event profile trace.
- If none of the above, you may need to monitor network connections on the application server.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
August 18, 2016 at 7:55 am
Using extended events, you can capture all the connection information and suss out if there is any information that might lead to the individual user in that way. However, the likelihood here is that you'll have to monitor through the application. There's no way I'm aware of to do otherwise.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 19, 2016 at 2:39 am
Thanks all for the replies, as some new info has come to light, I think this situation can only lead to dead ends:
Multiple users login from the same desktop in satellite offices.
Some satellite offices use the logins of other satellite offices.
Citrix clients are skewing their actual locations.
In short its all a bit of a dogs-dinner.
Cheers all for your help!
Alex
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply