Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Connecting to SQL Server with a Bad Logon Trigger


By:   |   Read Comments (7)   |   Related Tips: More > Triggers

Attend a SQL Server Conference for FREE >> click to learn more


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:

CREATE TRIGGER BadLogonTrigger ON ALL SERVER FOR LOGON
AS
BEGIN
    INSERT INTO BadDB.dbo.SomeTable VALUES ('Test');
END;
GO

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:

DROP TRIGGER BadLogonTrigger ON ALL SERVER;
GO
Next Steps


Last Update:


signup button

next tip button



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

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Thursday, July 12, 2012 - 5:15:56 AM - Phil Back To Top

Is it possible to use Powershell to delete a logon trigger that has locked down a DB


Friday, September 04, 2009 - 6:30:20 AM - tgerade Back To Top

I got it!

I had forgotten to put the "on ALL Server" at the end of my drop statement.

Thanks so much everyone.

TG


Friday, September 04, 2009 - 6:25:10 AM - tgerade Back To Top

Hi,

Thanks for your response.

Msg 3701, Level 11, State 5, Server CISDEVMST01,  Line 1
Cannot drop the trigger 'Trigger_ServerLogon', because it does not exist or you
do not have permission.

TG


Friday, September 04, 2009 - 6:23:48 AM - ALZDBA Back To Top

It is strange sa cannot drop that trigger, however maybe just disable it to bypass your original problem.

and search for a solution if things are back running as normal.


Friday, September 04, 2009 - 6:19:36 AM - K. Brian Kelley Back To Top

Can you post the exact error message you receive when trying to delete the trigger?


Friday, September 04, 2009 - 6:08:18 AM - tgerade Back To Top

Hi,

I tried the tip and did end up with the Bad login.  However, when I connect DAC as sa it states I do not have permission to delete the trigger.   Please help if you can.

Thank you.

TG


Monday, November 24, 2008 - 1:17:01 AM - ALZDBA Back To Top

Your article describes exactly the issue I ran into and how i undid the issue using the DAC.

 My new solution uses event notifications, because it is just a logging application that should not block anything in real time.

I posted my little but drastic mishap at SQLserver central to be published in the near future. 

 


Learn more about SQL Server tools