Tuesday, August 4, 2015

SQL- Logon Triggers

LOGON Triggers are another beautiful feature of SQL. It is used to track the 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 means maintain the log in attempts to SQL Server initiated by a particular login. We should need to be careful when setting them up because it can be easily locked users out of the server and / or cause other problems.

Similar to DDL Triggers, LOGON Triggers fire stored procedures or T-SQL statements in response to LOGON events.  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. Logon triggers do not fire if authentication fails.
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, 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.

For example, in the following code, the logon trigger denies log in attempts to SQL Server initiated by login msbi_datatools if there are already three user sessions created by that login:
USE master;
GO

-- create the new login
CREATE LOGIN msbi_datatools
WITH PASSWORD = 'M@c123' MUST_CHANGE,
    CHECK_EXPIRATION = ON;
GO

-- Grant permission
GRANT VIEW SERVER STATE TO msbi_datatools ;
GO

-- create trigger to fire when connection limits meet the condition
CREATE TRIGGER LogonTrigger_ConnectionLimit
ON ALL SERVER WITH EXECUTE AS 'msbi_datatools'
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= 'msbi_datatools' AND (SELECT COUNT(*) FROM sys.dm_exec_sessions
            WHERE is_user_process = 1 AND original_login_name = 'msbi_datatools') > 3
            Begin
                                                Print 'You are logged more than authorised sessions' ;      
                                                ROLLBACK;
                                                End
END;

You can easily associate multiple triggers on the LOGON event and any one of these triggers can be designated the first or last trigger to be fired on an event by using the sp_settriggerorder system stored procedure.  You should keep in mind that SQL Server does not guarantee the execution order of the remaining triggers.
Use msdb
Go
-- create CaptureLogin Details to store every login information
CREATE TABLE CaptureLoginDetails
(LogonTime datetime,
LoginName varchar(50),
ClientHost varchar(50),
LoginType varchar(50)
)

-- Logon Trigger to fill the audit log table
CREATE TRIGGER LogonTrigger_CaptureLogin
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN

-- declare local variable to store value
declare @LogonTriggerData xml, @EventTime datetime, @LoginName varchar(50),
@HostName varchar(50), @LoginType varchar(50)

-- set value from the event data     
set @LogonTriggerData = eventdata()
set @EventTime = @LogonTriggerData.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime')
set @LoginName = @LogonTriggerData.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(50)')
set @HostName = @LogonTriggerData.value('(/EVENT_INSTANCE/ClientHost)[1]', 'varchar(50)')
set @LoginType = @LogonTriggerData.value('(/EVENT_INSTANCE/LoginType)[1]', 'varchar(50)')

-- check condition here
if @LoginType = 'SQL Login' and @HostName not in ('sa,') and @LoginName not in ('sa')
  Begin
 
 -- fill the data into logon capture detail table
  Insert into Msdb..CaptureLoginDetails (LogonTime,LoginName,ClientHost,LoginType)
  Values(@EventTime,@LoginName,@HostName,@LoginType)
  End
             
end


Managing Transactions
The life-cycle of a logon trigger is very simple: a user connects to SQL Server, the Logon trigger fires; an implicit transaction is opened and if you want to deny the attempt to log in to SQL Server, just issue a ROLLBACK statement and life-cycle is completed. This involve the following steps-
  • Before SQL Server fires a logon trigger, SQL Server creates an implicit transaction that is independent from any user transaction.
  • Whenever the first logon trigger starts firing, the transaction count is 1. After all the logon triggers finish executing, the transaction commits. As with other types of triggers, SQL Server returns an error if a logon trigger finishes execution with a transaction count of 0.
  • The ROLLBACK TRANSACTION statement resets the transaction count to 0, even if the statement is issued inside a nested transaction. COMMIT TRANSACTION might decrements the transaction count to 0. 
  • In the real practice, avoid using COMMIT TRANSACTION statements inside logon triggers.
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.

Click on Triggers to know more.
Click on Benefits of Triggers to know more.
Click on Disadvantages of Triggers to know more.
Click on SQL - DML Triggers to know more.
Click on SQL - DDL Triggers to know more.
References:
https://technet.microsoft.com/en-us/library/bb326598(v=sql.130).aspx

No comments:

Post a Comment

Popular Posts