Restrict an SQL Server Connection to a Specific Ip Address

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.

How to restrict connection to database using IP

Here is the article which exactly matches your criteria. IP Address blocking

Restrict SQL Login on a MS SQL Database based on Host Name

You probably want to take a look at Logon Triggers which fire after the authentication phase but before the user session gets established, e.g.:

USE master;  
GO
CREATE TRIGGER host_name_reject_trigger
ON ALL SERVER
FOR LOGON  
AS
BEGIN
    IF HOST_NAME() in (N'bad_host', N'worse_host', N'the_worst')
        ROLLBACK;
END;

Someone connecting from bad_host, worse_host or the_worst in this example will receive an error message similar to the following:

Logon failed for login 'sa' due to trigger execution.
Changed database context to 'master'.
Changed language setting to us_english. (Microsoft SQL Server, Error: 17892)

Of course this relies on HOST_NAME() returning what you would term a "correct" result. The HOST_NAME (Transact-SQL) specifically says:

Important

The client application provides the workstation name and can provide inaccurate data. Do not rely upon HOST_NAME as a security feature.

You should try to find some mechanism other than HOST_NAME() to identify logins that should be rejected.

Block remote connection on SQL server and allow only local connection

I fixed this by entering the entire IP range of the local subnet in the allowed address and I was able to access DB server from my APP server and blocking all other remote connections.

---

Related Topics