Obtaining machine names for failed logins

The SQL Server error log contains invaluable details about failed logins, but only if you enable login auditing. Once enabled, the error log will provide the details of logins that failed including the name of the login, the IP address of the machine where the login originated, and the cause of the failure.

The code below analyzes the SQL Server error logs for messages relating to failed logins, and obtains the DNS machine name for the computers where the failed logins originated.

SET NOCOUNT ON;

IF OBJECT_ID('tempdb..#mverrlog') IS NULL
CREATE TABLE #mverrlog
(
	ErrorLogFileNum INT NULL
	, LogDate DATETIME
	, ProcessInfo VARCHAR(255)
	, [Text] VARCHAR(4000)
);
TRUNCATE TABLE #mverrlog;

DECLARE @ErrorLogCount INT;
DECLARE @ErrorLogPath VARCHAR(1000);
DECLARE @cmd VARCHAR(2000);
DECLARE @output TABLE
(
	txtID INT NOT NULL PRIMARY KEY IDENTITY(1,1)
	, txt VARCHAR(1000) NULL
);

SET @ErrorLogPath = CONVERT(VARCHAR(1000), SERVERPROPERTY(N'errorlogfilename'));
SET @ErrorLogPath = LEFT(@ErrorLogPath, LEN(@ErrorLogPath) - CHARINDEX('\', REVERSE(@ErrorLogPath))) + '\';
SET @cmd = 'DIR /b ' + CHAR(34) + @ErrorLogPath + 'ERRORLOG*' + CHAR(34);

/*get the directory listing of all errorlog files'*/
INSERT INTO @output (txt)
EXEC xp_cmdshell @cmd;

SELECT @ErrorLogCount = COUNT(*)
FROM @output o
WHERE o.txt IS NOT NULL;

DECLARE @FileNum INT;
SET @FileNum = 0

WHILE @FileNum < @ErrorLogCount
BEGIN
	INSERT INTO #mverrlog (LogDate, ProcessInfo, Text)
	EXEC sys.sp_readerrorlog @FileNum, 1;

	UPDATE #mverrlog 
	SET ErrorLogFileNum = @FileNum
	WHERE ErrorLogFileNum IS NULL;

	SET @FileNum = @FileNum + 1;
END

DECLARE @IPs TABLE
(
	IP VARCHAR(15)
	, Name VARCHAR(255)
);
DECLARE @IP VARCHAR(15);
DECLARE @Name VARCHAR(255);
DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC
FOR 
SELECT ClientIP = SUBSTRING(el.text
		, CHARINDEX('[', el.text) + 9
		, CHARINDEX(']', el.text) - (CHARINDEX('[', el.text) + 9))
FROM #mverrlog el
WHERE el.Text LIKE 'Login failed for user %.%'
GROUP BY SUBSTRING(el.text
		, CHARINDEX('[', el.text) + 9
		, CHARINDEX(']', el.text) - (CHARINDEX('[', el.text) + 9));
OPEN cur;
FETCH NEXT FROM cur INTO @IP;
WHILE @@FETCH_STATUS = 0
BEGIN
	DELETE FROM @output;
	SET @cmd = 'nslookup ' + @IP;
	INSERT INTO @output(txt)
	EXEC sys.xp_cmdshell @cmd;

	DELETE 
	FROM @output
	WHERE txt NOT LIKE 'Name: %';

	UPDATE @output 
	SET txt = RIGHT(txt, LEN(txt) - 9);

	INSERT INTO @IPs (IP, Name)
	SELECT @IP, txt
	FROM @output;
	FETCH NEXT FROM cur INTO @IP;
END
CLOSE cur;
DEALLOCATE cur;

DELETE 
FROM @IPs
WHERE name IS NULL;

SELECT MostRecentFailedLoginAttempt = MAX(el.LogDate)
	, LoginName = SUBSTRING(el.text
		, CHARINDEX('''', el.text) + 1
		, CHARINDEX('''', el.text, CHARINDEX('''', el.text) + 1) - (CHARINDEX('''', el.text) + 1))
	, FailureReason = SUBSTRING(el.text
		, CHARINDEX('.', el.text) + 1
		, CHARINDEX('.', el.text, CHARINDEX('.', el.text) + 1) - (CHARINDEX('.', el.text) + 1))
	, ClientIP = SUBSTRING(el.text
		, CHARINDEX('[', el.text) + 9
		, CHARINDEX(']', el.text) - (CHARINDEX('[', el.text) + 9))
	, ClientName = ips.Name
FROM #mverrlog el
	LEFT JOIN @IPs ips ON (SUBSTRING(el.text
		, CHARINDEX('[', el.text) + 9
		, CHARINDEX(']', el.text) - (CHARINDEX('[', el.text) + 9))) = ips.IP
WHERE el.Text LIKE 'Login failed for user %.%'
GROUP BY el.Text
	, ips.Name
ORDER BY MAX(el.LogDate) DESC;

In order to run the above code, you need to enable `xp_cmdshell`, and you’ll need to enable auditing of failed logins. The following code enables both items:

USE master;
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE'
    , N'Software\Microsoft\MSSQLServer\MSSQLServer'
    , N'AuditLevel'
    , REG_DWORD
    , 2;
EXEC sys.sp_configure 'show advanced options', 1;
RECONFIGURE
EXEC sys.sp_configure 'xp_cmdshell', 1;
RECONFIGURE
GO

The output looks similar to:

+---------------------+--------+--------------------------------+--------------+----------------------+
| Most Recent Failed  | Login  | FailureReason                  | ClientIP     | ClientName           |
| Login Attempt       | Name   |                                |              |                      |
+---------------------+--------+--------------------------------+--------------+----------------------+
|                     |        |                                |              |                      |
| 2016-06-09 11:33:58 | usr1   | The system administrator can   | 192.168.0.10 | machine1.domain.com  |
|                     |        | unlock it                      |              |                      |
|                     |        |                                |              |                      |
| 2016-06-09 10:33:15 | usr2   | Reason: Password did not match | 192.168.0.11 | machine5.domain.com  |
|                     |        |  that for the login provided   |              |                      |
|                     |        |                                |              |                      |
| 2016-05-30 07:23:53 | mon    | Reason: Password did not match | 192.168.0.13 | machine4.domain.com  |
|                     |        |  that for the login provided   |              |                      |
|                     |        |                                |              |                      |
| 2016-05-26 15:28:03 | dbo    | Reason: Failed to open the exp | 192.168.0.20 | machine17.domain.com |
|                     |        | licitly specified database     |              |                      |
|                     |        |                                |              |                      |
+---------------------+--------+--------------------------------+--------------+----------------------+