By: Aaron Bertrand | 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:
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
