"Logon failed for login 'sa' due to trigger execution." When No SA Login Triggers Defined

Question

Last night, I received some error messages in my SQL Server instance stating:

"Logon failed for login 'sa' due to trigger execution."

The instance has a number of logon triggers enabled, but after verifying them, none of them would reject the 'sa' login. The logon triggers that exist are all in this format:

CREATE TRIGGER [forbid_bar_connection_for_foo]
ON ALL SERVER 
FOR LOGON
AS 
BEGIN
    IF ORIGINAL_LOGIN() = 'foo' AND APP_NAME() like 'bar%'
    ROLLBACK;
END;
GO

and they exist as a stop-gap until we can get proper security in place. (I know that this is insanely easy to bypass.)

In none of the triggers do we check ORIGINAL_LOGIN() against 'sa'.

Around the same time that I saw this for the first time (followed by many of the actual logon triggers firing, far more than they should've), there appears to have been an out of memory event. A new database was being brought in, and there's a stack dump preceding this by about 30 seconds regarding a non-yielding IOCP Listener.

My assumption is that there was a memory issue at play, and this caused the logon triggers to not fire correctly, and thus rollback access to sa. Is this reasonable? Is there another explanation for a logon trigger not allowing a login that it does not check against?

Thanks for any insight!

Answer 1

"Logon failed for login 'sa' due to trigger execution."

For security reasons logon triggers don't report errors to the end user, but they are captured in the SQL Log. From Docs:

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

Is there another explanation for a logon trigger not allowing a login that it does not check against?

Of course you could always have a bug in one of your logon triggers. Check the SQL Logs. And of the Logs don't clearly indicate which trigger failed and why, then add more telemetry (PRINT or RAISERROR) to your triggers so that next time they will.