防止对SQL Server的蛮力攻击

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

重新启动MSSQL服务以使更改生效。

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

阻止的IP列表保存在一个名为T_BlockedIPs的表中（如果不存在，它将在第一次运行时自动创建）：

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

是时候创建存储过程本身了：

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  '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('')),'
','') 
    
    --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

现在，您应该可以运行该存储过程了。在下面，您可以找到运行它的一些方法：

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

默认情况是像EXEC spUpdateBlockedIP一样运行它，没有任何参数。

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

EXEC spUpdateBlockedIPs @GetFireWallRuleNo = 1