Connecting to SQL Server with a Bad Logon Trigger

By: K. Brian Kelley   |   Comments (7)   |   Related: > Triggers

Problem

In a previous tip, SQL Server 2005 SysAdmin Login Auditing, I wrote about how to use a LOGON trigger to capture all logon activity.  One of the things you need to aware of is that if you create a logon trigger and there is some bad code you are going to prevent everyone from logging into your SQL Server, even if you try as "sa" or a member of the sysadmin fixed server role. How do I log on to my SQL Server to fix this trigger?

Solution

If you created a trigger for LOGON and you have some bad code in the trigger, when you attempt to log on, you're going to get an error similar to the one shown in Figure 1.

Figure 1:

The bad execution of the logon trigger is causing the logon attempt to fail.

For instance, the following code is designed to fail if the trigger is created:

There is no database known as BadDB and that means there is also no table within BadDB known as SomeTable. As a result, any normal attempts to logon the server will fail because the trigger is referring to a non-existent object. In order to correct this, you'll either need to:

• Use an existing established connection that has the appropriate rights.
• Use the Dedicated Admin Connection (DAC) to connect to SQL Server

If you have an existing connection that has the ability to drop or disable the trigger, use that existing connection to correct the problem. But in the likely event that you don't, you will need to rely on the DAC.

By default, the DAC is set up to only be available on the local server. That means you'll need to connect either by logging onto the computer locally or by using another means such as Remote Desktop. Once you're logged on, you can either use SQLCMD or SQL Server Management Studio (SSMS).

If you use SQLCMD, you'll want to specify the -A switch to connect using the DAC. If you're connecting via SSMS, be sure to do so by specifying ADMIN: before the name of the server, such as in Figure 2.

Figure 2:

The reason this works is that SQL Server minimizes the checks and resources for a connection via the DAC. This was done to give DBAs a "back door" for when one or more runaway processes is consuming a SQL Server such that a normal login isn't possible. One of the things SQL Server does not do when connecting via the DAC is execute any logon triggers. As a result, you can use the DAC and you won't be blocked by the bad trigger. Then you can disable or delete the trigger as necessary.

For instance, once connected via the DAC, I could execute the following command to get rid of the trigger entirely:

Next Steps

• If you use LOGON triggers or any process that may get you into trouble remember that the Dedicated Administrator Connection (DAC) is there to help solve you problem
• Here is another tip about the DAC - Dedicated Administrator Connection in SQL Server 2005
• Take a look at these other DDL Trigger tips

About the author

K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips