On Twitter today
@anonythemouse tweeted the following "any good #sql links for login triggers to block hostnames from logging in? #sqlhelp". Looked like an interesting question - I thought I would give the question a try and see if I could come up with an answer.
Following scripts are based upon researching the following links from the SQL Server 2008 Books Online (BOL):
Below are two possible options to restrict access by IP to SQL Server (developed and tested on SQL Server 2008 R2 Ent Eval Ed CTP Nov only):
Blacklist Option (Clients that are blocked):
CREATE TRIGGER tr_logon_hostname_blacklist
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
DECLARE @ClientHost nvarchar(max);
SELECT @ClientHost = EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]','nvarchar(max)');
-- ClientHost gives IP except if the connecting to SQL Server from instance machine.
-- Do NOT put '<local machine>' in this otherwise you will block client access from instance machine.
IF @ClientHost IN ('10.0.2.53'
,'10.0.2.54'
,'10.0.2.55'
,'10.0.2.56')
ROLLBACK;
END;
Whitelist Option (Clients that are allowed access):
CREATE TRIGGER tr_logon_hostname_whitelist
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
DECLARE @ClientHost nvarchar(max);
SELECT @ClientHost = EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]','nvarchar(max)');
-- ClientHost gives IP except if the connecting to SQL Server from instance machine.
IF @ClientHost NOT IN ('<local machine>' -- client is on the instance box (Do NOT change or remove!)
,'10.0.2.54'
,'10.0.2.55'
,'10.0.2.56')
ROLLBACK;
END;
Please note that the '<local machine>' string should NOT be modified or removed from the Whitelist trigger - you want to continue to be able to connect to your SQL Server instance from the instance box itself correct!?! Before running these queries please make sure that you have an open connection in another Query window in SSMS with the following script to drop the Logon Trigger just in case you make a mistake. You do not want to break your instance!
DROP TRIGGER tr_logon_hostname_blacklist
ON ALL SERVER;
DROP TRIGGER tr_logon_hostname_whitelist
ON ALL SERVER;
Fair Warning: Please note that Logon Triggers can be potentially instance breaking things. Especially if they include a ROLLBACK as the two scripts above include. Strongly recommend testing retesting and reretesting scripts fully on a Test box before even considering getting near a Production box. Make sure you have a separate open query window in SSMS or preferrably an already open SQLCMD session with the DROP TRIGGER script at the ready just in case you break things.
Please share any feedback or ideas that you may have. Thanks!