As a SQL Server administrator, it is important to capture and track failed logins for auditing and reporting purposes. By default, SQL Server logs failed login attempts in the error log. However, this log is recycled periodically, potentially causing the loss of valuable information. In this article, we will discuss how to capture and store failed login events into a table for future analysis.

Creating the FailedLogin Table

First, we need to create a permanent table that will hold the results of our captured failed logins. We can use the following SQL script to create the table:

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
);

Using xp_readerrorlog to Extract Failed Login Information

SQL Server provides an undocumented extended stored procedure called xp_readerrorlog, which allows us to read the contents of the error log. We can leverage this procedure to extract the failed login information before cycling the error log.

The xp_readerrorlog procedure requires three parameters:

• Parameter 1: 0 – Signifies the current log file
• Parameter 2: 1 – Specifies that we want to read the SQL Server error log
• Parameter 3: ‘Login Failed’ – Filters the log entries to only include those with the specified string

Using the information from xp_readerrorlog, we can create a temporary table to store the extracted information. We will then query the temporary table and insert the results into our FailedLogin table.

Here is an example of the SQL script that accomplishes this:

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

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;

Querying the FailedLogin Table

Once the failed login events have been captured and stored in the FailedLogin table, we can easily query the table for reporting purposes. For example, we can retrieve all the failed logins recorded since the error log was last cycled using the following query:

SELECT * FROM dbo.FailedLogin;

This query will return a result set containing the EventID, EventDate, LoginName, and ClientName for each failed login event.

Automating the Process

To ensure that failed login events are captured before the error log is recycled, we can create a SQL Server Agent job that executes the above query as a job step immediately prior to cycling the error log. By scheduling this job to run at regular intervals, we can continuously capture and store failed login events for future analysis.

In conclusion, capturing and tracking failed logins in SQL Server is essential for security and auditing purposes. By leveraging the xp_readerrorlog procedure and storing the results in a dedicated table, administrators can easily report on failed login events and identify potential security threats.