SQL Server Brute Force Attack Detection: Part 2

• Download source code - 6.1 KB

Parts in the Series

• Part 1 - Initial Setup
• Part 2 - Additional logging and data collection
• Part 3 - More configurations and auditing tools

Introduction

For the second part of this series, I wanted to go over some additional functionality that I've been testing to make the code more useful to a developer. These features will also work (to my knowledge) on all versions of SQL Server 2005 and later.

Background

The original code has been chugging along on my DB server at home and regularly blocking offending IPs. In the meantime, I gave some more thought to additional functionality that will make the code more useful to a developer.

One use case that came to mind is a client or mobile app that connects to a SQL Server database and where users have their own user names and passwords. In the case where a user accidentally enters their password incorrectly too many times, we want an easy way to unblock them in the firewall. Typically, a user authentication UI includes a password reset feature so we want an easy way to tack on some simple code to unblock them automatically.

Additionally, I thought it would be also nice to log block/unblock events so I could grab statistics by IP and see if there were any repeat offenders.

Using the Code

I added two tables to capture additional data on failed login attempts. BlockedClientDtl captures the user ID used for each failed attempt and EventLog records block/unblock actions done by the CheckFailedLogins stored procedure. I also modified BlockedClient to record a specific unblock datetime value in case we want to modify how this is calculated (e.g. by client IP, perhaps to extend the block time for repeat offenders).

CREATE TABLE BlockedClient
(
    IPAddress VARCHAR(15) NOT NULL PRIMARY KEY,
    LastFailedLogin DATETIME,
    UnblockDate DATETIME,
    FailedLogins INT,
    FirewallRule VARCHAR(255)
);

CREATE INDEX IX_BlockedClient_UnblockDate ON BlockedClient(UnblockDate);

CREATE TABLE BlockedClientDtl
(
    IPAddress VARCHAR(15) NOT NULL,
    Attempt INT NOT NULL,
    LogDate DATETIME,
    UserId VARCHAR(255),
    Message VARCHAR(512),
    PRIMARY KEY(IPAddress, Attempt),
    FOREIGN KEY(IPAddress) REFERENCES BlockedClient ON DELETE CASCADE
);

CREATE INDEX IX_BlockedClientDtl_UserId_LogDate ON BlockedClientDtl(UserId, LogDate);

CREATE TABLE EventLog
(
    LogId BIGINT NOT NULL PRIMARY KEY IDENTITY,
    LogDate DATETIME DEFAULT GETDATE(),
    IPAddress VARCHAR(15),
    Action VARCHAR(20),
    EventDesc VARCHAR(512)
);

CREATE INDEX IX_EventLog_IP_LogDate ON EventLog(IPAddress, LogDate);

The idea with BlockedClientDtl is to be able to run queries like the following when a user requests a password reset:

DELETE FROM BlockedClient
WHERE EXISTS (SELECT * FROM BlockedClientDtl
              WHERE BlockedClientDtl.IPAddress = BlockedClient.IPAddress
                AND BlockedClientDtl.UserId = 'JDOE')

When user JDOE completes a password reset, we can delete all entries in BlockedClient associated with the user ID (and optionally only for a certain amount of time going back based on BlockedClientDtl.LogDate).

Lastly, the revised code for our CheckFailedLogins SP:

CREATE PROCEDURE CheckFailedLogins
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @UnblockDate DATETIME
    DECLARE @LookbackDate DATETIME
    DECLARE @MaxFailedLogins INT
    DECLARE @FailedLogins TABLE
    (
         LogDate datetime,
         ProcessInfo varchar(50),
         Message text
    );
    DECLARE @FailedLoginClientDtl TABLE
    (
      IPAddress VARCHAR(15),
        LogDate DATETIME,
        UserID VARCHAR(128),
        Message VARCHAR(1000)
    );
    
    SELECT @LookbackDate = dateadd(second, -ConfigValue, getdate())
    FROM Config
    WHERE ConfigID = 1

    SELECT @MaxFailedLogins = ConfigValue
    FROM Config
    WHERE ConfigID = 2

    SELECT @UnblockDate = CASE WHEN ConfigValue > 0 THEN DATEADD(hour, ConfigValue, getdate()) END
    FROM Config
    WHERE ConfigID = 3

    INSERT INTO @FailedLogins -- Read current log
    exec sp_readerrorlog 0, 1, 'Login failed';

    INSERT INTO @FailedLoginClientDtl
    SELECT ltrim(rtrim(substring(CONVERT(varchar(1000), Message),
        charindex('[CLIENT: ', CONVERT(varchar(1000), Message)) + 9,
        charindex(']', CONVERT(varchar(1000), Message)) - 9 - 
          charindex('[CLIENT: ', CONVERT(varchar(1000), Message))))) as IPAddress,
        LogDate,
        CASE WHEN charindex('Login failed for user ''', CONVERT(varchar(1000), Message)) > 0 THEN
        ltrim(rtrim(substring(CONVERT(varchar(1000), Message),
        charindex('Login failed for user ''', CONVERT(varchar(1000), Message)) + 23,
        charindex('''. Reason:', CONVERT(varchar(1000), Message)) - 23 - 
        charindex('Login failed for user ''', CONVERT(varchar(1000), Message))))) END AS UserId,
        Message
    FROM @FailedLogins
    WHERE (Message like '%Reason: An error occurred while _
                           evaluating the password.%' -- Some filter criteria
            OR Message like '%Reason: Could not find a login matching the name provided.%'
            OR Message like '%Reason: Password did not match that for the login provided.%'
            OR Message LIKE '%Login failed. The login is from an untrusted domain _
                             and cannot be used with Windows authentication.%')
        AND LogDate >= @LookbackDate

    INSERT INTO BlockedClient(IPAddress, LastFailedLogin, UnblockDate, FailedLogins)
    OUTPUT INSERTED.IPAddress, 'Block', 'Blocked client ' + INSERTED.IPAddress + _
          ' after ' + CONVERT(VARCHAR(10), INSERTED.FailedLogins) + ' failed login attempts.'
    INTO EventLog(IPAddress, Action, EventDesc) -- Record block event
    SELECT IPAddress,
        MAX(LogDate) AS LastFailedLogin,
        @UnblockDate,
        COUNT(*) AS FailedLogins
    FROM @FailedLoginClientDtl d
    WHERE NOT EXISTS (SELECT * FROM Whitelist l -- Check against whitelist
                      WHERE l.IPAddress = d.IPAddress)
      AND NOT EXISTS (SELECT * FROM BlockedClient c -- ignore already blocked clients
                      WHERE c.IPAddress = d.IPAddress)
      AND IPAddress <> '<local machine>' -- ignore failed logins from local machine
    GROUP BY IPAddress
    HAVING COUNT(*) >= @MaxFailedLogins -- Check against number of failed logins config

    INSERT INTO BlockedClientDtl(IPAddress, Attempt, LogDate, UserId, Message)
    SELECT IPAddress,
      Attempt,
        LogDate,
        UserID,
        Message
    FROM
    (
        SELECT IPAddress,
            ROW_NUMBER()OVER(PARTITION BY IPAddress ORDER BY LogDate) AS Attempt,
            LogDate,
            UserID,
            Message
        FROM @FailedLoginClientDtl d
        WHERE EXISTS (SELECT * FROM BlockedClient c
                                    WHERE c.IPAddress = d.IPAddress)
    )AS t
    WHERE NOT EXISTS (SELECT * FROM BlockedClientDtl dtl
                      WHERE t.IPAddress = dtl.IPAddress
                        AND t.Attempt = dtl.Attempt)

    DELETE FROM BlockedClient -- Delete entries older than the delete config set if > 0
    OUTPUT DELETED.IPAddress, 'Unblock', 'Unblocked client ' + DELETED.IPAddress  + '.'
    INTO EventLog(IPAddress, Action, EventDesc) -- Record unblock event
    WHERE UnblockDate < getdate()
END

For the insert and delete operations on BlockedClient, I've used the INSERTED and DELETED special tables available when using the OUTPUT clause to insert records into the EventLog table so I can see a history of block/unblock events.

Further Improvements

We could perhaps get creative with client-specific block parameters by observing patterns in EventLog. Maybe incrementally increase the block time for an IP for each time they get blocked. In any case, it's good to have visibility of these events if we want to dig in deeper or troubleshoot unexpected behavior.

Unfortunately, we've about reached the limits of information that can be obtained from sp_readerrorlog. In the third part of this series, I'll go over a completely different implementation that uses the service broker that is only available in Standard and Enterprise editions. We'll be able to gather a lot more information on login events with this approach and trigger client blocks on these events directly. With that said, I wanted to cover all of the features that will work in Express editions of SQL Server first so more people can take advantage of them and hopefully make immediate improvements in securing their databases.

The final installment is available here.