Auditing SQL Server using LOGON Trigger

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.