14 August,2012 by Jack Vamvas
SQL Server EVENTDATA function and SQL Logon Triggers are an effective method to restrict attempted logons from specified IP addresses.
A server owner requested a solution to restrict access from certain IP sources. By creating a Restricted IP table – the Logon Trigger executed a check against the table and could ROLLBACK the logon attempt.
EVENTDATA returns information about server and database events. The EVENTATA function can also be executed within a Logon Trigger.
The Logon Trigger executes a stored procedure in response to the LOGON event. Once the logon has authenticated but before the user session. Information returned by the LOGON event include: PostTime, SPID, LoginName
In the example below, the aim is to restrict logon from specified ip addresses.:
1) A restricted IP table is created
2) An attempted SQL Server logon log table is created.
3) A TRIGGER is created based on ALL SERVER Logon attempts. The TRIGGER executes a IF EXISTS condition check on the RestrictedIP table. If the clientHost matches an IP address in the table – the logon attempt is forced to ROLLBACK.In this example , the Restricted IP address is '10.103.11.13'
USE DBA_TOOLS --create restricted ip table CREATE TABLE restricted_ips (ip VARCHAR(50) ) --log table of logons CREATE TABLE logon_attempts (eventDate DATETIME, ip VARCHAR(50) ) --add a restricted ip INSERT INTO DBA_TOOLS..restricted_ips VALUES('10.103.11.13') --create trigger CREATE TRIGGER LogonTrigger ON ALL SERVER WITH EXECUTE AS 'sa' FOR LOGON AS BEGIN DECLARE @LogonTriggerXML XML, @IP NVARCHAR(50), @EVENTDATE DATETIME SET @LogonTriggerXML = EVENTDATA() SET @IP = @LogonTriggerXML.value('(/EVENT_INSTANCE/ClientHost)', 'NVARCHAR(50)') SET @EVENTDATE = @LogonTriggerXML.value('(/EVENT_INSTANCE/PostTime)', 'DATETIME') INSERT INTO DBA_TOOLS..logon_attempts values (@EVENTDATE, @IP) IF EXISTS (SELECT ip FROM DBA_TOOLS..restricted_ips) BEGIN ROLLBACK END END go