5

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?

3

You can use DMVs to see who's logged in at the time, but this won't get you any historic information.

SELECT c.session_id AS spid, c.auth_scheme, s.login_name,
       s.[host_name], c.client_net_address
FROM sys.dm_exec_connections AS c
INNER JOIN sys.dm_exec_sessions AS s ON c.session_id=s.session_id;

I suppose you could use this code in a logon trigger and save the information to a table, but if you need a running log, you may want to look at Extended Events or running a trace (SQL Server Profiler).

7

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.

3

You can query sys.dm_exec_sessions to find out the host name of the machine that is connecting to your SQL server instance with the application account. If you can track back the host name to an user machine, you should be able to determine whoo is using that account:

 SELECT  host_name ,
    program_name
FROM       sys.dm_exec_sessions
WHERE   login_name = 'service account'

If needed you can make a snapshot of this table every 15 minutes, however, the session_id is likely to remain active for some time unless the (ab)user disconnects directly after running a query.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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