Finding Host Names for Failed login attempts!

The Intro

If you manage a lot of SQL Server instances, you likely run into failed login attempts quite often. Perhaps you’re even wondering what client machine is causing all those failures. Since most environments run over TCP/IP; SQL Server helpfully logs the IP address of the client machine that made these failed login attempts to the SQL Server Error Log.

Here’s some common messages around failed logins from the error log:

  • Login failed for user “<username>”. Reason: Password did not match that for the login provided. [CLIENT: 192.168.0.2]
  • Login failed for user “<username>”. Reason: An attempt to login using SQL authentication failed. [CLIENT: 192.168.0.3]
  • Login failed for user “<username>”. Reason: Failed to open the explicitly specified database ‘<databasename>’. [CLIENT: 192.168.0.4]
  • Login failed for user “<username>”. Reason: Could not find a login matching the name provided. [CLIENT: 10.0.0.2]
  • Login failed for user “<username>”. Reason: Access to server validation failed while revalidating the login on the connection. [CLIENT: 10.0.0.100]
  • Login failed for user “<username>”. Reason: The account is currently locked out. [CLIENT: 192.168.0.76]
  • Login failed for user “<username>”. Reason: Failed to open the database ‘<databasename>’ configured in the session recovery object while recovering the connection. [CLIENT: 42.42.42.42]

The script below uses xp_cmdshell and nslookup to lookup the IP address from the error message against your DNS server for the name of the client machine. This can be useful for quickly identifying where all those failed logins are coming from, without requiring a manual nslookup.

A small caution!

Since I’m using xp_cmdshell in this script, you obviously need to have that enabled, which might be considered a potential security issue. I tend to ensure no one has sysadmin rights that isn’t actually a bonafide systems administrator; so I’m not super worried about an escalation-of-privilege attack via xp_cmdshell.

Paying the rent!

So, homies, on to the script1:

/*
    Inspects the SQL Server Error Log for failed logins, then
    uses xp_cmdshell to get the machine name by running nslookup against 
    the IP address of the machine that initiated the failed login.

    By:  Max Vernon
*/

SET NOCOUNT ON;

IF OBJECT_ID(N'tempdb..#errlog', N'U') IS NULL
CREATE TABLE #errlog
(
    ErrorLogFileNum int NULL
    , LogDate datetime
    , ProcessInfo varchar(255)
    , [Text] varchar(4000)
);
TRUNCATE TABLE #errlog;

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 "' + @ErrorLogPath + '\ERRORLOG*"';

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 #errlog (LogDate, ProcessInfo, [Text])
    EXEC sys.sp_readerrorlog @FileNum, 1;

    UPDATE #errlog 
    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 #errlog 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;

--show only the most recent message for each client
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 #errlog 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;

--show all messages 
SELECT 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 #errlog 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 %.%'
ORDER BY el.LogDate DESC;

Let me know if you like this script, and also, let me know if you think it stinks!

This post forms part of our series on SQL Server Security.


1 – yes, the script rendering in this post sucks. Too many double-quotes-inside-single-quotes, or something!