Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Use Extended Events to Get More Information About failed SQL Server Login Attempts


By:   |   Read Comments (3)   |   Related Tips: More > Auditing and Compliance

Attend our free MSSQLTips Webcast - How to Simplify Routine SQL Server Administration Tasks


Problem

You may have cases where an ancient application is using an old login or the wrong password. SQL Server is great about auditing failed logins and recording that they happened; it is not so great, however, at providing enough information to locate them. For example, the event log does not record the application that attempted to log in; I know in a lot of cases just knowing the server name is not going to be enough.

Solution

There are several solutions you would think to use, but that won't necessarily work very well:

  • The default trace does not reliably catch all login failures, and can also be turned off;
  • SQL Server Audit claims to log application name, but didn't in my tests; also, this is not available across all editions and all versions;
  • logon triggers sound promising, but they don't fire until a login attempt has actually succeeded; and,
  • your own server-side trace can capture this information, but trace and profiler are both deprecated, meaning you should not use this approach moving forward.

A better alternative is Extended Events. In SQL Server 2008 and 2008 R2 you can easily set up an XE session to capture all errors of Severity 14. In SQL Server 2012 and above, you can hone in directly on error number 18456. (I've blogged about many of the possible reasons for this error, and potential workarounds.)

In 2008 / 2008 R2, you can set up the following session:

CREATE EVENT SESSION FailedLogins
ON SERVER
 ADD EVENT sqlserver.error_reported
 (
   ACTION 
   (
     sqlserver.client_app_name,
     sqlserver.client_hostname,
     sqlserver.nt_username
    )
    WHERE severity = 14
      AND state > 1 -- removes redundant state 1 event
  )
  ADD TARGET package0.asynchronous_file_target
  (
    SET FILENAME = N'C:\temp\FailedLogins.xel',
    METADATAFILE = N'C:\temp\FailedLogins.xem'
  );
GO

ALTER EVENT SESSION FailedLogins ON SERVER
  STATE = START;
GO

Then you can poll the session data periodically using the following query:

;WITH event_data AS 
(
  SELECT data = CONVERT(XML, event_data)
    FROM sys.fn_xe_file_target_read_file(
   'C:\temp\FailedLogins*.xel', 
   'C:\temp\FailedLogins*.xem', 
   NULL, NULL
 )
),
tabular AS
(
  SELECT 
    [host] = data.value('(event/action[@name="client_hostname"]/value)[1]','nvarchar(4000)'),
    [app] = data.value('(event/action[@name="client_app_name"]/value)[1]','nvarchar(4000)'),
    [date/time] = data.value('(event/@timestamp)[1]','datetime2'),
    [error] = data.value('(event/data[@name="error_number"]/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 [host],[app],[state],[message],[date/time]
  FROM tabular
  WHERE error = 18456 
  ORDER BY [date/time] DESC;

You may have to filter out some false positives here (there may be other Severity 14 errors that are not login failures).

Starting with SQL Server 2012, you can change one line to get a much more targeted session:

CREATE EVENT SESSION FailedLogins
ON SERVER
 ADD EVENT sqlserver.error_reported
 (
   ACTION 
   (
     sqlserver.client_app_name,
     sqlserver.client_hostname,
     sqlserver.nt_username
    )
    WHERE severity = 14

    -- added this line:
      AND error_number = 18456

      AND state > 1 -- removes redundant state 1 event
  )
  ADD TARGET package0.asynchronous_file_target
  (
    SET FILENAME = N'C:\temp\FailedLogins.xel',
    METADATAFILE = N'C:\temp\FailedLogins.xem'
  );
GO

ALTER EVENT SESSION FailedLogins ON SERVER
  STATE = START;
GO

In these versions, you can also view the live data in the UI, so that you don't have to use any of that ugly XQuery code to review the results:

Context menu option for viewing live session data
Context menu option for viewing live session data
Viewing live session data
Viewing live session data

Note that there are some login failures that won't be caught here either; for example, if you try to open a second Dedicated Administrator Connection, this is blocked long before the XE session will ever pick it up. Still, it can often be a very useful way to collect information about normal login failures that other methods don't provide.

Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Aaron Bertrand Aaron Bertrand is a Senior Consultant at SQL Sentry, Inc., and has been contributing to the community for about two decades, first earning the Microsoft MVP award in 1997.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Tuesday, November 10, 2015 - 10:58:25 AM - steve hendricks Back To Top

I'm using SQL Server 2008 R2. The "error_number" field in the XML is listed as "error". Once I made this edit, the code worked like gangbusters. Woo-hoo!!


Wednesday, July 30, 2014 - 4:06:57 PM - Sri Back To Top

Awesomely written. Nice an simple.. Good startin point towards XE use cases....


Tuesday, July 22, 2014 - 10:25:50 PM - Rory Mac Back To Top

 

Hi Aaron,

 

Great article, how would I implement this for successful logins?

 

Thanks

Rory


Learn more about SQL Server tools