Prevent Brute Force Attacks on SQL Server

Introduction

This article is based on a great article from Ryan G Conrad: SQL Server Brute Force Attack Detection: Part 1.

Sometimes, you need to have your SQL server exposed to the internet giving the possibility to connect from any IP address without using a VPN, thus opening for the likeliness of having your SQL server attacked by unwanted guests. By detecting logins that fail and block their IP address for some time, you will most likely have them move on to attack another server instead. This code creates firewall rules that stop the attackers after a few attempts.

Background

Please read the article by Ryan G Conrad for the background, as mine was the same as his. I have used parts of his solution in mine, but made a better one (for me at least). He created one firewall rule for each IP address that should be blocked, which made a mess when there were hundreds or maybe thousand+ addresses in the block list. This solution creates blocks (configurable) of IP addresses and keeps the list of firewall rules to a manageable number. The IP is kept blocked for 48 hours (default but configurable), after that, the IP address is deleted from the list and the firewall rule(s) recreated.

Using the Code

This code assumes that you are not using the sa account, and disabling the sa account is always a good practice. If the sa account is in use, you can remove it from the code. The attackers seem to always try with different accounts, and sa, su, admin, sys, mssql, kisadmin, bwsadmin seems to be common. Check your log to find out which accounts they attack that are not used by you or your customers. In the code where the log is read, you can alter the ANDs and ORs to get the result you want.

The code is only T-SQL put in a stored procedure, two tables and a SQL Agent job. The procedure can be put in any database on the same SQL sever, but for the ease of it, the master database is used here.

The code is using xp_cmdshell which is disabled by default. To enable it, run the following code from an "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 affect.

Inside the stored procedure code, there is a section where it checks for the existance of a couple of tables, and if they do not exist, they will be created.

The best way (I found) to fetch the failed login attempts is to use auditing. The audit files can be kept small and few and will be quicker to read than using the sp_readerrorlog.

The code also supports reading the log using the sp_readerrorlog, and it too can be kept small by running the sp_cycle_errorlog.

Run the code below to make the audit file. Change the FILEPATH to your requirements before running it.
This setup creates 2MB files with only 2 files. This makes it fast to query. You can always run the procedure with @UserErrorLog=1 to fill the list of IPs to block from the start.

USE Master
GO

CREATE SERVER AUDIT [Audit-FailedLogins]
TO FILE 
(   FILEPATH = N'D:\Database\Audits'
    ,MAXSIZE = 2 MB
    ,MAX_ROLLOVER_FILES = 2
    ,RESERVE_DISK_SPACE = OFF
)
WITH
(   QUEUE_DELAY = 1000
    ,ON_FAILURE = CONTINUE
    ,AUDIT_GUID = '56346368-70ee-45c2-85af-3ad7181501f9'
)
ALTER SERVER AUDIT [Audit-FailedLogins] WITH (STATE = ON)
GO

CREATE SERVER AUDIT SPECIFICATION [Audit-FailedLogins-Specification]
FOR SERVER AUDIT [Audit-FailedLogins]
ADD (FAILED_LOGIN_GROUP)
WITH (STATE = ON)
GO

USE Master
GO

CREATE TABLE dbo.T_System(
VarName VarChar(50) NOT NULL,
Value VarChar(50) NOT NULL,
CONSTRAINT PK_T_System PRIMARY KEY CLUSTERED (VarName ASC))  

GO

--The system table is used for some variables that needs to be set and can be changed
INSERT INTO T_System VALUES
('AuditFilePath','D:\Database\Audits\*.sqlaudit') --Change this to fit your requirements
INSERT INTO T_System VALUES('MaxNoIpAddressesInOneFwRule',200)
INSERT INTO T_System VALUES('FwRuleNaming','Blocked SQL Attempts #')    
INSERT INTO T_System VALUES
('LocalIpRange','192.168.0.%') --Local IP addresses are excluded, enter your own IP range.

INSERT INTO T_System VALUES ('IpBlockTime',48) --The number of hours the IP address will be blocked before deleted 
                   --from the table
--UPDATE T_System SET Value=48 WHERE VarName='IpBlockTime'

INSERT INTO T_System VALUES('RemakeFireWallRules',0) 
--Running an UPDATE T_System SET Value=1 WHERE VarName='RemakeFireWallRules' 
--will force the routine to run the RemakeFireWallRules part   

The list of blocked IPs are kept in a table called T_BlockedIPs (this will be created automatically on the first run if it does not exist):

USE Master
GO

CREATE TABLE T_BlockedIPs(
ID int IDENTITY(1,1) NOT NULL,
EntryTime datetime NULL,
IP varchar(20) NULL,
FirstAttempt datetime NULL,
LastAttempt datetime NULL,
NoAttempts int NULL,
FireWallRuleNo int NULL,
WhiteListed bit NOT NULL CONSTRAINT DF_T_BlockedIPs_WhiteListed DEFAULT(0),
CONSTRAINT [PK_T_BlockedIPs] PRIMARY KEY CLUSTERED (ID ASC))
        
GO

CREATE INDEX IX_T_BlockedIPs ON T_BlockedIPs(LastAttempt)
GO

It's time to create the procedure itself:

CREATE PROC [dbo].[spUpdateBlockedIPs] --Version 2.0.2 Last edited 2020.06.24
@UseReadErrorLog bit = 0, --Use the logfile to read failed login attempts
@RemakeFireWallRules bit = 0, --Triggers a remake of the firewall rules
@JustReadLog bit = 0, --Just read the log
@GetFireWallRuleNo int = 0, --Can be used to search for firewall rules in the firewall 
                            --with the correct naming
@GetFireWallRuleTo int = 0, --Can be used to search for firewall rules in the firewall 
                            --with the correct naming
@GetOnlyFireWallRuleName bit = 0,
@GetFireWallRuleWithIP VarChar(20)=NULL, --Find the rule that has the IP address

@WhiteListIP VarChar(20)=NULL --Can be used to whitelist an IP address. 
                              --The WhiteListed value of T_BlockedIPs will be set then
                              --Whitelisted IP addresses can also be added to 
                              --the T_WhiteList table
                              --Run a remake after whitelisting addresses
                              --(EXEC spUpdateBlockedIPs @RemakeFireWallRules = 1)
AS
--This routine will block all failed attempts to use sa account after some time
--(depending on how often you run the routine).
--By not blocking attempts to logon to your real DB users, 
--you will not have your customers blocked unless they try the sa account.
--Most attacks will try this account, so by blocking all that tries to connect 
--using this account(and fails) will catch them before they also try other accounts.
--TIP: For security reason, the sa account should be disabled anyway.

--By using auditing, it will be faster to query, but after it has been created 
--it will start from scratch to build up the records of IPs to block.
--To build the table of IPs to block from the start, run the routine with 
--@UseReadErrorLog = 1 once, and @UseReadErrorLog = 0 from then on.
--The sp_readerrorlog can take a very long time if the log has gotten large. 
--You can run the command sp_cycle_errorlog to shorten eg log, 
--but this will also make it start from scratch(unless you alter the log you want to read).

--Create the stored procedure and the necessary tables in the Master(or any other) 
--database and make a SQL Agent job to run it every 15 seconds or 
--whatever time interval you want.
--Many attackers try multiple times per second, so even with a 15 second job running, 
--they get some attempts to guess the password.

--To keep the list of IP addresses somewhat under control, you can specify 
--how long after their last attempt the IP address will be kept in the list.
--You can unblock it after 48 hours for example. If "they" try again, 
--they will be blocked another 48 hours.
--This feature can be set in the system table.

SET NOCOUNT ON

BEGIN --REGION
    IF OBJECT_ID('T_System') IS NULL
    BEGIN
        
        CREATE TABLE dbo.T_System(
        VarName VarChar(50) NOT NULL,
        Value VarChar(50) NOT NULL,
        CONSTRAINT PK_T_System PRIMARY KEY CLUSTERED (VarName ASC))

        --The system table is used for some variables that needs to be set and can be changed
        INSERT INTO T_System VALUES('AuditFilePath',_
        'C:\Database\Audits\*.sqlaudit') --Change this to fit your requirements
        INSERT INTO T_System VALUES('MaxNoIpAddressesInOneFwRule',200)
        INSERT INTO T_System VALUES('FwRuleNaming','Blocked SQL Attempts #')    
        INSERT INTO T_System VALUES('LocalIpRange','192.168.16.%') --Local IP addresses 
                                     --are excluded, enter your own IP range.
        
        INSERT INTO T_System VALUES('IpBlockTime',48) --The number of hours the IP address 
                                   --will be blocked before deleted from the table
        --UPDATE T_System SET Value=48 WHERE VarName='IpBlockTime'
        
        INSERT INTO T_System VALUES('RemakeFireWallRules',0) --Running an UPDATE 
                                   --T_System SET Value=1 WHERE VarName='RemakeFireWallRules'
                                   --will force the routine to run the RemakeFireWallRules part    
    END
           
    IF OBJECT_ID('T_BlockedIPs') IS NULL
    BEGIN
            
        CREATE TABLE T_BlockedIPs(
        ID int IDENTITY(1,1) NOT NULL,
        EntryTime datetime NULL,
        IP varchar(20) NULL,
        FirstAttempt datetime NULL,
        LastAttempt datetime NULL,
        NoAttempts int NULL,
        FireWallRuleNo int NULL,
        WhiteListed bit NOT NULL CONSTRAINT DF_T_BlockedIPs_WhiteListed DEFAULT(0),
        CONSTRAINT [PK_T_BlockedIPs] PRIMARY KEY CLUSTERED (ID ASC))
                
        CREATE INDEX IX_T_BlockedIPs ON T_BlockedIPs(LastAttempt)
        
        --Run the statements below to create an Failed login audit. 
        --Adjust the FILEPATH to your requirements.   
        --NB! Adjust the FILEPATH to suit your criteria first
        --Keep the filesize and number of files small to make it faster to query
        --You can always get the data from the log using @UseReadErrorLog = 1
        
        --USE Master
        --CREATE SERVER AUDIT [Audit-FailedLogins]
        --TO FILE 
        --(   FILEPATH = N'D:\Database\Audits'
        --    ,MAXSIZE = 2 MB
        --    ,MAX_ROLLOVER_FILES = 2
        --    ,RESERVE_DISK_SPACE = OFF
        --)
        --WITH
        --(   QUEUE_DELAY = 1000
        --    ,ON_FAILURE = CONTINUE
        --    ,AUDIT_GUID = '56346368-70ee-45c2-85af-3ad7181501f9'
        --)
        --ALTER SERVER AUDIT [Audit-FailedLogins] WITH (STATE = ON)
        --GO
        --
        --CREATE SERVER AUDIT SPECIFICATION [Audit-FailedLogins-Specification]
        --FOR SERVER AUDIT [Audit-FailedLogins]
        --ADD (FAILED_LOGIN_GROUP)
        --WITH (STATE = ON)
        --GO
        
    END

    IF OBJECT_ID('T_WhiteList') IS NULL
    BEGIN
        CREATE TABLE T_WhiteList (IP varchar(20) NOT NULL)
    END
END

IF @RemakeFireWallRules = 0
BEGIN
    SELECT @RemakeFireWallRules = Value FROM T_System WHERE VarName='RemakeFireWallRules'    
    IF @RemakeFireWallRules = 1 UPDATE T_System SET Value=0 WHERE VarName='RemakeFireWallRules'
END 

/*  Some samples on how to use the routine
    EXEC spUpdateBlockedIPs @UseReadErrorLog = 0 --Default
    EXEC spUpdateBlockedIPs @UseReadErrorLog = 1
    EXEC spUpdateBlockedIPs @RemakeFireWallRules = 1
    EXEC spUpdateBlockedIPs @UseReadErrorLog = 0, @JustReadLog = 1
    EXEC spUpdateBlockedIPs @UseReadErrorLog = 1, @JustReadLog = 1
    EXEC spUpdateBlockedIPs @GetFireWallRuleNo = 4
    EXEC spUpdateBlockedIPs @GetFireWallRuleNo = 1,@GetFireWallRuleTo = 10,@GetOnlyFireWallRuleName=1
    EXEC spUpdateBlockedIPs @WhiteListIP = '1.1.1.1'
    EXEC spUpdateBlockedIPs @GetFireWallRuleWithIP = '196.28.236.74',@GetOnlyFireWallRuleName=1

    --Put a new IP address into the whitelist table
    INSERT INTO T_WhiteList VALUES('2.2.2.2')

    --Get a list of all the blocked IP addresses ordered by the time it was put in the table
    SELECT B.*,(SELECT COUNT(*) FROM T_WhiteList WHERE IP = B.IP) 
            AS InWhiteListTable FROM T_BlockedIPs B ORDER BY EntryTime DESC
    
    --Query how many IP addresses there are in each group
    SELECT FireWallRuleNo,WhiteListed,WL.IP AS InWhiteListTable,COUNT(*) AS NoRecords 
    FROM T_BlockedIPs B LEFT OUTER JOIN T_WhiteList WL ON WL.IP=B.IP
    GROUP BY FireWallRuleNo,WhiteListed,WL.IP ORDER BY FireWallRuleNo    
*/

--Local IP range
DECLARE @LocalIP VarChar(100)
SELECT @LocalIP = Value FROM T_System WHERE VarName='LocalIpRange'

--The path to the audit files.
DECLARE @AuditFilePath VarChar(500) 
SELECT @AuditFilePath = Value FROM T_System WHERE VarName='AuditFilePath'
IF @AuditFilePath IS NULL 
BEGIN
    PRINT 'AuditFilePath is not set in T_System!'
    RETURN
END

--Name of the firewall rule. 
--A number will be added down the line  - for example Blocked IPs #01
DECLARE @FireWallRuleName VarChar(50)
SELECT @FireWallRuleName = Value FROM T_System WHERE VarName='FwRuleNaming'
IF @FireWallRuleName IS NULL 
BEGIN
    PRINT 'FireWallRuleName is not set in T_System!'
    RETURN
END

--The max number of IP addresses you want in each firewall rule
--If you change this after the routine has been running, 
--run the routine -> EXEC spUpdateBlockedIPs @RemakeFireWallRules = 1
--It has been tested with and default set to 200 in each rule, but will probably handle more
DECLARE @MaxIPs int
SELECT @MaxIPs = Value FROM T_System WHERE VarName='MaxNoIpAddressesInOneFwRule'
IF @MaxIPs IS NULL 
BEGIN
    PRINT 'MaxNoIpAddressesInOneFwRule is not set in T_System!'
    RETURN
END
          
--Getting the number of hours the IP address should be blocked
--If not set in T_System or the Value has not been set to a number, 48 hours will be used
DECLARE @sIpBlockTime VarChar(20)
DECLARE @IpBlockTime int
SELECT @sIpBlockTime = Value FROM T_System WHERE VarName='IpBlockTime'
IF @sIpBlockTime IS NULL OR ISNUMERIC(@sIpBlockTime)=0 SET @sIpBlockTime = '48'
SET @IpBlockTime = CAST(@sIpBlockTime AS int)


--Removing IP addresses older than the block time from T_BlockedIPs
DELETE FROM T_BlockedIPs WHERE LastAttempt < DATEADD(hh,-@IpBlockTime,GETDATE())
IF @@ROWCOUNT > 0 SET @RemakeFireWallRules = 1 --This will recreate the firewall rules

--SELECT * FROM T_BlockedIPs WHERE LastAttempt < DATEADD(hh,-48,GETDATE())

--Variables and tables
DECLARE @Tab1 TABLE (ID int,EntryTime datetime,IP VarChar(20),
FirstAttempt DateTime,LastAttempt DateTime,NoAttempts int)
DECLARE @IPs VarChar(5000)
DECLARE @FireWallCmd VarChar(5000)
DECLARE @FireWallNo int = 0
DECLARE @FireWallName VarChar(100)

IF @WhiteListIP IS NOT NULL
BEGIN
    UPDATE T_BlockedIPs SET WhiteListed = 1 WHERE IP = @WhiteListIP
    EXEC spUpdateBlockedIPs @RemakeFireWallRules = 1
    --PS! Whitelisted IP addresses can also be put in the T_WhiteList table
    RETURN
END    


IF @GetFireWallRuleNo > 0 OR @GetFireWallRuleWithIP IS NOT NULL
BEGIN
    DECLARE @Tab TABLE (FireWallRuleNo int,output VarChar(MAX))
    
    IF  @GetFireWallRuleWithIP IS NOT NULL 
    AND @GetFireWallRuleNo = 0 AND @GetFireWallRuleTo = 0
    BEGIN
        SET @GetFireWallRuleNo = 1
        SET @GetFireWallRuleTo = 50
    END
    ELSE
        IF @GetFireWallRuleTo < @GetFireWallRuleNo SET @GetFireWallRuleTo = @GetFireWallRuleNo
        
    SET @FireWallNo = @GetFireWallRuleNo
    WHILE @FireWallNo <= @GetFireWallRuleTo
    BEGIN
        SET @FireWallName = @FireWallRuleName + RIGHT(STR(100 + @FireWallNo),2)
        SET @FireWallCmd = 'cmd.exe /c netsh advfirewall firewall show rule name="' + 
                           @FireWallName + '"'    
        INSERT INTO @Tab (output) EXEC xp_cmdshell @FireWallCmd
        UPDATE @Tab SET FireWallRuleNo=@FireWallNo WHERE FireWallRuleNo IS NULL
        
        SET @FireWallNo = @FireWallNo + 1
    END 
        
    IF @GetFireWallRuleWithIP IS NULL        
        SELECT REPLACE(output,'/32','') AS output FROM @Tab WHERE output 
        LIKE 'Rule Name:%' OR (@GetOnlyFireWallRuleName = 0 AND output LIKE '%/32,%')        
    ELSE
    BEGIN
        SELECT @FireWallNo = FireWallRuleNo FROM @Tab WHERE output 
                             LIKE '%' + @GetFireWallRuleWithIP + '%'        
        SELECT REPLACE(output,'/32','') AS output FROM @Tab 
        WHERE (output LIKE 'Rule Name:%' OR (@GetOnlyFireWallRuleName = 0 
        AND output LIKE '%/32,%')) AND FireWallRuleNo=@FireWallNo               
    END        
    RETURN
END

IF @RemakeFireWallRules = 1
    UPDATE T_BlockedIPs SET FireWallRuleNo=NULL --This will trigger a remake 
                                                --of the firewall rules further down
    
IF @UseReadErrorLog=1
BEGIN
    DECLARE @Tab2 TABLE (LogDate DateTime,ProcessInfo VarChar(500),Text VarChar(500))
    INSERT INTO @Tab2
    EXEC sp_readerrorlog 0, 1, 'Login failed for user '''   --This will get all failed 
                                                            --login attempts. It can take 
                                                            --a long time to run 
                                                            --if the log is big.
                                                            --The log can be rolled over 
                                                            --using the sp_cycle_errorlog 
                                                            --routine
    
    INSERT INTO @Tab1 (IP,FirstAttempt,LastAttempt,NoAttempts)
    SELECT LTRIM(RTRIM(REPLACE(SUBSTRING(Text,CHARINDEX('[CLIENT:',Text)+8,20),']',''))) IP,
    MIN(LogDate) AS FirstAttempt,
    MAX(LogDate) AS LastAttempt,
    COUNT(*) AS NoAttempts
    FROM @Tab2 WHERE LogDate > '20200101' --Just so that we don't go too far back in time 
               --and get unnecessary old attack attempts. Adjust to your requirements.
    AND Text NOT LIKE '%Failed to open the explicitly specified database%'
    AND (Text LIKE 'Login failed for user ''sa''%' --Make OR's to catch the accounts you want
    OR Text LIKE 'Login failed for user ''su''%'
    OR Text LIKE 'Login failed for user ''sys''%'
    OR Text LIKE 'Login failed for user ''mssql''%'
    OR Text LIKE 'Login failed for user ''kisadmin''%'
    OR Text LIKE 'Login failed for user ''bwsadmin''%')
    GROUP BY LTRIM(RTRIM(REPLACE(SUBSTRING(Text,CHARINDEX('[CLIENT:',Text)+8,20),']','')))        
END
ELSE
BEGIN
    INSERT INTO @Tab1 (IP,FirstAttempt,LastAttempt,NoAttempts)
    SELECT  LTRIM(RTRIM(REPLACE(SUBSTRING(statement,CHARINDEX('[CLIENT:',statement)+8,20),']',''))) IP,
    MIN(DATEADD(hh,2,event_time)) AS FirstAttempt, --Adding 2 hours because of the timezone. 
                                               --Should/could probably be done a better way :)
    MAX(DATEADD(hh,2,event_time)) AS LastAttempt,
    COUNT(*) AS NoAttempts
    FROM sys.fn_get_audit_file(@AuditFilePath,DEFAULT, DEFAULT)
    WHERE action_id = 'LGIF' 
    AND statement NOT LIKE '%Failed to open the explicitly specified database%'
    AND (statement LIKE 'Login failed for user ''sa''%' --Make OR's to catch the accounts 
                                                        --you want
    OR statement LIKE 'Login failed for user ''su''%'
    OR statement LIKE 'Login failed for user ''sys''%'
    OR statement LIKE 'Login failed for user ''mssql''%'
    OR statement LIKE 'Login failed for user ''kisadmin''%'
    OR statement LIKE 'Login failed for user ''bwsadmin''%')
    GROUP BY LTRIM(RTRIM(REPLACE(SUBSTRING(statement,CHARINDEX('[CLIENT:',statement)+8,20),']','')))        
END

--Since this routine is running quite often(probably), 
--we keep the job history clean of these items
EXEC msdb.dbo.sp_purge_jobhistory @job_name = N'AuditFailedLogins'

IF @JustReadLog = 1
BEGIN
    SELECT T.IP,T.FirstAttempt,T.LastAttempt,T.NoAttempts,
    B.ID,B.EntryTime,FireWallRuleNo,WhiteListed AS WhiteListed,
    (SELECT COUNT(*) FROM T_WhiteList WHERE IP = T.IP) AS InWhiteListTable
    FROM @Tab1 T LEFT OUTER JOIN T_BlockedIPs B ON B.IP = T.IP ORDER BY LastAttempt DESC
    RETURN
END

INSERT INTO T_BlockedIPs (EntryTime,IP,FirstAttempt,LastAttempt,NoAttempts) 
SELECT GETDATE(),IP,FirstAttempt,LastAttempt,NoAttempts 
FROM @Tab1 WHERE IP NOT IN(SELECT IP FROM T_BlockedIPs) AND
IP NOT LIKE @LocalIP --Local IP addresses are excluded, enter your own IP range above

UPDATE T_BlockedIPs SET LastAttempt=I.LastAttempt,NoAttempts=I.NoAttempts
FROM T_BlockedIPs B JOIN @Tab1 I ON I.IP=B.IP
    
--Clearing the @Tab1 table before reusing it
DELETE FROM @Tab1

--Catching only the new IP addresses in T_BlockedIPs and not the ones that are whitelisted
INSERT INTO @Tab1 SELECT ID,EntryTime,IP,FirstAttempt,LastAttempt,NoAttempts 
FROM T_BlockedIPs WHERE WhiteListed = 0 AND IP NOT IN(SELECT IP FROM T_WhiteList)
AND FireWallRuleNo IS NULL 
ORDER BY ID
 
IF (SELECT COUNT(*) FROM @Tab1)= 0 RETURN --No changes
    
DECLARE @IP TABLE (ID int,IP VarChar(20))

DECLARE @LastFireWallRuleNo int = ISNULL((SELECT MAX(FireWallRuleNo) FROM T_BlockedIPs),1)
DECLARE @LastFireWallRuleNoCnt int = ISNULL((SELECT COUNT(*) FROM T_BlockedIPs
WHERE FireWallRuleNo=@LastFireWallRuleNo),0)
AND IP NOT IN(SELECT IP FROM T_WhiteList) AND
DECLARE @TopCntSpaceLeft int = @MaxIPs - @LastFireWallRuleNoCnt

IF @TopCntSpaceLeft > 0
    SET @FireWallNo = @LastFireWallRuleNo --Using the last firewall rule number 
                                          --to put the TopCntLeft IP addresses in
ELSE
    SET @FireWallNo = @LastFireWallRuleNo + 1 --Making a new firewall rule

--Getting the @TopCntSpaceLeft records to put in the existing firewall rule
INSERT INTO @IP SELECT TOP (@TopCntSpaceLeft) ID, IP FROM @Tab1 ORDER BY ID

--Getting the existing records that are not whitelisted
INSERT INTO @IP SELECT ID,IP FROM T_BlockedIPs WHERE WhiteListed = 0 
       AND IP NOT IN(SELECT IP FROM T_WhiteList) AND FireWallRuleNo = @FireWallNo
--ID,EntryTime,IP,FirstAttempt,LastAttempt,NoAttempts
WHILE (SELECT COUNT(*) FROM @Tab1) > 0 --Looping while there are more IPs in @Tab1
BEGIN            
    SET @FireWallName = @FireWallRuleName + RIGHT(STR(100 + @FireWallNo),2)
    
    --Updating the IP records and setting the FireWallRuleNo
    UPDATE T_BlockedIPs SET FireWallRuleNo = @FireWallNo WHERE IP IN(SELECT IP FROM @IP)
    
    --Making a comma separated list of IP addresses using the FOR XML PATH and 
    --removing x0D(carrigage returns)
    SELECT @IPs = REPLACE((SELECT IP + ',' FROM @IP ORDER BY ID FOR XML PATH('')),'&#x0D;','') 
    
    --Removing linefeeds and carriage returns
    SELECT @IPs = REPLACE(REPLACE(@IPs,CHAR(10),''),CHAR(13),'')
     
    --Checking if the firewall rule already exists
    SET @FireWallCmd = 'cmd.exe /c netsh advfirewall firewall show rule name="' + 
                        @FireWallName  + '"'
    INSERT INTO @Tab (output) EXEC xp_cmdshell @FireWallCmd
    IF EXISTS(SELECT * FROM @Tab WHERE output LIKE '%No rules match the specified criteria%')
    BEGIN
         --Create the firewall rule with the IP addresses that should be blocked
        SET @FireWallCmd = 'cmd.exe /c netsh advfirewall firewall add rule name="' + 
        @FireWallName  + '" dir=in interface=any protocol=any action=block remoteip=' + @IPs    
        PRINT @FireWallCmd
        EXEC xp_cmdshell @FireWallCmd,no_output        
    END
    ELSE
    BEGIN         
        --Update the firewall rule with the new IP addresses
        SET @FireWallCmd = 'cmd.exe /c netsh advfirewall firewall set rule name="' + 
                            @FireWallName  + '" new remoteip=' + @IPs    
        PRINT @FireWallCmd
        EXEC xp_cmdshell @FireWallCmd,no_output
    END      
    
    DELETE FROM @Tab1 WHERE IP IN(SELECT IP FROM @IP) --Delete the handled IPs from @Tab1
    DELETE FROM @IP --Clear the @IP table
    INSERT INTO @IP SELECT TOP (@MaxIPs) ID, IP FROM @Tab1 ORDER BY ID --Inserting the 
                                            --next @MaxIPs records from @Tab1 into @IP
    
    SET @FireWallNo = @FireWallNo + 1 --Shifting to next firewall rule number
END    

--Delete firewall rules with the correct naming that are above the highest FireWallRuleNo
SET @FireWallNo = ISNULL((SELECT MAX(FireWallRuleNo) FROM T_BlockedIPs),0) + 1
WHILE @FireWallNo < 20 --Adjust the value to how high your numbers can get up to
BEGIN        
    SET @FireWallName = @FireWallRuleName + RIGHT(STR(100 + @FireWallNo),2) 
    SET @FireWallCmd = 'cmd.exe /c netsh advfirewall firewall delete rule name="' + 
                        @FireWallName + '"'   
    EXEC xp_cmdshell @FireWallCmd,no_output       
    SET @FireWallNo = @FireWallNo + 1
END

You should now be able to run the procedure. Below, you find some ways to run it:

EXEC spUpdateBlockedIPs @UseReadErrorLog = 0 --Default
EXEC spUpdateBlockedIPs @UseReadErrorLog = 1
EXEC spUpdateBlockedIPs @RemakeFireWallRules = 1
EXEC spUpdateBlockedIPs @UseReadErrorLog = 0, @JustReadLog = 1
EXEC spUpdateBlockedIPs @UseReadErrorLog = 1, @JustReadLog = 1
EXEC spUpdateBlockedIPs @GetFireWallRuleNo = 1
EXEC spUpdateBlockedIPs @GetFireWallRuleNo = 1,@GetFireWallRuleTo = 10,@GetOnlyFireWallRuleName=1
EXEC spUpdateBlockedIPs @WhiteListIP = '1.1.1.1'
EXEC spUpdateBlockedIPs @GetFireWallRuleWithIP = '196.28.236.74',@GetOnlyFireWallRuleName=1

The default is to run it just like EXEC spUpdateBlockedIP, without any parameters.

• @UseReadErrorLog = 1 will force it to use the built in sp_readerrorlog function. This can take a long time and will produce a long list of IP addresses if the attacks have been going on for some time.
• @RemakeFireWallRules = 1 will rearrange/recreate the firewall rules. It is only the last fw rule that is not filled up to the MaxIPs that is affected if there is a new IP that attacks the system. The procedure will keep the fw rules if there are no changes in them. When the last attempt of blocked IP address is older than the IpBlockTime variable in T_System, the IP address will be deleted and the procedure will automatically recreate the fw rule(s).
• @JustReadLog = 1 will not affect the firewall rules. It will just read the audit log (or use the sp_readerrorlog if @UseReadErrorLog = 1 is also set).
• @GetFireWallRuleNo = 1 will get the information from the firewall rule number. The firewall rule number is given in the T_BlockedIPs table. The information is read using netsh advfirewall firewall show rule name="Blocked SQL Attempts #01"
• @GetFireWallRuleTo = 1 in combination with @GetFireWallRuleNo will loop from RuleNo to RuleTo and get the information as described above.
• @GetOnlyFireWallRuleName = 1 will show only the firewall names and not the IP addresses.
• @WhiteListIP = '[some IP address]' will set the IP address as whitelisted in the T_BlockedIPs table.
• There is also a whitelist table of its own called T_WhiteList containing IP addresses that are whitelisted.
• @GetFireWallRuleWithIP = '196.28.236.74' will search the firewall rules for the given IP address. In combination with @GetOnlyFireWallRuleName, it will only show the rule name in which it was found.

Some other queries that are nice are:

--Get a list of all the blocked IP addresses ordered by the time it was put in the table
SELECT B.*,(SELECT COUNT(*) FROM T_WhiteList WHERE IP = B.IP) 
AS InWhiteListTable FROM T_BlockedIPs B ORDER BY EntryTime DESC

This will give you a list of all the blocked IPs in the table, sorted by the last attempt on top.
It will give you the time the IP was put in the list (the EntryTime) and also the date and time for the first and last attempt before the system caught it. Some attackers are very aggressive and hammer the system many times per second, and the NoAttempts column shows how many attempts they managed to perform before they were blocked. The FireWallRuleNo is the number of the firewall rule.

--Query how many IP addresses there are in each group
SELECT FireWallRuleNo,WhiteListed,WL.IP AS InWhiteListTable,COUNT(*) AS NoRecords 
FROM T_BlockedIPs B LEFT OUTER JOIN T_WhiteList WL ON WL.IP=B.IP
GROUP BY FireWallRuleNo,WhiteListed,WL.IP ORDER BY FireWallRuleNo  

The one with NULL as FireWallRuleNo is in the whitelist table (T_WhiteList).
If it is whitelisted in the T_BlockedIPs itself, it will be stated so in the WhiteListed field.
In this case, there are 156 IP addresses in firewall rule #1.

EXEC spUpdateBlockedIPs @GetFireWallRuleNo = 1

Then finally, make a SQL Agent job that runs the routine every 15 seconds (or whatever you find suitable). Normally, the routine takes only a second or so to run when there are no new IP addresses to block. And still just a few seconds if it finds IP addresses that should be blocked. I will not give instructions on how to created the SQL Agent job, as you can find this all over the internet.

The article from Ryan G Conrad also has some nice procedures that can create IP ranges for the whitelisting. So check that out as well.

Remarks

It is a good practice to always disable the sa account, and VPN is also a good solution. But as stated, sometimes you need the channel open, and this procedure will keep you safer.

Please feel free to comment on the code (which mostly is credited to Ryan G Conrad), and let me know of any bugs or improvements you could wish for.

History

• 24^th June, 2020: Initial version