Logon Trigger Woes

T-SQL-Tuesday-Logo

This month’s TSQLTuesday is hosted by Steve Jones on the topic of triggers!

In a large enough environment, eventually the question will be asked “is this database still being used?” There’s a few technologies available to help you answer this question and by far the worst is logon triggers.

I work in a large mixed environment (SQL Server, Oracle, MySQL, YourSQL, etc…) and logon triggers have been used here for a very long time. Specifically on the Oracle servers, logon triggers are used to track who is connecting to the database from where. To a lesser extent, they’re also used to control where connections come from. So the idea of using logon triggers to identify if a SQL Server was still being used seemed very natural to the team.

Logon triggers were something I learned for a 2008 certification exam but I have never seen them put to use in a SQL Server environment.

Setting up a Logon Trigger

First, I created a database with a table to capture login information. Columns for the login, the timestamp, and the host they rode in on:

CREATE DATABASE [DBAInfo]

USE [DBAInfo]
CREATE TABLE [dbo].[LoginInfo](
[Login_Name] [nvarchar](256) NULL,
[Login_Time] [datetime] NULL,
[Host_Name] [nvarchar](128) NULL
) ON [PRIMARY]

I wrote my logon trigger to delete any old Login\Host records before inserting the new record. The idea being that I really only care about the most recent login. I also don’t want this table to cause any growth concerns and I also don’t want to put a purge job in place. Having the DELETE in the trigger takes care of all those concerns (but adds new ones, more on that below):

CREATE TRIGGER [LastLogon]
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
DECLARE @hostname NVARCHAR(128)

--Capture the hostname
SELECT @hostname = EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]','NVARCHAR(128)')

--Remove old Login\Hostname records
DELETE FROM DBAInfo.dbo.LoginInfo
WHERE [Login_Name] = ORIGINAL_LOGIN()
AND [Host_Name] = @hostname

--Insert the new record
INSERT INTO DBAInfo.dbo.LoginInfo
SELECT ORIGINAL_LOGIN(), GETDATE(),
EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]','NVARCHAR(128)')
END
GO

The trigger works as expected, recording all logins. As a DBA, I like having a table to query and sort to my liking.

Ooops

But what happens when the trigger fails? My first brush with this was when I dropped the database containing the logon trigger. This causes an error in the trigger and fails the logon! No users or application accounts can login, including myself.

The error message provided is “Logon failed for login due to trigger execution” and there are two ways to fix it.
1. Logon with the dedicated administrator connection (DAC) and disable\drop the trigger.
1. Restart SQL Server in single user mode and disable\drop the trigger.

While I may be able to go the rest of my career without breaking this trigger, another DBA might not. Mistakes happen to all of us and having a server’s availability hinge on a single table or database strikes me as very risky.

Deadlocks

I implemented this trigger on a test system that was being monitored by Idera SQL Diagnostic Manager. Idera was creating so many connections that it was deadlocking due to the logon trigger. Both failed logons and deadlocks are logged in the SQL Server Error log:
Idera_Deadlocks

Summary

My logon trigger, as written, cannot go into production. If you are forced to use a logon trigger in production, here’s some thoughts:

  1. Make sure you have error handling in place. The trigger can never fail or you will lock users out of the instance.
  2. Make sure you have remote DAC configured (and know how to use it!), for cases when the trigger does fail.
  3. Limit the transactions in the trigger to avoid deadlocking.

If you’re like me and need to get a grip on who’s loging into your server, a better solution is really to use an audit to capture logins (post coming in the future on that), a trace, or a third party tool. There are many ways this trigger could be re-written to prevent these issues but IMO logon triggers are too risky for critical environments.

One thought on “Logon Trigger Woes”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s