How to Audit Successful Logins for certain logins only?

Question

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

Answer 1

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

Answer 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.

Answer 3

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