将SQL Server连接限制为特定IP地址

Question

将SQL Server连接限制为特定IP地址

sqlsql-serversecuritysql-server-2005sql-server-2008

26

我希望限制连接到我的 SQL Server 实例的 IP 地址，只允许来自特定 IP 列表的连接，防止其他任何 IP 地址的连接。这是否可以在 SQL Server 实例或数据库中进行配置？

- mohammedn

3

听起来这是一件完全合理的事情。让我感到尴尬的是，SQL Server没有这个功能（而MySQL却有这个能力）。 - Aaron Kempf

5个回答

26

我编写了此功能，用于自动禁止已从同一IP地址尝试登录X（@FailedLoginAttempts）次的IP地址。它基于SQL Server错误日志。我正在运行Windows Server 2008和SQL Server 2008 R2。

请注意，如果您有一段时间没有循环使用SQL Server错误日志，则可能会得到许多IP地址，并且处理所有内容可能需要一些时间。由于我每10分钟运行一次此操作，整个过程大约需要4-5秒钟。

步骤：

Insure you are logging failed attempts. In SQL Server Management Studio (SSMS) right click your instance (above your databases) Properties → Security → Login auditing. Make sure the radio button for either [Failed logins only] || [Both failed and successful logins] is selected.

Create the table to store banned IP addresses

/* Create table to store banned IP addresses */
USE [YourDB]
GO

CREATE TABLE [dbo].[autobanned_ipaddesses](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [ipaddress] [varchar](50) NOT NULL,
    [attacked_on] [datetime2](2) NOT NULL,
    [banned_on] [datetime2](7) NOT NULL,
    [number_login_attempts] [int] NULL,
 CONSTRAINT [PK_autobanned_ipaddesses] 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].[autobanned_ipaddesses] ADD  CONSTRAINT   [DF_autobanned_ipaddesses_banned_on]  DEFAULT (getdate()) FOR [banned_on]

Create the process to auto add IP addresses to the firewall. Personally I placed this code in an Agent Job running every 10 minutes. Also note that this process utilizes xp_cmdshell. I certainly don't want to debate the merits of enabling or disabling this functionality. To each their own, but my script won't work without this functionality. If you don't have it enabled here is a good link to help you.

USE [YourDB]
    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,'Could not find a login matching the name provided'
        INSERT INTO @T
        EXEC sp_readerrorlog 0,1,'An error occurred while evaluating the password'
    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 autobanned_ipaddesses ai WHERE ai.ipaddress=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 autobanned_ipaddesses (attacked_on,ipaddress,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:

我理解这并不是一个完美的解决方案，因为它只适用于IPv4 IP地址，并且仅查看通过可能的端口1433进行的登录尝试。然而，它已经帮助我在一周左右的时间内识别和阻止了100多个IP地址（主要来自中国和香港，但我也阻止了国土安全部）。 TANGENT - 运行了一周左右后，我很快意识到IP地址的网络范围存在相当数量的模式。我发现这个工具非常有帮助，可以确定这些访问来自哪里。这个网站的好处在于，一旦你得到了IP地址的位置，在下面你可以再次输入IP地址并获得其网络范围。例如（对不起中国），我发现59.53.67.13的网络范围是59.0.0.0-59.255.255.255。话虽如此，我创建了一个手动函数来阻止整个网络范围，并删除任何已包含该范围内IP地址的Windows防火墙规则。

    USE [YourDB]

    DECLARE @CmdExc varchar(300)
    DECLARE @NetRange varchar(50)='59.0.0.0 - 59.255.255.255'

    DECLARE @NetRangeFrom varchar(20),@NetRangeTo varchar(20),@IPAddress varchar(20)
    DECLARE @IPPart2From int,@IPPart2To int
    DECLARE @IPPartSearch2From int,@IPPartSearch2To int

    DECLARE @T Table (ipaddress varchar(20))

    SET @NetRange=REPLACE(@NetRange,' ','')
    SELECT @NetRangeFrom=LTRIM(RTRIM(SUBSTRING(@NetRange,1,CHARINDEX('-',@NetRange)-1)))
    SELECT @NetRangeTO=LTRIM(RTRIM(SUBSTRING(@NetRange,CHARINDEX('-',@NetRange)+1,50)))
    SELECT @IPPartSearch2From=CAST(PARSENAME(@NetRangeFrom,3) as int)
    SELECT @IPPartSearch2To=CAST(PARSENAME(@NetRangeTo,3) as int)

    INSERT INTO @T
    select ai.ipaddress from autobanned_ipaddesses ai where LTRIM(ai.ipaddress) like SUBSTRING(@NetRangeFrom,1,CHARINDEX('.',@NetRangeFrom,1))+'%' AND PARSENAME(LTRIM(RTRIM(ai.ipaddress)),3) BETWEEN @IPPartSearch2From AND @IPPartSearch2To

    SET @CmdExc = 'netsh advfirewall firewall add rule name="AB SQL Attacked '+@NetRange+'" dir=in action=block enable="yes" remoteip='+@NetRange
    EXEC master..xp_cmdshell @CmdExc
    WHILE EXISTS(SELECT * from @T)
    BEGIN
        SELECT TOP(1) @IPAddress=ipaddress from @T
        SET @CmdExc = 'netsh advfirewall firewall delete rule name="Autobanned IP - SQL Attacked '+@IPAddress+'"'
        EXEC master..xp_cmdshell @CmdExc
        DELETE TOP(1) FROM @T
    END

我期待能够收到改进这个功能的评论。

- Matt Smith

1

非常有趣的方法！不幸的是，当我尝试添加防火墙规则时出现了“所请求的操作需要提升（以管理员身份运行）”的错误。我仍在努力寻找一种运行此操作的方法... - HansLindgren

与http://stackoverflow.com/questions/19192210/re-enable-remote-desktop-windows-firewall-rule-on-windows-azure类似的问题。 - HansLindgren

@matt smith，我的 SQL 服务器正在遭受暴力攻击，所以我会尝试使用您的存储过程，这正是我要找的。非常感谢您。 - Artemination

@HansLindgren 我遇到了同样的问题：“请求的操作需要提升（以管理员身份运行）。” 但是当我创建一个 SQL Server 代理作业时，我发现在“T-SQL 步骤”中有“以用户身份运行”的选项。当您在“以用户身份运行”中指定管理员时，不会出现错误。 - Husain Sanwerwala

就我个人而言，我将这段代码放在一个代理作业中，每10分钟运行一次。您如何做到这一点？ - Yoni

3

使用外部防火墙，如Baracuda或F5 - 最佳选择，这样可以减轻Windows服务器的负担。
Windows防火墙设置 - 当您无法执行上述操作时，请设置传入防火墙设置并打开端口1433，并在远程计算机选项卡中输入您的源IP地址。
在SQL Server网络层面上 - SQL Server网络配置* → 高级 → 接受的NTLM SPN。在此处添加域名。
按照 Matt Smith 的程序进行操作，使用触发器。

- Ashburn RK

1

我想你可以编写一个登录触发器，就像这里所描述的那样，检查他们从哪里登录，但我建议最好使用防火墙。

- Hans Olsson

1

请注意，这并不会阻止连接本身，只是防止成功登录。 - Aaron Bertrand

0

我完全同意Peter Mortensen的回答。如果你想要这个系统能够良好地扩展，首先应该尝试获取专用硬件。如果没有，那么使用Windows防火墙。你的服务器只需要关注它的任务，也就是作为一个数据库。这就像开发一样，每个项目都应该有单一的职责或原因。

- Yaniv

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Aaron Bertrand · Accepted Answer

29

听起来像是可以使用 Windows 防火墙（您可以阻止 SQL Server 端口，并允许某些 IP 地址的例外）完成此操作。

您可以使用诸如登录触发器对 IP 地址进行检查，但我认为与其直接阻止流量相比，这是一个不太理想的选择。

在数据库层面上实现会更加困难。

- Aaron Bertrand

10

外部思考 +1！使用适合的工具完成任务 :) （原文为英文） - Oded

1

感谢您的回答。是的，在防火墙中执行此操作是有意义的，但我认为这应该作为SQL Server的安全上层提供。 - mohammedn

并不是所有情况都可以通过阻止IP地址来解决，因为连接SQL Server的方式有很多种，比如共享内存、命名管道、TCP/IP等，而且使用IP地址时它们可能会改变（例如在ISP级别上或者使用ipconfig /renew进行DHCP时），也可能处于不同子网、VPN中等。 - Aaron Bertrand

2

另外值得一提的是，现在的 SQL Server 默认情况下非常安全。您必须费尽心思才能让流量进入。只需看看“SQL Server 不允许远程连接”错误由于更安全的默认设置而在 SO/SF 上出现的频率即可：http://is.gd/zAuLcV / http://is.gd/7PZOIC……抱歉这些链接太长了（可能无法完整放在评论中）。 - Aaron Bertrand

有人能确认一下Windows防火墙是否真的可以做到这一点吗？我可能漏掉了什么，但我只能为本地Windows网络中的项目（计算机、用户等）添加例外，而不能为来自互联网的任意IP地址添加例外。 - Álvaro González

显示剩余2条评论