The internet abounds with documentation and blog posts about logon triggers, introduced to SQL Server 2005 in service pack 2. Recently I had the opportunity to work with them developing an imperfect solution to provide a temporary bandage for a vendor's poor SQL security design; a recipe for delving into the dusty, forgotten corners of the functionality. From that experience, combined with some help from , I've come away with a couple hints and tips, all of which were tested on SQL Server 2005 SP2.
Errors In Your Trigger Can Prevent Everyone From Connecting
Let's say you've created a trigger, but made the mistake of referencing a table that doesn't exist in the trigger's logic. Well, that error is going to prevent anyone, even administrators, from connecting to the server because of the trigger execution. Yikes!
Failures to connect to SQL Server because of a logon trigger are logged to your SQL log file. Fortunately, an error like a non-existent table will also show up in the log file as part of that logon trigger failure to connect. You should be able to use this to give you clues on how to fix your logon trigger.
Know How You Are Going to Disable the Trigger
So, you probably already know that you can use a DAC (Dedicated Administrator Connection) to get into SQL Server, but it's a little more complicated than just using admin:. You're probably panicking because you've launched SSMS and are trying to connect using admin:, but you keep getting a message saying 'Dedicated administrator connections are not support. (ObjectExplorer)'. Reading closely you'll see that you can't use Object Explorer with a DAC. Trying connecting via a Query instead.
If you're DBA who doesn't have access to the server on which SQL Server resides, you'll want to make sure you have remote DAC enabled on your server before you start your logon trigger work, either via the SQL Server Surface Area Configuration for Features or sp_configure.
Also, if you try to connect via a DAC and see this error message from SSMS:
A connection was successfully established with the server, but then an error occurred during the login process. (provider: TCP Provider, error: 0 - An established connection was aborted by the software in your host machine.) (Microsoft SQL Server, Error : 10053)
or this error message from sqlcmd:
HResult 0x2745, Level 16, State 1
TCP Provider: An established connection was aborted by the software in your host machine. Sqlcmd: Error: Microsoft SQL Native Client : Communication link failure.
...then it means that one of your co-workers, or your evil subconscious, have already made the single DAC connection allowed to a server, and you'll have to track them down and wrestle the DAC away from them.
If you don't have SQL Browser running, you might not be able to connect via a DAC either, receiving some long error about the SQL Browser and port numbers in sqlcmd, or in SSMS about a 'network-related or instance-specific error'. In this case, you'll need to check your SQL error log to find the port that the DAC is listening on, and then use sqlcmd -S, to connect to the server.
Know How To Find The Triggers
If you use descriptive names like me and can't remember the exact spelling, you need to be able to find the exact name of the trigger once you've connected via a DAC. The system view sys.server_triggers is your friend. Armed with the name of your logon trigger from this view, DISABLE TRIGGER ON ALL SERVER will help you out of your jam. Or, if you're feeling particular vindictive, DROP TRIGGER ON ALL SERVER will work too.
What if you have multiple logon triggers and don't know which one is stopping you? I'm afraid you'll have to use a process of elimination and the sys.server_triggers.is_disabled column to help you keep track of where you are at.
Although, there is a way to create your logon trigger so that it both prevents a user from connecting AND logs that failed connection to a table you create. That sounds like a good topic for my next blog post.
Thanks go out to @MladenPrajdic and @afernandez on Twitter, both very smart, helpful people whom you should follow, for the inspiration for this post.