SQL Server: Troubleshoot Login Trigger

Purpose: To document the procedure required to remediate a loss of SQL Server access caused by a logon trigger.

Background

As part of our standard SQL Server build, we include a logon trigger that inserts valuable auditing information for successful logons to a table in the master database. However, there is a serious risk associated with the use of the logon trigger. If an error occurs that would prevent inserting data into the table (e.g. someone renamed the table), all login attempts will fail.

Process

This process is predicated upon use of the Dedicated Administrator Connection (DAC). The DAC can be thought of intuitively as a back door into the server.  Please note that by default the DAC is only enabled for accounts logged onto the local machine (allowing remote DAC can be configured).  As such, you will likely have to RDP to the specific server.  Furthermore, only one person can be connected to the DAC at a time.

1. RDP to the server
2. Open SSMS
3. Click New Query.  Note: the object explorer will NOT connect via DAC.
4. Add admin: prior to the server name and use a credential that is a member of the sysadmin role:
   1. admin:serverinstance
5. Click connect
6. In the resulting query window, run the following query: disable trigger All on All Server;
7. This will disable all server triggers and re-enable logons
8. Disconnect from the DAC connection
9. Triage and resolve the issue that caused the logon trigger to fail.
10. Re-enable the logon trigger as desired.