Login Audit Using Extended Events in SQL Server

Introduction

Often banking, financial or any sensitive data organization will receive a request to audit the logins that connect to the SQL server. In this post, I’ll overview how to use extended events to capture both successful and failed logins with additional details.

We all know by default that SQL server error logs capture failed login information, and if we want to capture successful logins as well, we can change the default settings on the SQL server to capture both successful and failed login details.

However, the captured details on the SQL logs will have only limited information. We may need additional information, like client name, login, etc., while troubleshooting any login failures or sending a login audit report to customers.

This overview uses the extended event for login audit. In SQL server, extended events is a lightweight performance monitoring system with the least possible SQL server resource consumption for collecting the data needed to monitor and diagnose problems.

Extended event session to capture the login audit:

The chart below shows the error numbers and severity we picked based on the text column that looked something like the related success or failed logins and we added it to the login audit script.

We also added few filters, like not to capture for the system databases, session IDs greater than 50, SQL agent related jobs, replication and SSMS intelligence. We can also add more filters based on the requirements.

Script to create an event session that captures both successful and failed logins :

Copy this script and execute on SQL instance to create event session and change the output file name, max_file_size and max_rollover_files based on the requirement.

CREATE EVENT SESSION [LoginAudit_Success_Failed] ON SERVER

ADD EVENT sqlserver.error_reported(

    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.nt_username,sqlserver.server_instance_name,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.session_server_principal_name,sqlserver.username)

    WHERE (([package0].[equal_int64]([severity],(20))

              OR [package0].[equal_int64]([severity],(14))

              OR [package0].[equal_int64]([severity],(16))

              OR [package0].[equal_int64]([severity],(10))

              AND NOT [sqlserver].[like_i_sql_unicode_string]([sqlserver].[client_app_name],N'%Transact-SQL%IntelliSense'))

              AND ([package0].[equal_int64]([error_number],(17197)) OR [package0].[equal_int64]([error_number],(18401))

              OR [package0].[equal_int64]([error_number],(18451))

              OR [package0].[equal_int64]([error_number],(18452))

              OR [package0].[equal_int64]([error_number],(18456))

              OR [package0].[equal_int64]([error_number],(18458))

              OR [package0].[equal_int64]([error_number],(18459))

              OR [package0].[equal_int64]([error_number],(18460))

              OR [package0].[equal_int64]([error_number],(18461))

              OR [package0].[equal_int64]([error_number],(18470))

              OR [package0].[equal_int64]([error_number],(18486))

              OR [package0].[equal_int64]([error_number],(18487))

              OR [package0].[equal_int64]([error_number],(18488))

              OR [package0].[equal_int64]([error_number],(17828))

              OR [package0].[equal_int64]([error_number],(17832))

              OR [package0].[equal_int64]([error_number],(17897))

              OR [package0].[equal_int64]([error_number],(33147))

              OR [package0].[equal_int64]([error_number],(18453))

              OR [package0].[equal_int64]([error_number],(18454))

              OR [package0].[equal_int64]([error_number],(18455))

              OR [package0].[equal_int64]([error_number],(28046))

              OR [package0].[equal_int64]([error_number],(5701))

              AND [database_id]>(4)

              AND [package0].[greater_than_uint64]([sqlserver].[session_id],(50))

              AND (NOT [sqlserver].[like_i_sql_unicode_string]([sqlserver].[client_app_name],N'%Replication%')

              AND (NOT [sqlserver].[like_i_sql_unicode_string]([sqlserver].[client_app_name],N'%SQLAgent%')

              AND (NOT [sqlserver].[like_i_sql_unicode_string]([sqlserver].[client_app_name],N'%Repl-LogReader%')

              AND NOT [sqlserver].[like_i_sql_unicode_string]([sqlserver].[client_app_name],N'%Transact-SQL%IntelliSense')))))))

ADD TARGET package0.event_file(SET filename=N'C:\Audit\LoginAudit_Success_Failed.xel',max_file_size=(10),max_rollover_files=(10))

WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)

GO

Script to read the login audit event output XEL files:

;WITH event_data AS

(

SELECT data = CONVERT(XML, event_data)

FROM sys.fn_xe_file_target_read_file

('C:\Audit\LoginAudit_Success_Failed*.xel', default, NULL, NULL)

),

tabular AS

(

SELECT

[timestamp] = data.value('(event/@timestamp)[1]','varchar(30)'),

[client_hostname] = data.value('(event/action[@name="client_hostname"]/value)[1]','nvarchar(4000)'),

[client_app_name] = data.value('(event/action[@name="client_app_name"]/value)[1]','nvarchar(4000)'),

[username] = data.value('(event/action[@name="username"]/value)[1]','nvarchar(4000)'),

[nt_username] = data.value('(event/action[@name="nt_username"]/value)[1]','nvarchar(4000)'),

[database_id] = isnull(data.value('(event/data[@name="database_id"]/value)[1]','int'),data.value('(event/action[@name="database_id"]/value)[1]','int')),

[database_name] = isnull(DB_NAME(data.value('(event/data[@name="database_id"]/value)[1]','int')),DB_NAME(data.value('(event/action[@name="database_id"]/value)[1]','int'))),

[server_instance_name] = data.value('(event/action[@name="server_instance_name"]/value)[1]','nvarchar(4000)'),

[server_principal_name] = data.value('(event/action[@name="server_principal_name"]/value)[1]','nvarchar(4000)'),

[session_server_principal_name] = data.value('(event/action[@name="session_server_principal_name"]/value)[1]','nvarchar(4000)'),

[session_id] = data.value('(event/action[@name="session_id"]/value)[1]','int'),

[error_number] = data.value('(event/data[@name="error_number"]/value)[1]','int'),

[severity] = data.value('(event/data[@name="severity"]/value)[1]','int'),

[state] = data.value('(event/data[@name="state"]/value)[1]','tinyint'),

[message] = data.value('(event/data[@name="message"]/value)[1]','nvarchar(250)')

FROM event_data

)

SELECT *

FROM tabular AS t

ORDER BY [timestamp] DESC