将 SQL Server 连接限制为特定 IP 地址
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7127602/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Restrict an SQL Server connection to a specific IP address
提问by mohammedn
I want to restrict the connections to my SQL Server instance to specific IP addresses. I want to prevent any connections from any IP addresses except a specific list. Is this something that can be configured in the SQL Server instance or database?
我想将到我的 SQL Server 实例的连接限制为特定的 IP 地址。我想阻止来自除特定列表之外的任何 IP 地址的任何连接。这是可以在 SQL Server 实例或数据库中配置的东西吗？
回答by Aaron Bertrand
Sounds like something you'd do using the Windows firewall(you can block the SQL Server port(s), and allow exceptions for certain IP addresses).
听起来像您使用Windows 防火墙所做的事情（您可以阻止 SQL Server 端口，并允许某些 IP 地址例外）。
You could do this with something like a logon triggerthat checked the IP address using sys.dm_exec_connections but I think it's a much less desirable option than blocking the traffic outright.
您可以使用类似于使用 sys.dm_exec_connections 检查 IP 地址的登录触发器来执行此操作，但我认为与完全阻止流量相比，这是一个不太理想的选择。
Certainly much tougher to do at the database level.
当然，在数据库级别做起来要困难得多。
回答by Matt Smith
I wrote this functionality to auto ban an IP address that has made more than X (@FailedLoginAttempts) log in attempts from the same IP address. It is based on the SQL Server Error Logs. I am running a Windows Server 2008 and SQL Server 2008 R2.
我编写了此功能来自动禁止从同一 IP 地址尝试超过 X 个（@FailedLoginAttempts）登录尝试的 IP 地址。它基于 SQL Server 错误日志。我正在运行 Windows Server 2008 和 SQL Server 2008 R2。
Be advised if you have not cycled your SQL Server Error Logs in a while, you may get a great many IP addresses and it may take a little time to process everything. As I run this every 10 minutes the whole process takes about 4-5 seconds.
如果您有一段时间没有循环 SQL Server 错误日志，请注意，您可能会获得大量 IP 地址，并且可能需要一点时间来处理所有内容。因为我每 10 分钟运行一次，整个过程大约需要 4-5 秒。
Steps
脚步
- 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 hereis 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:
- 确保您正在记录失败的尝试。在 SQL Server Management Studio (SSMS) 中右键单击您的实例（在您的数据库上方）Properties→ Security→ Login auditing。确保 [仅限登录失败] || 的单选按钮 选择了[登录失败和成功登录]。
创建表来存储被禁止的 IP 地址
/* 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]创建进程以将 IP 地址自动添加到防火墙。我个人将此代码放在每 10 分钟运行一次的代理作业中。另请注意，此过程使用xp_cmdshell。我当然不想争论启用或禁用此功能的优点。每个人都有自己的，但如果没有这个功能，我的脚本将无法工作。如果您没有启用它，这里有一个很好的链接可以帮助您。
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:
I understand that this is not a perfect solution, because it only works with IPv4 IP addresses and only looks at log in attempts made through probably port 1433 depending on your configuration. However it has helped me identify and block over 100 IP addresses in a week or so (mainly China and Hong Kong, but I did block the Department of Homeland Security).
我知道这不是一个完美的解决方案，因为它仅适用于 IPv4 IP 地址，并且仅查看可能通过端口 1433 进行的登录尝试，具体取决于您的配置。然而，它帮助我在一周左右的时间内识别并阻止了 100 多个 IP 地址（主要是 CN 和香港，但我确实阻止了国土安全部）。
TANGENT- Once I ran this for a week or so I quickly realized there were a fair amount of patterns in the net ranges of the IP addresses. I found this toolto be most helpful to nail down who and where these hits were coming from. The thing that is great about this website is that once you get the location of the IP address, down below you can input the IP address again and get the net range of the IP address. For instance (sorry China), I found that 59.53.67.13 had a net range of 59.0.0.0 - 59.255.255.255. That being said, I created a manual function to block the entire net range and delete any Windows Firewall rules that already contained IP addresses in this range.
TANGENT- 一旦我运行了一个星期左右，我很快意识到 IP 地址的网络范围中有相当多的模式。我发现这个工具最有助于确定这些点击的来源和来源。这个网站的优点在于，一旦您获得 IP 地址的位置，在下方您可以再次输入 IP 地址并获得 IP 地址的网络范围。例如（对不起 CN ），我发现 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
I am looking forward to comments that improve this functionality.
我期待改进此功能的评论。
回答by Ashburn RK
- Use an external firewall like Baracuda or F5 - Best option so that you reduce the load on the Windows server.
- Windows Firewall Settings - When you can't do the above, set the incoming firewall settings and open port 1433 and in the Remote Computer tab enter your source IP address.
- At the SQL Server Networking Level - SQL Server Network Configuration* → Advanced→ Accepted NTLM SPNs. Add the domain names here.
- Follow Matt Smith's procedurewith trigger
- 使用 Baracuda 或 F5 等外部防火墙 - 最佳选项，以便减少 Windows 服务器上的负载。
- Windows 防火墙设置 - 如果您无法执行上述操作，请设置传入防火墙设置并打开端口 1433，然后在“远程计算机”选项卡中输入您的源 IP 地址。
- 在 SQL Server 网络级别 - SQL Server 网络配置* →高级→接受的 NTLM SPN。在此处添加域名。
- 使用触发器遵循马特史密斯的程序
