Auditing SQL Server using LOGON Trigger

This blog is about creating a LOGON trigger in SQL for auditing purpose.

Logon trigger is basically used for auditing, and controlling logins or sessions on SQL Server.

But it could be used for several purposes like :

1. Deny access for particular client application(eg. Clients connecting from ODBC , OLEdb etc.)
2. Allow login for certain time period in a day.

Tracking login activity

And many more.

Creating a LOGON trigger

Here in following code snippet I have created a simple Logon trigger for : If someone is connecting with SQL Server from any other client application a part form SSMS(SQL Server Management Studio.)
Will get an Error: “Logon failed for sa due to trigger execution.”

CREATE TRIGGER [DenyAccess]

ON ALL SERVER WITH EXECUTE AS 'sa'  

FOR LOGON    

AS    

BEGIN    

DECLARE @data XML  

SET @data = EVENTDATA()    

DECLARE @AppName sysname      

SELECT @AppName = [program_name]    

FROM sys.dm_exec_sessions    

WHERE session_id = @data.value('(/EVENT_INSTANCE/SPID)[1]', 'int')    

IF (@AppName not in ('Microsoft SQL Server Management Studio') )    

begin    

RAISERROR (15600,-1,-1, 'Can not access form ODBC') WITH LOG,NOWAIT    

ROLLBACK; --Disconnect the session    

End;  

End;  

Uses

I have described simple example here for better understanding. You can use LOGON trigger according to your need (ex. You can deny access based on host-name, ip address range.. etc.)

You can also write all these failure and success event in DB.