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