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!