Restrict an SQL Server connection to a specific IP

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?

回答1:

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).

You could do this with something like a logon trigger that checked the IP address using sys.dm_exec_connections but I think it's a much less desirable option than blocking the traffic outright.

Certainly much tougher to do at the database level.

回答2:

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.

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.

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 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:

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).

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 tool to 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.

    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.

回答3:

I suppose you could write a logon trigger as described here that checks where they're logging in from, but I'd suggest that it would be better to use a firewall.

回答4:

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 procedure with trigger