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

By: Aaron Bertrand   |   Updated: 2014-04-17   |   Comments (3)   |   Related: > Auditing and Compliance

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

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

• Set up an Extended Events session so that you can track down the application that is causing login failures.
• See these other tips and resources:
  • Getting Started with Extended Events in SQL Server 2008
  • Extended Events Tips
  • Jonathan Kehayias' Extended Events posts on SQLskills.com
  • An XEvent A Day: 31 days of Extended Events

About the author

Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and also blogs at sqlblog.org.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips