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 I’m 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 I’m going to use this.
My Idea is to 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.
- 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
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
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, 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.*')
Now I’m going to connect to this SQL Server from the IP address 192.168.1.4, now the trigger should block the connection.
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
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.
hi nice article….thanks
I have one doubt …how to find index usage on sql server 2000.. is there any script for it..
please let me know
Nope, there is no direct way to find it. Only hope is to use profiler
Hello, I implemented this method and it worked great. Thanks. But when I found out I was blocking a valid IP address. I removed the IP from the table IPBlock. Still couldn’t login. Restarted SQL server. Didn’t help. In urgency, I renamed the table to “IPBlock_off”. I logged off & restarted the service, only to find out now that I couldn’t log in myself, even with ‘sa’ account. HELP!!!!
Thanks very much.
-Allan
Hi, I also have the same problem as AllanV, I cannot log onto the SQL instance at all from any IP.
Please could you give some assistance as to how to log in.
Allan \ Chris,
Sorry I couldn’t turn up immediately to me, I was busy with other works.
In such a situation you can make use of dedicated administrator connection to disable or drop the trigger, renaming the trigger will not help you
. Only login’s with server admin role can connect as dedicated administrator connection (DAC). Please follow the steps below to drop or disable the trigger
1.) Launch command prompt (go to run and type cmd and press enter)
2.) Type the command below, either use windows or SQL authentication command. DAC allows you to connect from even blocked IP addresss FYI
--Connecting with Dedicated Admin Connection with Windows authenticationSQLCMD -S ServerName -E
--Connecting with Dedicated Admin Connection with SQL authentication
SQLCMD -S ServerName -U SA -P password
3.) Run any of the following TSQL code to disable or delete the trigger.
--Disabling the TriggerDISABLE TRIGGER block_ipaddress ON ALL SERVER
--Delete or Drop the Trigger
DROP TRIGGER block_ipaddress ON ALL SERVER
Thanks Vidhya.
In addition, in step #2, if those 2 options failed, try:
>SQLCMD -S ServerName -U sa -P sa_password -d master -A
Hope this will help everyone else.
Thanks Allan.. This looks wonderful
Hi ,
Iam having the same problem i couldnt open my sql , i run all the commands , iam new to sqlserver please help me out of this situation
Declare @ipaddress nvarchar(15)
set @ipaddress =(select EVENTDATA().value
(‘(/EVENT_INSTANCE/ClientHost)[1]‘,’nvarchar(15)’));
create table validadresses
(ip nvarchar(15),
constraint pk_validadress primary key clustered(ip));
insert into validadresses(ip) values (’192.168.1.108′)
create trigger tn_logon_checkip
on ALL SERVER
for LOGON
as
begin
if is_srvrolemember(‘sysadmin’) =1
begin
Declare @ipaddress nvarchar(15);
set @ipaddress =(select EVENTDATA().value
(‘(/EVENT_INSTANCE/ClientHost)[1]‘,’nvarchar(15)’));
if not exists
(select ip from validadresses where ip =@ipaddress)
rollback;
end;
end;
This was the query i executed iam unable to access my Sqlserver please help me out
HI,
i have same problem.
but above 3 commands are not working.
please help me.
@Kalyan — In your script you have added sysadmin filter so whoever logs in with sysadmin permission will be rejected. Why you have added that filter?
Do you have any other login with ALTER and CONTROL SERVER permissions? If yes login with that ID and disable this server level trigger.
@Kalyan — Only problem is the filter sysadmin you have added in your script. Try to login with some other ID and disable the trigger
Nice one.. Great article
Very Nice sagar………Am very happy now i can block a entire server
Hi this is a good article thanks for it but I have a problem with this.
is this trigger allows only to sa user? cant I implement it to enable for all users on sql?
Hi.. This script is not specific for login, it’s specific to IP address so you can use this to SQL logins you can add that filter
Hi! I tried your script and it seemed to work fine for sa account. But all other accounts got blocked and couldnt log….
@Nuno — Can you try the comments I have given to Allan, that will help you
Well, I solved the problem at the time, just deleted the trigger.
I just dont understand why, while only checking the IP, the trigger is blocking login on every account with or without valid IP except the ‘sa’ account!