IP Address Blocking or Restriction in SQL Server

I have seen some questions in forums asking for the answer is it possible to block connections to SQL Server based on IP address. As far as now there is no official way in SQL Server to block the connections in SQL Server based on IP address. However this can be done from the OS end, we have the following three options available, refer HERE for more.

  • Firewall
  • IPSec
  • RRAS IP Filter

However from SQL Server 2005 MS has introduced Logon triggers. We can create a logon trigger and we capture the IP address of the client machine and there by we can block the connection. In this case Im going to use ClientHost from EVENTDATA function to get the IP address, when you connect to SQL Server from local machine you will get the name, if you connect it from client machine this will return the IP address hence Im going to use this.

My Idea is to create a table and put the IPs to be blocked in that table, while checking the IP we can get the data from this table and we can decide whether to block it or allow it. So the entire process will be like below.

  • Create a table and store IP’s to be blocked
  • Create a DDL Logon trigger and block IPs based the table

Creating a table and storing IP address

Im going to create a table in master database and store the IPs.

CREATE TABLE master.dbo.IPBLock (ipaddress VARCHAR(15))

Create a DDL Logon trigger

This trigger will block all the connections from the IP address however you can add some more filters in the trigger to allow admin connections, or system admin etc

CREATE TRIGGER block_ipaddress
ON ALL SERVER
FOR LOGON
AS
BEGIN
            DECLARE @capturedip NVARCHAR(15);
            SET @capturedip = (SELECT EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]', 'NVARCHAR(15)'));
            IF EXISTS(SELECT ipaddress FROM master.dbo.IPBLock WHERE ipaddress = @capturedip)
            BEGIN
                        Print 'Your IP Address is blocked, Contact Administrator'
                        ROLLBACK
            END
            ELSE
            BEGIN
                        DECLARE @IPRange VARCHAR(15)
                        SELECT @IPRange= SUBSTRING(@capturedip,1,LEN(@capturedip)-CHARINDEX('.',REVERSE(@capturedip)))+'.*'
                        IF EXISTS(SELECT ipaddress FROM master.dbo.IPBLock WHERE ipaddress = @IPRange)
                        BEGIN
                            Print 'Your IP Address Range is blocked, Contact Administrator'
                            ROLLBACK
                        END
            END
END
GO 

Testing the Trigger

To test this trigger, Im going to insert some IP address into the table to block their connection. You can also insert IP range in to the table.

INSERT INTO IPBLock VALUES('192.168.1.3')
INSERT INTO IPBLock VALUES('192.168.1.4')
INSERT INTO IPBLock VALUES('10.100.25.*')

ip_block_1

Now Im going to connect to this SQL Server from the IP address 192.168.1.4, now the trigger should block the connection.

ip_block_2

From the image above you can see the login to the server is blocked because of trigger execution. The value which we printed in the trigger will be written to errorlog as below

ip_block_3

Incase if you face any problem after creating this trigger, then you can either empty the IPBlock table else drop this server level trigger. I would like to hear your suggestions concerns about this article, please post it in our discussion board.


Posted

in

by

Comments

6 responses to “IP Address Blocking or Restriction in SQL Server”

  1. Wolfgang Werner avatar
    Wolfgang Werner

    Your Logon Trigger fails if the Login, which tries to log in, does not have select permission on you IPBlock table. Your example works, because you use sa to test.

  2. Mahesh Kumar avatar

    Good for Security of Ip level.

  3. Jiri avatar
    Jiri

    You don’t need to have the IPBlock table in the master db, but you can use table variable inside the trigger instead.

  4. Hari avatar
    Hari

    Good technique. Thanks for sharing it. It works great and helped me block a user session that kept spawning new sessions indefinitely.

  5. Augustine S. avatar

    Hi thanks for the post, can I restrict connection to my database (in Sql server 2012) according to Computer Name?
    Consider that I have a table that capture Computer name and want SQL to only accept connections from computer in our database and deny access to all computer Name that does not exist in our database.

  6. Ingrid avatar
    Ingrid

    This query worked very well for me, thank you very much

Leave a Reply

Your email address will not be published. Required fields are marked *