0

I need to setup auditing (logging) of successful logins on SQL Server (date, time, who), not for all logins on a server, but only for a small group of logins (developers)

In short, me only interested in logging/auditing developer logins, not application logins

Tried using SQL Server audit feature, using SUCCESSFUL_LOGIN_GROUP, but it does not allow me to select "principal name" and specify logins that are interesting to me

Is there a workaround ? I need only certain logins to be audited, otherwise it will be millions of records in the audit file if I audit every successful login of every application/user

  • What version of SQL Server? – Tony Hinkle Aug 2 '18 at 0:38
2

You can do this with audit objects with SQL Server 2012 and later by using a filter to specify the principals you want to audit. The only downside is that you can't specify a group or a role, you have to include each login.

USE [master]
GO

/****** Object:  Audit [sysadmin_successful_logon] ******/
CREATE SERVER AUDIT [developer_successful_logon]
TO APPLICATION_LOG
WHERE ([server_principal_name]='domain\dev01' OR 
    [server_principal_name]='domain\dev02' OR
    [server_principal_name]='domain\dev03')
ALTER SERVER AUDIT [developer_successful_logon] WITH (STATE = ON)
GO

CREATE SERVER AUDIT SPECIFICATION [developer_successful_logon_spec]
FOR SERVER AUDIT [developer_successful_logon]
ADD (SUCCESSFUL_LOGIN_GROUP)
WITH (STATE = ON)
GO
  • Thank you for valuable comment! That worked. One thing that I do not understand though, that according to audit log, my login keeps reconnecting ("login succeeded") each minute under different session_ids (program name = Microsoft SQL Server Management Studio, client_interface_name = .Net SqlClient Data Provider). What is going on ? This activity "spams" audit log, creating too many records – aleksey vitsko Aug 2 '18 at 13:21
  • I don't have an answer for that--or even a good theory. – Tony Hinkle Aug 2 '18 at 13:49
2

I don't like suggesting this, but it's a valid use case, however Logon Triggers can do this. Do note that this isn't free and can cause logon issues, login issues, and a myriad of other problems under high concurrency.

If you're going to do this, put the logging tables in master and move the data over to a different database at load logon times. If you choose to put the logging table or area in another database, logins may fail under another set of myriad circumstances.

You could additionally choose to purchase 3rd party software products that can also do this or feel free to write your own.

1

I have been through this pain few years back for SoX auditing and had to exclude applications logins too. I have done this through filtered server side trace. I didn’t notice any performance impact but you’d have check your case.

This should give an idea and if not, I’ll try to dig out the code for you tomorrow (2am here now):

https://www.mssqltips.com/sqlservertip/4721/audit-sql-server-logins-using-sql-profiler/

Edit: this has got me thinking that these days we should look into extended events: Extended Events filtering

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.