我注意到大量定期从特定 IP 地址使用 SA 帐户进行登录尝试。我想使用 SQL 触发器或其他一些方法来根据用户的 IP 地址阻止这些用户访问数据库。
我能够创建一个数据库表来通过一个存储过程收集失败的尝试，我计划安排该存储过程每 10 分钟左右运行一次......
这是我的数据库表：
'''
USE [MASTER]
GO
CREATE TABLE [master].[dbo].[BLockedIPAddresses](
[id] [int] IDENTITY(1,1) NOT NULL,
[ip] [varchar](50) NOT NULL,
[attacked_on] [datetime2](2) NOT NULL,
[banned_on] [datetime2](7) NOT NULL,
[number_login_attempts] [int] NULL,
CONSTRAINT [PK_BLockedIPAddresses] PRIMARY KEY CLUSTERED
([id] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]) ON [PRIMARY]
ALTER TABLE [dbo].[BLockedIPAddresses] ADD CONSTRAINT [DF_BLockedIPAddresses_banned_on] DEFAULT (getdate()) FOR [banned_on]
'''
然后，我创建了一个存储过程来更新并用尝试使用 SA 帐户登录的新 IP 填充数据库表。
'''
CREATE PROCEDURE [dbo].[sp_BLockedIPAddresses]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
-- [dbo].[BLockedIPAddresses]
DECLARE @T TABLE(LogDate datetime,ProcessInfo varchar(200),Text varchar(max))
DECLARE @T2 TABLE(LogDate datetime,ProcessInfo varchar(200),IPAddress varchar(max))
DECLARE @T3 TABLE(LogDate datetime,IPAddress varchar(max))
DECLARE @IPAddress varchar(50),@LogDate datetime,@NumLoginAttempts int,@CmdExc varchar(300),@FailedLoginAttempts int=10
BEGIN /* Get error log records with failed login attempt data */
INSERT INTO @T
EXEC sp_readerrorlog 0,1,'Password did not match that for the login provided'
INSERT INTO @T
EXEC sp_readerrorlog 1,1,'Password did not match that for the login provided'
INSERT INTO @T
EXEC sp_readerrorlog 2,1,'Password did not match that for the login provided'
INSERT INTO @T
EXEC sp_readerrorlog 3,1,'Password did not match that for the login provided'
INSERT INTO @T
EXEC sp_readerrorlog 4,1,'Password did not match that for the login provided'
Select * from @T
EXEC sp_readerrorlog 5,1,'Password did not match that for the login provided'
Select * from @T
EXEC sp_readerrorlog 6,1,'Password did not match that for the login provided'
--Select * from @T
END
BEGIN /* Get the IP address from T*/
INSERT INTO @T2
SELECT LogDate,ProcessInfo,REPLACE(REPLACE( SUBSTRING(Text, PATINDEX ('%[0-9].%[0-9].%[0-9].[0-9]%',Text)-2,50),']',''),':','') FROM @T
END
BEGIN /* Get the NEW ip addresses from T2*/
INSERT INTO @T3
SELECT CONVERT(varchar(10),LogDate,101) LogDate,IPAddress from @T2 T
WHERE NOT EXISTS(SELECT * FROM [master].[dbo].[BLockedIPAddresses] ai WHERE ai.ip=T.IPAddress)
GROUP BY CONVERT(varchar(10),LogDate,101),IPAddress
HAVING COUNT(LogDate)>@FailedLoginAttempts
ORDER BY IPAddress
END
BEGIN /* Validate that T3 has records, if not skip the firewall add */
IF (SELECT COUNT(*) FROM @T3)=0
BEGIN
GOTO ExitWithoutCycle
END
END
BEGIN /* Loop through T3 and add each entry to the windows firewall */
WHILE EXISTS(SELECT * FROM @T3)
BEGIN
SELECT TOP(1) @LogDate=LogDate, @IPAddress=IPAddress FROM @T3
SELECT @NumLoginAttempts=COUNT(*) FROM @T2 WHERE IPAddress=@IPAddress
INSERT INTO [master].[dbo].[BLockedIPAddresses] (attacked_on,ip,number_login_attempts) VALUES(@LogDate,@IPAddress,@NumLoginAttempts)
--SET @CmdExc = 'netsh advfirewall firewall add rule name="Autobanned IP - SQL Attacked '+@IPAddress+'" dir=in action=block enable="yes" remoteip='+@IPAddress+' protocol=any interfacetype=any'
--EXEC master..xp_cmdshell @CmdExc
DELETE @T3 WHERE IPAddress=@IPAddress
END
END
/* sp_cycle_errorlog archives the current error log. */
EXEC sp_cycle_errorlog
ExitWithoutCycle:
END
'''
但是，这种方法对我不起作用。我仍然在数据库日志文件上看到登录尝试。
我可以获得可行的方法建议来减轻对服务器的这种安全攻击吗？
