Connecting to SQL Server with a Bad Logon Trigger
Written By: K. Brian Kelley -- 11/20/2008
-- read/post comments
-- print --
Rating:
(not rated yet)
Rate
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
Readers Who Read This Tip Also Read
Free Live Webcast
Comment or Ask Questions About This Tip
|
