Automate collection and saving of failed logins for SQL Server

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:

Output from FailedLogin table

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

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *