Finding Host Names for Failed login attempts!

2019-02-01 · by Hannah Vernon · in security, tools, Uncategorized

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 script¹:

/*
    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;</code>

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

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;</code>

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!}

Tags: logins

Finding Host Names for Failed login attempts!

The Intro

A small caution!

Paying the rent!

Related

One Response

Leave a ReplyCancel reply

Search

Categories

Pages

Meta