Who is the person using a SQL login?

Is it possible to find out who used a SQL-Server login?

Let's assume we suspect a person to abuse a SQL login that is supposed to be used by an application only to logon to SQL-Server. Is the domain user who used the SQL login logged anywhere, so that we can make a conclusion who used it?

No, if the user connects to SQL Server using a SQL Authentication login, there is no way to determine from that which Windows login was responsible. SQL Server can only record the information it has been provided, and when you use SQL Authentication, no Windows login / domain / group information is passed to SQL Server.

You can look at DMVs like sys.dm_exec_sessions and sys.dm_exec_connections to get other supporting information, such as IP address and host name, but:

• You can only do this going forward, you can't look back in time unless you were specifically collecting this information (you can do this now with a server-side trace, or a logon trigger, or audit, or extended events, or...)

• This will only help if the user connects directly to SQL Server from their workstation. If your users use a jump box in the data center or have common access to other machines, you'll be able to tell which machine they came from, but not which Windows user was logged into the box at the time. If you know exactly when the change happened in SQL Server, you might be able to correlate that time with the logs on the machine to determine which user it was.

You can't even prevent users from connecting as this SQL Authentication login unless you change the application to use Windows Auth and disable SQL Auth altogether. Sure, you could set up a logon trigger that prevents logging in unless host_name and/or program_name are within a desired set of values, but these things are very easy to spoof, so if this behavior is malicious, your options are quite limited.

StackExchange Database Administrators Q#127477, answer score: 7