目录
介绍
有时，您需要将SQL服务器暴露给internet，从而允许在不使用VPN的情况下从任何IP地址连接，从而降低SQL服务器被不需要的客户机攻击的可能性。通过检测失败的登录并阻止其IP地址一段时间，您很有可能让它们继续攻击另一台服务器。此代码创建防火墙规则，以阻止攻击者几次尝试。
背景
有一种解决方案是为每个应该被阻止的IP地址创建了一个防火墙规则，当阻止列表中有成百上千个地址时，这使情况变得一团糟。我的解决方案创建IP地址块（可配置），并将防火墙规则列表保持在可管理的数量。IP被阻止48小时（默认但可配置），之后，该IP地址将从列表中删除，并重新创建防火墙规则。
使用代码
此代码假定您没有使用该sa帐户，并且禁用该sa帐户始终是一个好习惯。如果该sa帐户正在使用中，则可以将其从代码中删除。攻击者似乎总是尝试使用不同的帐户，sa、su、admin、sys、mssql、kisadmin、bwsadmin似乎很常见。检查您的日志以找出他们攻击的哪些帐户未被您或您的客户使用。在读取日志的代码中，您可以更改AND和OR以获取所需的结果。
该代码只是将T-SQL放在存储过程中，两个表和一个SQL Agent作业。该过程可以放在同一SQL服务器上的任何数据库中，但是为了简便起见，此处使用master数据库。
该代码正在使用xp_cmdshell，默认情况下处于禁用状态。要启用它，请从“admin”帐户运行以下代码：
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服务以使更改生效。
在存储过程代码中，有一部分代码用于检查几个表的存在，如果不存在，则将创建它们。
获取失败的登录尝试的最佳方法（我发现）是使用审核。审计文件可以保持很小，并且比使用sp_readerrorlog更快地读取。
该代码还支持使用sp_readerrorlog读取日志，也可以通过运行sp_cycle_errorlog使其保持较小。
运行以下代码以制作审核文件。在运行之前，将FILEPATH更改为您的需求。
此安装程序仅使用2个文件创建2MB文件。这样可以快速查询。您始终可以使用@UserErrorLog=1来运行该过程，以从头开始填充要阻止的IP列表。
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阻止的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 <= @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('')),'
','')
--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一样运行它，没有任何参数。
- @UseReadErrorLog = 1将强制其使用内置sp_readerrorlog函数。如果攻击持续了一段时间，这可能会花费很长时间，并且会生成很长的IP地址列表。
- @RemakeFireWallRules = 1将重新排列/重新创建防火墙规则。如果有新的IP攻击系统，则只有最后一个fw规则没有被MaxIP填充。如果未更改任何规则，该过程将保留它们。当最后一次尝试阻止的IP地址比T_System中的IpBlockTime变量更早时，该IP地址将被删除，并且该过程将自动重新创建fw规则。
- @JustReadLog = 1不会影响防火墙规则。它只会读取审核日志（或使用sp_readerrorlog if @UseReadErrorLog = 1已设置）。
- @GetFireWallRuleNo = 1将从防火墙规则号获取信息。防火墙规则编号在T_BlockedIPs表中给出。使用以下信息读取信息netsh advfirewall firewall show rule name="Blocked SQL Attempts #01"
- @GetFireWallRuleTo = 1结合@GetFireWallRuleNo将循环从RuleNo到RuleTo并获取上述信息。
- @GetOnlyFireWallRuleName = 1 将仅显示防火墙名称，而不显示IP地址。
- @WhiteListIP = '[some IP address]'会将IP地址设置为T_BlockedIPs表格中的白名单。
- 还有一个自己的白名单表，其中T_WhiteList包含被列入白名单的IP地址。
- @GetFireWallRuleWithIP='196.28.236.74'将在防火墙规则中搜索给定的IP地址。与@GetOnlyFireWallRuleName结合使用，它将仅显示找到它的规则名称。
其他一些不错的查询是：
--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
这将为您提供表中所有被阻止IP的列表，并按最后一次的尝试排序。
它会为您提供IP放入列表（EntryTime）的时间，以及系统捕获到该IP 之前第一次尝试和最后一次尝试的日期和时间。一些攻击者非常激进，每秒都攻击系统多次，该NoAttempts列显示了在被阻止之前设法执行了多少次尝试。该FireWallRuleNo是防火墙规则的数量。
--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
使用NULL作为FireWallRuleNo的是在白名单表(T_WhiteList)中。
如果T_BlockedIPs本身被列入白名单，则会在WhiteListed字段中声明。
在这种情况下，防火墙规则1中有156个IP地址。
EXEC spUpdateBlockedIPs @GetFireWallRuleNo = 1
Then finally，使SQL Agent作业每15秒（或您认为合适的话）运行一次例程。通常，当没有新的IP地址要阻止时，该例程只需花费一秒钟左右的时间即可运行。如果发现应该阻止的IP地址，它仍然只有几秒钟。我将不提供有关如何创建SQL Agent作业的说明，因为您可以在Internet上找到所有内容。
备注
始终禁用sa帐户是一个好习惯，VPN也是一个很好的解决方案。但是如上所述，有时您需要打开通道，此过程将使您更加安全。
2671
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
