2 out of 3 rated this helpful - Rate this topic

Logon Triggers

SQL Server 2008 R2

Logon triggers fire stored procedures in response to a LOGON event. This event is raised when a user session is established with an instance of SQL Server. Logon triggers fire after the authentication phase of logging in finishes, but before the user session is actually established. Therefore, all messages originating inside the trigger that would typically reach the user, such as error messages and messages from the PRINT statement, are diverted to the SQL Server error log. Logon triggers do not fire if authentication fails.

You can use logon triggers to audit and control server sessions, such as by tracking login activity, restricting logins to SQL Server, or limiting the number of sessions for a specific login. For example, in the following code, the logon trigger denies log in attempts to SQL Server initiated by login login_test if there are already three user sessions created by that login.

Transact-SQL

USE master;
GO
CREATE LOGIN login_test WITH PASSWORD = '3KHJ6dhx(0xVYsdf' MUST_CHANGE,
    CHECK_EXPIRATION = ON;
GO
GRANT VIEW SERVER STATE TO login_test;
GO
CREATE TRIGGER connection_limit_trigger
ON ALL SERVER WITH EXECUTE AS 'login_test'
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= 'login_test' AND
    (SELECT COUNT(*) FROM sys.dm_exec_sessions
            WHERE is_user_process = 1 AND
                original_login_name = 'login_test') > 3
    ROLLBACK;
END;

Note that the LOGON event corresponds to the AUDIT_LOGIN SQL Trace event, which can be used in event notifications. The primary difference between triggers and event notifications is that triggers are raised synchronously with events, whereas event notifications are asynchronous. This means, for example, that if you want to stop a session from being established, you must use a logon trigger. An event notification on an AUDIT_LOGIN event cannot be used for this purpose.

Disabling a Logon Trigger

A logon trigger can effectively prevent successful connections to the Database Engine for all users, including members of the sysadmin fixed server role. When a logon trigger is preventing connections, members of the sysadmin fixed server role can connect by using the dedicated administrator connection, or by starting the Database Engine in minimal configuration mode (-f). For more information, see How to: Use the Dedicated Administrator Connection with SQL Server Management Studio and Using the SQL Server Service Startup Options.

Capturing Logon Trigger Event Data

To capture XML data about LOGON events for use inside logon triggers, use the EVENTDATA function. For more information, see Designing and Implementing Structured Storage (Database Engine). The LOGON event returns the following event data schema:

<EVENT_INSTANCE>

<EventType>event_type</EventType>

<ServerName>server_name</ServerName>

<LoginName>login_name</LoginName>

<LoginType>login_type</LoginType>

<ClientHost>client_host</ClientHost>

<IsPooled>is_pooled</IsPooled>

</EVENT_INSTANCE>

<EventType>: Contains LOGON.
<PostTime>: Contains the time when a session is requested to be established.
<SID>: Contains the base 64-encoded binary stream of the security identification number (SID) for the specified login name.
<ClientHost>: Contains the host name of the client from where the connection is made. The value is '<local_machine>' if the client and server name are the same. Otherwise, the value is the IP address of the client.
<IsPooled>: Is 1 if the connection is reused by using connection pooling. Otherwise, the value is 0.