Problem
You are a SQL DBA responsible for many instances of SQL Server running many versions of SQL Server from 2005 to 2016. You get a requirement from the IT security department to get a list of all failed login attempts from last week from all servers. You need a consistent method to capture this data from all of these servers and in this tip we will cover one approach.
Solution
The method for solving this problem involves a stored procedure that will be compiled and executed on the master database of each SQL Server instance.
The stored procedure declares two table variables @EnumErrorLogs and @ErrorLogInfo. The first, @EnumErrorLogs will store the list of the SQL Server error log values like; archive number, change date and file size. The second, @ErrorLogInfo will store the message lines from the logs.
We will use the system stored procedure sp_enumerrorlogs to fill the first table and then we will loop over each of these files using sp_readerrorlog system stored procedure to fill the second table. We will then filter this data to find login information and specifically where there were failures for the last 7 days.
Code to Find Failed Logins
If you want to go back further than 7 days, you change the 7 values below to match your needs.
CREATE PROC usp_GetFailedLoginsListFromLastWeek
AS
BEGIN
SET NOCOUNT ON
DECLARE @ErrorLogCount INT
DECLARE @LastLogDate DATETIME
DECLARE @ErrorLogInfo TABLE (
LogDate DATETIME
,ProcessInfo NVARCHAR (50)
,[Text] NVARCHAR (MAX)
)
DECLARE @EnumErrorLogs TABLE (
[Archive#] INT
,[Date] DATETIME
,LogFileSizeMB INT
)
INSERT INTO @EnumErrorLogs
EXEC sp_enumerrorlogs
SELECT @ErrorLogCount = MIN([Archive#]), @LastLogDate = MAX([Date])
FROM @EnumErrorLogs
WHILE @ErrorLogCount IS NOT NULL
BEGIN
INSERT INTO @ErrorLogInfo
EXEC sp_readerrorlog @ErrorLogCount
SELECT @ErrorLogCount = MIN([Archive#]), @LastLogDate = MAX([Date])
FROM @EnumErrorLogs
WHERE [Archive#] > @ErrorLogCount
AND @LastLogDate > getdate() - 7
END
-- List all last week failed logins count of attempts and the Login failure message
SELECT COUNT (TEXT) AS NumberOfAttempts, TEXT AS Details, MIN(LogDate) as MinLogDate, MAX(LogDate) as MaxLogDate
FROM @ErrorLogInfo
WHERE ProcessInfo = 'Logon'
AND TEXT LIKE '%fail%'
AND LogDate > getdate() - 7
GROUP BY TEXT
ORDER BY NumberOfAttempts DESC
SET NOCOUNT OFF
END
Example of how to use the stored procedure
We execute the procedure:
exec usp_GetFailedLoginsListFromLastWeek
We get the following failed logins from last week list (as it was recorded on my server)
NumberOfAttempts Details ---------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 3 Login failed for user 'NT SERVICE\ReportServer$SQL2K14'. Reason: Failed to open the explicitly specified database 'ReportServer$SQL2K14'. [CLIENT: <local machine>] 3 Login failed for user 'NT SERVICE\ReportServer$SQL2K14'. Reason: Failed to open the explicitly specified database 'ReportServer$SQL2K14'. [CLIENT: 10.1.22.32] 2 Login failed for user '222'. Reason: Could not find a login matching the name provided. [CLIENT: <local machine>] 1 Login failed for user '222222'. Reason: Could not find a login matching the name provided. [CLIENT: <local machine>] 1 Login failed for user '11'. Reason: Could not find a login matching the name provided. [CLIENT: <local machine>] 1 Login failed for user '111'. Reason: Could not find a login matching the name provided. [CLIENT: <local machine>]
Repeat these steps on all reported SQL Servers.
SQL Server Setting to Capture Failed Logins
Make sure the server security auditing property is set to monitor Failed Logins only or Both Failed and Successful Logins.
To set for just “Failed Logins” do the following:
USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'AuditLevel',
REG_DWORD, 2
GO
For both “Failed and Successful Logins” do the following:
USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'AuditLevel',
REG_DWORD, 3
GO
These settings can also be changed in SSMS, by right clicking on the instance name, select Properties and go to the Security page. These options are under the Login Auditing section on this page.
Next Steps
- If you liked this solution you can compile and use this procedure in order to report failed logins.
- The procedure was tested on SQL Server 2014 Standard Edition, but should work for SQL Server 2005 and later.
Eli Leiba is a senior application DBA at Israel Electric Company, a teacher at Microsoft CTEC, and a senior database consultant. With 19 years of experience working with both SQL Server and Oracle RDBMS. He is certified in Oracle and SQL Server database administration and implementation and has a B.S. in Computer Science. He can be reached at: iecdba@hotmail.com.
- MSSQLTips Awards: Rising Star (50+ tips) – 2019 | Author of the Year Contender – 2016-2019
