0

How do I allow remote connection to SQL Server instance only to specific public IP Address (white-list)?

For example, login from SSMS to SQL Server instance hosted on Windows dedicated server allowing access only from specific public IP.

Also, the need to restrict access to all other IP's, i,e to prevent login from SSMS.

Below is a source trigger: can I change to remotehost in order to allow for specific public IP access

EXEC sp_cycle_errorlog ;  
GO  
CREATE TRIGGER trigLogon_CheckForIPAddress
ON ALL SERVER
FOR LOGON
AS
BEGIN
  IF NOT EXISTS (
    SELECT
      client_net_address AS ipaddress
    FROM sys.dm_exec_connections
    WHERE session_id = @@spid
      AND ISNULL(client_net_address ,'Named Pipes?') IN('<localhost>','Named Pipes?','192.168.0.1','192.168.0.2','192.168.0.40') )
    BEGIN
      RAISERROR('Unauthorized use of login from inpermissible machine IP.', 16, 1)
      ROLLBACK
    END
END;
GO
ENABLE TRIGGER trigLogon_CheckForIPAddress ON ALL SERVER
Marco
  • 1,679
  • 3
  • 17
  • 31
Anirudh Shankar
  • 1
  • 1

2 Answers2

1

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.

Biren
  • 31
  • 1
  • 9
1

Don't.

Really, please don't.

Install a VPN.

symcbean
  • 19,931
  • 1
  • 29
  • 49