Restrict an SQL Server connection to a specific IP address

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.

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

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

2. Create the table to store banned IP addresses

   /* Create table to store banned IP addresses */USE [YourDB]GOCREATE 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]

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

1. Use an external firewall like Baracuda or F5 - Best option so that you reduce the load on the Windows server.
2. 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.
3. At the SQL Server Networking Level - SQL Server Network Configuration* → Advanced → Accepted NTLM SPNs. Add the domain names here.
4. Follow Matt Smith's procedure with trigger