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. Even with all those great details, it can still be difficult to accurately understand which machine caused those failed logins, since the error log only shows the IP address, not the machine name.
So much beauty!
The code below analyzes the SQL Server error logs for messages relating to failed logins. Helpfully, it also obtains the DNS machine name for the computers where the failed logins originated.
The code requires the use of the extended stored procedure sys.xp_cmdshell, which it uses to query DNS for machine names. See the xp_cmdshell Microsoft Documentation for more details.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 |
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; </code> |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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 | | | | | | | | | +---------------------+--------+--------------------------------+--------------+----------------------+
Hopefully this script helped you figure out who caused those failed login attempts! Let me know if you have any questions or comments.
Check out the rest of our posts on security!