Some time ago, I wrote an article for SQL Server 2008 to help determine the use of the server since SQL Server 2008 was reaching End Of Life. In that article, I shared a reasonable use of server side trace to capture all of the logon events to the server. Afterall, you have to find out the source of connections and who is using the server if you need to migrate it to a newer SQL Server version. You can read that article here.
Soon after, from various sources, I received requests on how to perform a logon audit using the more preferred, robust, venerable, awesome tool called Extended Events (XEvents). In response, I would share a login audit session to each person. In this article, I will share my login audit solution and give a brief explanation. I use a solution like this on more than 90% of my client servers and I find it highly useful.
Auditing Logons
The Events to capture SQL Server logon/logoff activities were not a part of the original release of XEvents in 2008. The requisite events did not become available until SQL Server 2012. Why mention SQL Server 2008 at all given it has reached its End of Life you may ask? Well, as luck would have it, there are still plenty of 2008/R2 instances out there in the world that are yet to be upgraded still. So, it is useful to continually point it out. This session, and this information, does NOT apply to anything prior to SQL Server 2012.
The events in XEvents that we need happen to be called: sqlserver.login and sqlserver.logout. Here is a little more info on those events along with a query that can retrieve the details being shown here.
SELECT xp.name AS PackageName,xo.name AS EventName, xo.description --, xo.capabilities_desc FROM sys.dm_xe_objects xo INNER JOIN sys.dm_xe_packages xp ON xo.package_guid = xp.guid WHERE xo.name = 'login' OR xo.name = 'logout';
As shown in the preceding image, there are two events of interest that are needed for auditing logon events – sort of. These events are only useful to capture the successful connection (or connection pooling reuse connections) and the logoff events. To take this a step further and capture the failed logins, we can also use an Event that was available in 2008, error_reported. The use of the logout event would be an optional event in this case but could be of use during the troubleshooting under certain circumstances.
Focusing on the login and error_reported events, I have two very useful XEvent Sessions I like to use for my client servers. First part of the audit logon solution is the audit of successful logins with this XEvent Session.
USE master; GO -- Create the Event Session IF EXISTS ( SELECT * FROM sys.server_event_sessions WHERE name = 'SVRLoginAudit' ) DROP EVENT SESSION SVRLoginAudit ON SERVER; GO EXECUTE xp_create_subdir 'C:DatabaseXE'; GO CREATE EVENT SESSION SVRLoginAudit ON SERVER ADD EVENT sqlserver.login (SET collect_database_name = (1) , collect_options_text = (1) ACTION ( sqlserver.sql_text , sqlserver.nt_username , sqlserver.server_principal_name , sqlserver.client_hostname , package0.collect_system_time , package0.event_sequence , sqlserver.database_id , sqlserver.database_name , sqlserver.username , sqlserver.session_nt_username , sqlserver.client_app_name , sqlserver.session_id , sqlserver.context_info , sqlserver.client_connection_id ) ) ADD TARGET package0.event_file (SET filename = N'C:DatabaseXESVRLoginAudit.xel', max_file_size = (5120), max_rollover_files = (4)) WITH ( STARTUP_STATE = OFF , TRACK_CAUSALITY = ON ); /* start the session */ALTER EVENT SESSION SVRLoginAudit ON SERVER STATE = START; GO
Next, I like to use a separate session for the failed logins. This makes it easy to keep the audit files separate and only share the necessary files to others (e.g. only the failed logins instead of giving them both successful and failed which may be way too much information). In addition, I like this method because it makes searching the audit files easier and more focused.
USE master;
GO
-- Create the Event Session
IF EXISTS ( SELECT *
FROM sys.server_event_sessions
WHERE name = 'Audit_FailedLogon' )
DROP EVENT SESSION Audit_FailedLogon
ON SERVER;
GO
EXECUTE xp_create_subdir 'C:DatabaseXE';
GO
CREATE EVENT SESSION [Audit_FailedLogon] ON SERVER
ADD EVENT sqlserver.error_reported (
ACTION (
package0.event_sequence
, sqlserver.client_app_name
, sqlserver.client_hostname
, sqlserver.client_pid
, sqlserver.nt_username
, sqlserver.server_principal_name
, sqlserver.session_nt_username
, sqlserver.transaction_sequence
, sqlserver.username
)
WHERE ([severity]=(14) AND [error_number]=(18456) AND [state]>(1)) )
ADD TARGET package0.event_file
(SET filename = N'C:DatabaseXEAudit_FailedLogon.xel'
)
WITH ( MAX_MEMORY = 4096 KB
,EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS
, MAX_DISPATCH_LATENCY = 3 SECONDS
, MAX_EVENT_SIZE = 0 KB
, MEMORY_PARTITION_MODE = NONE
, TRACK_CAUSALITY = ON
, STARTUP_STATE = ON
);In this second session, I have the predicate defined such that I will only get the failed login events. Funny thing about this, I can often catch 3rd party vendors trying to login to client systems in very odd ways (including putting the password into the user name box which in turn causes the password to be stored in clear text in the error log).
Could these sessions be combined into a single all-purpose session? Sure! That is up to your discretion. I prefer the separate sessions for ease of management and review.
The Extra Mile
Let’s say we wanted to go ahead and add the logout event to our session, we can then proceed with a slight modification to the successful login session so that it would look something like the following.
USE master; GO -- Create the Event Session IF EXISTS ( SELECT * FROM sys.server_event_sessions WHERE name = 'SVRLoginOutAudit' ) DROP EVENT SESSION SVRLoginOutAudit ON SERVER; GO EXECUTE xp_create_subdir 'C:DatabaseXE'; GO CREATE EVENT SESSION SVRLoginOutAudit ON SERVER ADD EVENT sqlserver.login (SET collect_database_name = (1) , collect_options_text = (1) ACTION ( sqlserver.sql_text , sqlserver.nt_username , sqlserver.server_principal_name , sqlserver.client_hostname , package0.collect_system_time , package0.event_sequence , sqlserver.database_id , sqlserver.database_name , sqlserver.username , sqlserver.session_nt_username , sqlserver.client_app_name , sqlserver.session_id , sqlserver.context_info , sqlserver.client_connection_id ) ), ADD EVENT sqlserver.logout ( ACTION ( sqlserver.sql_text , sqlserver.nt_username , sqlserver.server_principal_name , sqlserver.client_hostname , package0.collect_system_time , package0.event_sequence , sqlserver.database_id , sqlserver.database_name , sqlserver.username , sqlserver.session_nt_username , sqlserver.client_app_name , sqlserver.session_id , sqlserver.context_info , sqlserver.client_connection_id ) ) ADD TARGET package0.event_file (SET filename = N'C:DatabaseXESVRLoginAudit.xel', max_file_size = (5120), max_rollover_files = (4)) WITH ( STARTUP_STATE = OFF , TRACK_CAUSALITY = ON ); /* start the session */ALTER EVENT SESSION SVRLoginOutAudit ON SERVER STATE = START; GO
And, to show what might occur with this session, I can see data similar to this for the logout event.
See how easy that is!
The Wrap
This article has shown how to audit the logon events for SQL Server 2012 and beyond through the use of XEvents. It also happens to be an excellent follow up to another recent article – here. An essential function of any DBA is to have a clear understanding of the activity (logons) that is occurring on the servers. This audit session will help you do exactly that!
Through the power of XEvents, we can accomplish a great many things and reach extensive insights into our database footprint. This post is just one of many in the XE Series, of which you can read more – here.
In addition, this post is similar to many in the back to basics series. Other topics in the series include (but are not limited to): Backups, backup history and user logins.