Automate collection and saving of failed logins for SQL Server

By: K. Brian Kelley   |   Comments (3)   |   Related: > Auditing and Compliance

Problem

I re-cycle my SQL Server log every night using sp_cycle_errorlog. However, before I do, I would like to capture all of the failed logins recorded. I have auditing turned on for failed logins, but I want to make sure that I capture those events into a table so I can report on. How can I do this?

Solution

There is an undocumented extended stored procedure called xp_readerrorlog which will do the trick. How to use it for SQL Server 2005/2008 was covered in a previous tip. We can use xp_readerrorlog, in combination with a temporary table to extract the login failure information before cycling the error log. First, we need to create the permanent table which will hold our results:

CREATE TABLE dbo.FailedLogin ( 
   EventID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED, 
   EventDate DATETIME NOT NULL, 
   LoginName sysname NOT NULL, 
   ClientName NVARCHAR(255) NULL 
); 
GO 

Then, using the information on xp_readerrorlog, we'll need to specify the first 3 parameters. The values we'll use are:

• Parameter 1: 0 - Signifies the current log file
• Parameter 2: 1 - Tells SQL Server we want the SQL Server error log, not the SQL Server Agent log.
• Parameter 3: 'Login Failed' - Tells SQL Server we're looking for log entries with this string in them.

The extended stored procedure will return three columns which we must account for. They are:

• LogDate  - When the event occurred.
• ProcessInfo - What generated the event.
• Text - The logged event itself.

To handle the incoming information, we'll put the information into a temporary table. We'll query from the temporary table and insert the results into our FailedLogin table that we've previously created. That query will look a bit convoluted because we'll need to do string manipulation to get just the login name and the client name. Note that while I'm using the parameter for xp_readerrorlog which should filter for Login Failed, I'm still going to qualify the SELECT from the temporary table to be sure. Putting it all together, we get the following results:

CREATE TABLE #ErrorLog ( 
  LogDate DATETIME, 
  ProcessInfo NVARCHAR(255), 
  LogText NVARCHAR(MAX) 
); 
GO 

INSERT INTO #ErrorLog ( 
   [LogDate], 
   [ProcessInfo], 
   [LogText] 
)  
EXEC xp_readerrorlog 0, 1, 'Login Failed'; 

INSERT INTO dbo.FailedLogin 
(EventDate, LoginName, ClientName) 
SELECT LogDate,  
  SUBSTRING(LogText, CHARINDEX('''' , LogText) + 1, CHARINDEX('''', LogText, CHARINDEX('''' , LogText) + 1) - CHARINDEX('''' , LogText) - 1) AS LoginName, 
  SUBSTRING(LogText, CHARINDEX('[CLIENT', LogText) + 9, CHARINDEX(']', LogText) - CHARINDEX('[CLIENT', LogText) - 9)  AS ClientName 
FROM #ErrorLog 
WHERE ProcessInfo = 'Logon' 
  AND CHARINDEX('Login failed', LogText) > 0; 

DROP TABLE #ErrorLog; 
GO 

And then if we query our FailedLogin table, we'll see all the failed logins recorded since the error log was previously cycled. An example is shown below:

Therefore, since we can extract the information correctly out of the SQL Server log, we can use the query above and put it as a job step immediately prior to cycling the error log. Then it's a simple matter of querying FailedLogin for any reporting which we need to do.

Next Steps

• Download the complete code
• Review these prior security tips
• Turn this into a stored procedure and call as needed.
• Modify so this can be run at any time and not create duplicate entries

About the author

K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

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

View all my tips