Hi
I have created one stored procedure for brute force attack, It automatically adds a firewall but does not happen, So please help me to do that.
CREATE PROC [dbo].[spUpdateBlockedIPs]
@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','x.x.x.%') --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 = 'x.x.x.x'
EXEC spUpdateBlockedIPs @GetFireWallRuleWithIP = 'xx.xx.xx.xx',@GetOnlyFireWallRuleName=1
--Put a new IP address into the whitelist table
INSERT INTO T_WhiteList VALUES('x.x.x.x')
--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 ''xx''%' --Make OR's to catch the accounts you want
OR Text LIKE 'Login failed for user ''xx''%'
OR Text LIKE 'Login failed for user ''xx''%'
OR Text LIKE 'Login failed for user ''xx''%'
OR Text LIKE 'Login failed for user ''xx''%'
OR Text LIKE 'Login failed for user ''xx''%')
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 ''xx''%' --Make OR's to catch the accounts
--you want
OR statement LIKE 'Login failed for user ''xx''%'
OR statement LIKE 'Login failed for user ''xxx''%'
OR statement LIKE 'Login failed for user ''xx''%'
OR statement LIKE 'Login failed for user ''xx''%'
OR statement LIKE 'Login failed for user ''xx''%')
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