时间戳

首页 > 解决方案 > 如何在 SQL Server 中创建触发器以阻止来自特定 IP 地址的登录用户？

sql - 如何在 SQL Server 中创建触发器以阻止来自特定 IP 地址的登录用户？

问题描述

我注意到定期使用特定 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

'''

但是，这种方法对我不起作用。我仍然在数据库日志文件上看到登录尝试。

我能否获得一个可行的方法建议来减轻对服务器的这种安全攻击？

标签： sqlsql-server

解决方案

我喜欢这个想法，但除此之外，我建议先执行以下操作：

  1. 始终禁用生产服务器上的帐户，在禁用之前在服务器上创建具有权限的sa单独帐户sysadminsa
  2. 避免将所有登录名添加到sysadmin角色中，登录名可能需要db_owner特定数据库而不是整个服务器的权限。
  3. 在服务器上创建 WMI 警报，只要登录/角色/用户发生更改，就会通过电子邮件通知。有关详细步骤在 SQL Server 上创建 WMI 警报
  4. 为 SQL Server 及其实例使用非标准端口（1433、1434 除外）
  5. 确保未将不需要的用户帐户添加到builtin\administrators运行 SQL 服务的服务器组中
  6. 不确定它是否适用于您的环境，但最好避免暴露整个网络可访问的数据库服务器 IP，只要应用程序服务器可以访问数据库服务器就足够了。对于管理活动，用户必须通过应用服务器的网络/VLAN 访问数据库服务器

推荐阅读