Automate collection and saving of failed logins for SQL Server
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?
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.
- 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
View all my tips