SQL Server Brute Force Attack Detection: Part 1

• 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

When deploying a SQL Server database that will be accessible to clients over the internet, it is always best to employ a VPN for clients to connect to the database's LAN. Windows Active Directory logins can also be employed for access so that AD security can be utilized (e.g., account lockout after failed login attempts). Most security experts and DBAs strongly recommend against opening up the port SQL Server listens on (default 1433) to the internet. This is due to the wide availability of automated port scanners that scour the internet for open SQL Server ports and attempt a brute force or dictionary attack, typically on the sa account.

However, sometimes, opening SQL Server up to the internet is unavoidable because of design specifications of an application or legacy code that can't be modified. Perhaps a mobile application connects to the database, making it infeasible or impossible to constrain access only to a set list of IPs whitelisted in the network or server firewall. External access in combination with SQL Server login account access can make a database particularly vulnerable to brute-force attacks since SQL Server has no built-in functionality to detect consecutive failed logins and subsequently disable the account or block the offending client. The worst is when the sa account is enabled and allowed remote access to the server; if a legacy application employs this kind of database access, a system admin will be in a bind trying to maintain security of the server as an attacker can freely attempt as many login attempts as they want to guess the password. The best that can be done is a periodic review of the SQL Server event logs and manually entering the offending IP address to Windows firewall or the edge firewall of the network. Even then, this gives an attacker ample time to try tens of thousands of login attempts. Even if the sa account is disabled and the attacker has to guess a login, these failed login attempts can unnecessarily waste resources on the targeted server.

We can detect and automatically block clients attempting to brute-force our database login using T-SQL on any version of SQL version 2005+. SQL Server has the ability to both read the event logs in T-SQL using sp_readerrorlog and execute command shell commands using the xp_cmdshell. The event log can be read to extract failed login attempts and obtain the remote client IP address and xp_cmdshell can be used to call netsh advfirewall to automatically add block rules in Windows firewall. With some additional processing code, we can incorporate configurations for number of failed attempts to detect before blocking the offending client IP address and for clearing out blocked entries after a certain amount of time.

Background

Several thousand application log entries for SQL Server; it was thousands of failed login attempts from a handful of IP addresses! I blocked them in my firewall, but soon more failed attempts showed from other IP addresses. I removed the port forward in my firewall and resolved to allow access only over my VPN connection, but started thinking about other ways to harden SQL Server in the event that external access via port forward was the only option for a developer or system admin. I knew of scripts written in PowerShell that scan the event logs for failed RDP connections and block IPs in Windows Firewall. After a little research, I determined SQL Server had the needed functionality to scan the event logs and run netsh to create Windows firewall rules. The additional logic to store configurations and manage a block list could then be easily handled in T-SQL.

Using the Code

Configure the Server

First, we need to enable access to xp_cmdshell (disabled by default) and to turn on logging of failed login attempts on the SQL Server instance. Run the following code from an sa or admin account:

USE [master]
GO
/*
Enable auditing of failed logins and use of command shell on database.
*/
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', _
     N'Software\Microsoft\MSSQLServer\MSSQLServer', N'AuditLevel', REG_DWORD, 2
GO

EXEC sp_configure 'show advanced options', 1;
GO

RECONFIGURE;
GO

EXEC sp_configure'xp_cmdshell', 1
GO

RECONFIGURE
GO

Restart the MSSQL service for the changes to take effect.

Create the Tables

Next, we create a new database BruteForceAttackMonitor and three tables to store blocked IP addresses, some configurations and a list of whitelisted IPs.

CREATE DATABASE [BruteForceAttackMonitor]
GO

ALTER DATABASE [BruteForceAttackMonitor] SET RECOVERY SIMPLE
GO

USE [BruteForceAttackMonitor]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

/*
Create tables
*/

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

CREATE INDEX IX_BlockedClient_LastFailedLogin ON BlockedClient(LastFailedLogin);

CREATE TABLE Config
(
    ConfigID INT NOT NULL PRIMARY KEY,
    ConfigDesc VARCHAR(255),
    ConfigValue INT,
);

CREATE TABLE Whitelist
(
    IPAddress VARCHAR(15) NOT NULL PRIMARY KEY,
    Description VARCHAR(255)
);

GO

Configurations

There are three configurations that we'll add to the Config table to give some user configurability. I'm sure many other configs could be implemented, but we'll focus on the most important ones to a sys admin.

1) Lookback time

First, we need a time duration to look back from the current timestamp for failed login attempts in the event log. This should be fairly short, but also longer than the scheduled interval to run the block script. I'm setting this as a duration in seconds and setting to 15 minutes back.

The logic behind this is simple. We want to scan a long enough period of time to capture enough entries in the event log but we also want to keep this short if we have to manually delete a blocked client that just accidentally mistyped their password too many times. Say the connecting application is a mobile app that a user enters their user name and password to connect to the database. They enter the wrong password too many times and get blocked, then perhaps contact support to reset. Support knows to unblock the IP by deleting the entry from our BlockedClient table, and by the time it's brought to their attention, the past failed login events in the event log will be out of our scanning time window so the client doesn't get re-blocked. In this case, we don't want to whitelist their IP; they get an IP dynamically from their mobile provider and some malicious client in the future could get this IP and bypass our brute-force detection.

INSERT INTO Config(ConfigID, ConfigDesc, ConfigValue)
VALUES(1, 'Time in seconds to look back for failed logins', 900)

2) Number of failed login attempts

Next, we define the threshold for failed login attempts before an IP is blocked. Typically, you want this to be greater than 1 so that an accidental mistyped password doesn't trigger a client to be blocked. Something between 3 and 10 is probably best, anything beyond that is likely a malicious client. We'll set this to 3 attempts (that is on the 3^rd failed attempt, block the client IP).

INSERT INTO Config(ConfigID, ConfigDesc, ConfigValue)
VALUES(2, 'Number of failed logins before client is blocked', 3)

3) Time until a client is unblocked

We don't want to block a given IP address forever. Clients often connect using a dynamic IP address from their ISP or mobile provider so there is some chance an IP address used by a malicious client could be used by a trusted client in the future. This may be an unlikely scenario but we also don't need to clutter up the windows firewall with an ever-growing list of blocked IPs. So long as a malicious IP is blocked for a significant amount of time, the hacker or script kiddie targeting our server will give up and move on after a few minutes of rejected connections. If they keep hammering our server for a prolonged period of time from the same IP, they'll only have a small window of time (the frequency that the block script runs) to make a handful of login attempts before being blocked again, greatly diminishing the effectiveness of their attack.

This config is in hours and we'll set it to 24; we'll still allow a client to be blocked forever by setting this to <=0 if that's the desired behavior.

INSERT INTO Config(ConfigID, ConfigDesc, ConfigValue)
VALUES(3, 'Hours before client is unblocked (<=0 for never)', 24)

Managing the Firewall Rules

The list of blocked IPs will be kept in sync with Windows Advanced Firewall using triggers for inserts and deletes on the BlockedClient table. The insert trigger uses xp_cmdshell and netsh to add firewall rules automatically for each blocked IP. The record in BlockedClient is then updated with the name of the firewall rule.

CREATE TRIGGER trg_BlockedClient_I
   ON BlockedClient
   AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @IPAddress VARCHAR(15);
    DECLARE @FirewallRule VARCHAR(255);
    DECLARE @FirewallCmd VARCHAR(1000);
    DECLARE vINSERT_CURSOR CURSOR LOCAL FOR
      SELECT IPAddress FROM INSERTED
    
    OPEN vINSERT_CURSOR;
    FETCH NEXT FROM vINSERT_CURSOR INTO @IPAddress

    WHILE @@FETCH_STATUS = 0
    BEGIN
      SET @FirewallCmd = 'netsh advfirewall firewall add rule name="';
      SET @FirewallRule = 'SQL Server Failed Login Block for ' + @IPAddress;
    /*
        Alternative firewall rule that just blocks IP on SQL Server TCP port
        SET @FirewallCmd = @FirewallCmd + @FirewallRule + _
        '" dir=in interface=any protocol=TCP action=block remoteip=' + @IPAddress + ' LocalPort=1433'    
        */

        SET @FirewallCmd = @FirewallCmd + @FirewallRule + _
        '" dir=in interface=any action=block remoteip=' + @IPAddress
        
        EXEC xp_cmdshell @FirewallCmd --Create firewall rule

        UPDATE BlockedClient  --Update blocked client entry with firewall rule 
                              --so we can reference for delete
        SET FirewallRule = @FirewallRule
        WHERE IPAddress = @IPAddress;

        FETCH NEXT FROM vINSERT_CURSOR INTO @IPAddress;
    END

    CLOSE vINSERT_CURSOR;
    DEALLOCATE vINSERT_CURSOR;
END

GO

The delete trigger references the name of the firewall rule saved in BlockedClient to remove the rule from the firewall by name, again using netsh.

CREATE TRIGGER trg_BlockedClient_D
   ON BlockedClient
   AFTER DELETE
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @FirewallRule VARCHAR(255);
    DECLARE @FirewallCmd VARCHAR(1000);
    DECLARE vDELETE_CURSOR CURSOR LOCAL FOR
      SELECT FirewallRule FROM DELETED
        WHERE FirewallRule IS NOT NULL
    
    OPEN vDELETE_CURSOR;
    FETCH NEXT FROM vDELETE_CURSOR INTO @FirewallRule

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @FirewallCmd = 'netsh advfirewall firewall delete rule name="' + @FirewallRule + '" dir=in';
        
        EXEC xp_cmdshell @FirewallCmd --Delete firewall rule

        FETCH NEXT FROM vDELETE_CURSOR INTO @FirewallRule;
    END

    CLOSE vDELETE_CURSOR;
    DEALLOCATE vDELETE_CURSOR;
END

GO

Next, we add a simple insert trigger to Whitelist to automatically delete records in BlockedClient when an IP is whitelisted, should the need arise.

CREATE TRIGGER trg_WhiteList_I
   ON Whitelist
   AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON; -- Automatically delete any blocked clients after they are whitelisted
    DELETE FROM BlockedClient
    WHERE EXISTS (SELECT * FROM INSERTED
                  WHERE INSERTED.IPAddress = BlockedClient.IPAddress);
END

GO

Whitelisting IPs

Before we get into the code that actually detects failed login attempts, let's put in some code to make it easier to whitelist IPs using CIDR ranges (e.g. to whitelist our LAN subnets). I borrowed some code to make it easier to work with CIDR notation in T-SQL. Using these two functions, we can create a simple stored procedure that can enumerate all IPs in a CIDR range and insert into Whitelist.

First our IP helper functions:

CREATE FUNCTION ConvertIPToLong(@IP VARCHAR(15))
RETURNS BIGINT
AS
BEGIN
    DECLARE @Long bigint
    SET @Long = CONVERT(bigint, PARSENAME(@IP, 4)) * 256 * 256 * 256 +
            CONVERT(bigint, PARSENAME(@IP, 3)) * 256 * 256 +
            CONVERT(bigint, PARSENAME(@IP, 2)) * 256 +
            CONVERT(bigint, PARSENAME(@IP, 1))

    RETURN (@Long)
END

GO

CREATE FUNCTION ConvertLongToIp(@IpLong bigint)
RETURNS VARCHAR(15)
AS
BEGIN
    DECLARE @IpHex varchar(8), @IpDotted  varchar(15)

    SELECT @IpHex = substring(convert(varchar(30), master.dbo.fn_varbintohexstr(@IpLong)), 11, 8)

    SELECT @IpDotted = convert(varchar(3), convert(int, _
                       (convert(varbinary, substring(@IpHex, 1, 2), 2)))) + '.' +
                       convert(varchar(3), convert(int, _
                       (convert(varbinary, substring(@IpHex, 3, 2), 2)))) + '.' +
                       convert(varchar(3), convert(int, _
                       (convert(varbinary, substring(@IpHex, 5, 2), 2)))) + '.' +
                       convert(varchar(3), convert(int, _
                       (convert(varbinary, substring(@IpHex, 7, 2), 2))))

    RETURN @IpDotted
END

Next, we create a single SP to insert whitelisted IPs that will be ignored completely by our failed login detection code. We'll make the subnet mask an optional parameter that defaults to /32 for easier whitelisting of single IPs.

CREATE PROCEDURE WhitelistIP
(
    @IpAddress varchar(15),
    @Mask int = 32,
    @Description text = null
)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @Base bigint  = cast(4294967295 as bigint);
    DECLARE @Power bigint  = Power(2.0, 32.0 - @Mask) - 1;
    DECLARE @LowRange bigint  = dbo.ConvertIPToLong(@IpAddress) & (@Base ^ @Power);
    DECLARE @HighRange bigint  = @LowRange + @Power;
    DECLARE @CurrentIP VARCHAR(15)
    IF @Description IS NULL
        SET @Description = 'Whitelist for ' + @IPAddress + '/' + CONVERT(varchar(2), @Mask);

    WHILE @LowRange <= @HighRange
    BEGIN
      SET @CurrentIP = dbo.ConvertLongToIp(@LowRange);
        INSERT INTO Whitelist(IPAddress, Description)
        SELECT @CurrentIP, @Description
        WHERE NOT EXISTS (SELECT * FROM Whitelist WHERE IPAddress = @CurrentIP);

        SET @LowRange = @LowRange + 1;
    END;
END

GO

It's important to immediately whitelist the IP ranges on the LAN so we don't lock ourselves out, especially if we decide to make the created firewall rules block connections on all ports. The code for the trg_BlockedClient_I trigger has commands for blocking connections on all ports or just the TCP port SQL Server listens on. If your LAN subnet is 192.168.1.x with 255.255.255.0 subnet mask, run the SP with the following parameters:

EXEC [dbo].[WhitelistIP] @IpAddress = N'192.168.1.0', @Mask = 24

When whitelisting IPs outside the LAN, make sure this is only done for a client with a known static IP from their ISP. With a mobile device or typical home internet connection, the public IP is dynamic so there's no point in whitelisting it. In general, only whitelist external IPs you know will always be used by trusted clients.

Blocking Malicious Clients

Now let's get into the code that actually detects malicious login attempts and manages our block list and firewall rules. I've put this code into a single stored procedure that can be scheduled to run at a regular interval. This can be done via SQL Server Agent or the SQL Server CLI and Windows Task Scheduler (if using an Express version that does not have SQL Server Agent). I recommend running this every 30 seconds to a minute. Make sure the "Time in seconds to look back for failed logins" parameter in Config is longer than this, otherwise there will be a window of unmonitored time where a failed login will go undetected.

Make sure the user account that runs the script is a local admin on the machine SQL Server runs on (if using SQL Server Agent, the service must be running under the local machine admin account). Machine admin access is needed to run netsh commands to add/remove firewall rules.

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
    );
    
    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 BlockedClient(IPAddress, LastFailedLogin, FailedLogins)
    SELECT IPAddress,
        MAX(LogDate) AS LastFailedLogin,
        COUNT(*) AS FailedLogins
    FROM
    (
        SELECT LogDate,
            ProcessInfo,
            Message,
            ltrim(rtrim(substring(CONVERT(varchar(1000), Message), -- Extract client IP
            charindex('[CLIENT: ', CONVERT(varchar(1000), Message)) + 9,
            charindex(']', CONVERT(varchar(1000), Message)) - 9 - _
                  charindex('[CLIENT: ', CONVERT(varchar(1000), Message))))) as IPAddress
        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
    ) AS t
    WHERE NOT EXISTS (SELECT * FROM Whitelist l -- Check against whitelist
                      WHERE l.IPAddress = t.IPAddress)
        AND NOT EXISTS (SELECT * FROM BlockedClient c -- ignore already blocked clients
                      WHERE c.IPAddress = t.IPAddress)
        AND IPAddress <> '<local machine>' -- ignore failed logins from local machine
    GROUP BY IPAddress
    HAVING COUNT(*) >= @MaxFailedLogins -- Check against number of failed logins config
      AND MAX(LogDate) >= COALESCE(@UnblockDate, MAX(LogDate)) -- Check that new entries 
                                     -- meet delete config criteria so we don't unnecessarily
                                     -- add a rule that would then get deleted.

    DELETE FROM BlockedClient -- Delete entries older than the delete config
    WHERE LastFailedLogin < @UnblockDate
END
GO

The second part of the procedure looks for clients blocked for longer than the 24 hour time period we set and removes them so our list doesn't grow out of control.

Lastly, looking at Windows Firewall, we can see our block rules that were automatically created!

Remarks

Remote access to a SQL Server database is always best done using a VPN to get clients onto the database's private LAN (and optionally using AD account access) for the best security. This setup will always give the highest security and the most flexibility for managing client access.

However, sometimes a developer or sys admin may be forced to work with a weak security implementation employing direct access to the listening port from outside the LAN and even worse using the sa account. With this setup, the server will usually get bombarded with thousands of login attempts on the SA account using commonly available port scanning tools. This code provides a free and easily deployable intrusion prevention scheme that will work on all versions of SQL Server beyond 2005 deployed in a Windows environment, whether Express or one of the paid enterprise editions.

Please feel free to comment on the code and point out any bugs that I've missed or improvements that could be made!

References

• IP Address Conversion Between Integer and String