SQLTeam.com | Weblogs | Forums

Brute Force attack on sql server

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 :slight_smile:
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('')),'&#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

I'm curious as to why you're maintaining your firewall settings in SQL Server. Generally a firewall or other network traffic control is maintained at a higher level than an individual server (it's a better practice anyway). If you're strictly limiting the firewall for just this machine, storing the blocklist/allowlist in a database is less than ideal. If nothing else, if your firewall is breached and someone ends up accessing your database, they can wipe out this data, and you could lose it permanently unless you have database backups.

Calling xp_cmdshell to run netsh utility is also less than ideal. If your firewall settings are not being picked up, it could be that the SQL Server service account does not have the appropriate permissions to set machine-wide firewall settings. The service account would probably need to have at least local administrator permissions, which in itself is a security liability. Again, anyone breaching your SQL Server could use these permissions to take control and remove firewall settings.

As you've probably realized, building and executing command-line strings in SQL is a bit tedious. If you haven't considered PowerShell, you might want to, as it's better designed to construct and execute system level commands. It can also be better secured against intrusion, and can still use the IP lists in the database.

Also, while using SQL Audit to capture failed login and similar events is a good idea, querying it as an alert mechanism is not. You may have better luck using Event Notifications, which can execute a stored procedure which queries the SQL Audit for more info (apologies if you're already doing this, it wasn't mentioned in the pasted text). This is still less than ideal for a brute force or other network intrusion, database servers are not optimized for such things.

2 Likes

The attacker uses port scanning techniques to identify the open ports on the target system. Once the attacker found port 1433/1434 in an open state, it starts brute-forcing the SA login which is a default administrator account. The attacker usually holds a dictionary with the most common passwords used by database administrators, thus making the attack faster and successful in most cases.