[SQL Server][擴充事件]稽核SQL Server Login Fail

2018-07-06

同事最近想在新版本的SQL Server追蹤異常的SQL登入(SQL Login Fail)，不過同事在舊版本SQL Server都用SQL Trace，來推薦同事改用擴充事件(Extended Event)追蹤登入失敗的錯誤碼18456。

擴充事件適用版本:SQL SERVER 2008以後

建立擴充事件

CREATE EVENT SESSION LoginFail ON SERVER
 ADD EVENT sqlserver.error_reported
 (
   ACTION(
   sqlserver.session_id,
   sqlserver.client_app_name,
   sqlserver.client_hostname,
   sqlserver.nt_username,
   sqlserver.database_id,
   sqlserver.username
   )
    WHERE severity = 14
        AND error_number = 18456
	   AND state >= 7  
	   AND state <= 8
  )
  ADD TARGET package0.asynchronous_file_target
  (
    SET FILENAME = N'C:\SQL\LoginFail.xel',
    METADATAFILE = N'C:\SQL\LoginFail.xem'
  );
GO

ALTER EVENT SESSION LoginFail ON SERVER
  STATE = START;
GO

依照實際需求調整State的條件:

來執行3次異常的登入，錯誤代碼18456

透過SSMS管理工具查詢(GUI)

管理 > 擴充事件 > 工作階段 > LoginFail > 顯示事件 > 就可以查詢到3次登入失敗的來源、使用的帳號等。

透過語法查詢

如果想用語法結合監控工具，也可以，語法如下:

 select 
 convert(xml,event_data).query('/event').value('(event/action[@name="client_hostname"]/value)[1]','nvarchar(100)') as client_hostname,
 convert(xml,event_data).query('/event').value('(event/action[@name="client_app_name"]/value)[1]','nvarchar(100)') as client_app_name,
 convert(xml,event_data).query('/event').value('(event/action[@name="nt_username"]/value)[1]','nvarchar(100)') as nt_username,
 convert(xml,event_data).query('/event').value('(event/data[@name="message"]/value)[1]','nvarchar(400)') as message,
 dateadd(hour,8,timestamp_utc) as logintime
 FROM sys.fn_xe_file_target_read_file( 'C:\SQL\LoginFail*.xel',  'C:\SQL\LoginFail*.xem', NULL, NULL)
  order by logintime desc

3筆異常登入資料