SQL Server: Troubleshooting Logon Triggers

Logon triggers were introduced in SQL server 2005 SP2. They 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 can be used to audit and control server sessions, in such usage scenarios as:

• Tracking login activity, you can create a user table to audit user sessions.
• Restricting logins to SQL Server, you can prevent a specific login from establishing a user session.
• limiting the number of sessions for a specific login, you can limit a specific login from establishing more than a specific number of user sessions to the instance.

You can extract useful XML data about Logon events inside Logon by using the EVENTDATA function. I will demonstrate a little later how we can use this function to audit log-ins to a user table.

How do Logon triggers work?

The life cycle of a Logon trigger is simple:

1. At beginning, a user connects and authenticates to SQL Server.
2. If the previous authentication is successful and the user has access to the instance, then the trigger fires. When the Logon trigger fires, an implicit transaction is opened and the logic inside the trigger executes.
3. After all the Logon triggers finish executing, the transaction commits and user is granted a session.

Note: You can define more than one Logon trigger on the server, by defining one as the first trigger and another as the last trigger by using the sp_settriggerorder system stored procedure. The implicit transaction then begins with the first trigger and commits after all Logon triggers finish execution. Microsoft advices against issuing COMMIT TRANSACTION statements inside Logon triggers, because it can decrement the transaction count to 0, and then SQL Server will return an error as it does with other trigger types.

A user session is not established if either of the following conditions occurs during execution of a trigger on a LOGON event:

1. The original implicit transaction is rolled back or fails such in case where a login qualifies for a banned list.
2. An error that has severity greater than 20 is raised inside the trigger body.

We are concerned more with the second case, where the Logon trigger could in some cases always fail because it encounters the same error each time.

A working example

Let’s examine a simple Logon trigger that audits log-ins on the server and logs them to a user table.

I will create the user table first. You can create it in the master database to ensure that the table is available. You need to be aware of table size and its impact on the database’s size, or you can create it in a dedicated user database. But you must ensure that the table will always be available, otherwise an error will happen and all user sessions will be denied.

Create Table master.dbo.audit_logins
(
Col_loginName varchar (50),
Col_LoginType varchar (50),
Col_LoginTime datetime,
Col_ClientHost varchar (50) 
)

GO

The table will hold the login name, the log-in type, the time of log-in, and the host from which the log-in request originated.

Now, let’s create the Logon trigger, and then close the session after that:

Create TRIGGER TR_audit_logins

ON ALL SERVER WITH EXECUTE AS 'sa'

FOR LOGON

AS

BEGIN

       declare @LogonTriggerData xml          

       set @LogonTriggerData = eventdata() ;

       Insert into master..audit_logins 

       Select @LogonTriggerData.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(50)'),

		  @LogonTriggerData.value('(/EVENT_INSTANCE/LoginType)[1]', 'varchar(50)'),

              @LogonTriggerData.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime'),         

              @LogonTriggerData.value('(/EVENT_INSTANCE/ClientHost)[1]', 'varchar(50)')          

end

The trigger extracts data into an XML variable using the eventdata function. Then we parse the XML variable to insert data into the previously created table.

Troubleshooting

Let’s assume that after a while that some other DBA or whomever changed the trigger logic and mistakenly mistyped the logging table in the trigger body, In that case the insert statement will fail. Or in another scenario, let’s say that you created the following trigger:

CREATE TRIGGER Lock_Out_Triger
ON ALL SERVER WITH EXECUTE AS 'SA'
FOR LOGON
AS
BEGIN
    ROLLBACK;
END;

You have simply locked yourself out, and any attempt to connect to the server using SSMS will show the following error:

Any other user application trying to connect to the instance will get the previous error as well. This is a serious issue—no one would be able to log in to the server (existing connections would continue to work normally). So, how to resolve this issue and be able to connect normally?

The options available are:

1. Fastest one: If you don’t know what exactly caused the error (such in case you have multiple Logon triggers) then you can disable all Logon triggers.
2. Disable the Logon trigger that caused the error if you are aware of it, or one trigger at a time.
3. Edit the trigger to correct the issue, drop and recreate it with correct logic.

For all above resolutions, where you won’t be able to log in normally, you have two options:

1. Use a dedicated administrator connection (DAC) to connect to the instance: connections via the DAC don’t fire login triggers. Note that only one DAC at a time can be connected to the instance. This the optimal solution as it doesn’t disconnect established sessions. This isn’t a known use of DAC, as it is referred to as a solution to terminate a heavy session if the server had maxed out the resources.
2. Start SQL Server with the Minimal Configuration start-up option: in this case only a single user can connect, remote access is disabled and start-up stored procedures do not run. This will terminate an already connected user and may terminate any already running SQL server jobs.

Note that if DAC isn’t available (it’s not enabled by default in SQL Server Express) then you will receive an error like this:

Note that, to enable DAC for SQL Server Express, you will need to add “-T7806” to the start-up parameters. In my testing, I have used SQL Server 2008 Express.

DAC is enabled only for local connections, i.e. you must initiate a DAC session from a local server. To enable remote DAC, you can use Sp_configure:

EXEC sp_configure 'remote admin connections', 1;
GO
RECONFIGURE;
GO

Let’s try to connect to the instance using DAC and see what we can do. Using SQL Server Management Studio, we can initiate a DAC session by connecting to Admin; in my case I used Admin:Server\SQL2K8X.

After successfully connecting, you can get list of Logon triggers created on the server by running the following query against the sys.server_triggers
and sys.server_events catalog views:

SELECT Name,parent_class_desc 'Class',tr.Type,tr_ev.Type_desc + '_' + tr.Type_desc 'Trigger_Type_Desc',is_ms_shipped,is_disabled 
FROM Master.sys.server_triggers tr
Inner Join Master.sys.server_trigger_events tr_ev
on tr.object_id = tr_ev.object_id

Here is the result:

It indicates that the triggers are enabled and that they are not MS_shipped, i.e: they are user-defined.

The fastest resolution, as indicated above, is to disable Logon triggers in order to give access to users and applications:

Disable Trigger All ON ALL Server;

Now try to connect normally, and there you go.

To disable one trigger only, you must specify its name:

Disable Trigger Lock_Out_Trigger ON ALL Server;

If you don’t want to disable any triggers and you can tolerate the downtime until you troubleshoot the issue, you will need to retrieve the code of the trigger(s) and modify it to omit any malicious part, then drop and recreate it.

You can get the definition of the trigger using:

Select Definition

From Master.sys.server_sql_modules sq
Inner Join Master.sys.server_triggers tr
on sq.object_id = tr.object_id

At this point you can edit the trigger code, drop and recreate it using the correct code. To drop a Logon trigger you can use:

DROP TRIGGER [tigger_name] ON ALL SERVER ;

An interesting point here is that, with Logon triggers, some errors that will halt execution in normal scripts will be skipped. I have tried this with the famous “String or binary data would be truncated.”.

I altered the Logon trigger and changed the following line to cast the LoginName to 10 characters:

Select @LogonTriggerData.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(10)')

Then I created a log-in with a length of more than 10 characters:

USE [master]
GO
CREATE LOGIN [user_with_long_username] WITH PASSWORD=N'password', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

I then tried to log in using it. And it works!

Retrieving the logs from table audit_logins, I got the following result:

Col_loginName       Col_LoginType     Col_LoginTime                       Col_ClientHost

user_with_          SQL Login         2008-10-19 10:42:20.450

It worked, and the trigger didn’t complain about data truncation.

What about using SQLCMD? SQLCMD is a popular troubleshooting utility because it is lightweight, and you can use it if you don’t have Management Studio installed on the server (we have clients who don’t).

All of the code we used above can be used exactly the same way in SQLCMD once you have successfully established, just take into consideration the following. If you want to return the definition of the Logon trigger, SQLCMD limits the output result by default to 256 characters. You will need to use -y parameter to increase the output like this:

SQLCMD -E -Sserver\sql2k8x -A -dmaster -y8000

To wrap this blog up, Logon triggers can be used to tighten up the security features of SQL Server, but you must be very careful when writing them as they can convert your server into a forbidden destination.

Enjoy!