0

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!

  • is the server case sensitive ? have you check this support.microsoft.com/en-us/help/2002062/… ? – Kin Shah Jun 12 '18 at 15:08
  • We could make a bunch of guesses, but it would probably be better if you show all of your logon triggers, obfuscating any sensitive info of course, and explain why you need "a number of them." The most I've ever seen is 1. – Aaron Bertrand Jun 12 '18 at 15:12
  • The server is not case sensitive. The triggers also do not insert anything to a table that doesn't exist (unless ORIGINAL_LOGIN() and APP_NAME() does), and I'd hope that the logins have access to those functions! EventData is also not explicitly used. Thanks though! – The Great Laughter Jun 12 '18 at 15:14
  • Aaron, there is no good reason for the 17 logon triggers, and I'll be consolidating them in the near future into just a single one. At the time, it was easier to create a new trigger per login, but that was a mistake. I've edited in the exact trigger, but it is generated based off of a template, so all the other triggers are the same. Is there value in posting all 17? Thanks – The Great Laughter Jun 12 '18 at 15:33
  • Do your apps use connection pooling? – Alen Jun 12 '18 at 16:09
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.

  • I will add some logging like you suggested when I revamp these to all be a single trigger. Thank you for the idea! – The Great Laughter Jun 12 '18 at 19:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.