Create a table and put the IP’s 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.
Creating a table and storing IP address
I’m 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
DECLARE @capturedip NVARCHAR(15);
SET @capturedip = (SELECT EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)', 'NVARCHAR(15)'));
IF EXISTS(SELECT ipaddress FROM master.dbo.IPBLock WHERE ipaddress = @capturedip)
Print 'Your IP Address is blocked, Contact Administrator'
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)
Print 'Your IP Address Range is blocked, Contact Administrator'
Testing the Trigger
To test this trigger, I’m 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.*')