Subscribe

SQLServer-DBA.com Links

Subscribe to newsletter

Dba_db2_button

Powered by TypePad
SQL Server – Restrict SQL Server Logons by IP with EVENTDATA and SQL Logon Trigger

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)[1]', 'NVARCHAR(50)')
       SET @EVENTDATE = @LogonTriggerXML.value('(/EVENT_INSTANCE/PostTime)[1]', 'DATETIME')
       
       INSERT INTO DBA_TOOLS..logon_attempts values (@EVENTDATE, @IP)
       IF EXISTS (SELECT ip FROM DBA_TOOLS..restricted_ips)
       BEGIN
			ROLLBACK
		END
       
END
go

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)

******** *******

Subscribe to SQLServer-DBA.com

Latest posts delivered to you daily

Delivered by FeedBurner

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer