ERROR:17892 Database login issue due to logon-trigger

I was working with Logon triggers and was stuck-up in a very interesting issue. Tried to create one trigger named “Logon_trigger” which writes auditing information in audit table in DB named “TestDB”. Once I finished my testing, I dropped the database [TestDB] without dropping the trigger. Unfortunately when I tried to connect to SSMS after that, I got the following error:

error

Thought of connecting thru DAC to drop the trigger and rectify the issue. Interestingly, was not able to connect thru DAC and got the following error:

DAC error

Here is how I rectified the issue:
Connected to the SQL Server Configuration Manger: Start >> All Programs >> SQL Server 2008 R2 >> Configuration Tools >> SQL Server Configuration Manager
Added a trace flag to enable DAC (-T7806), as shown in the screen below and then re-started the service:

services

Tried to connect using DAC again and execute below commands:

connected

Connected using SSMS successfully this time:-)

Advertisements

One comment

  1. KA · November 13, 2014

    WOW just what I was searching for. Came here by searching for backlinko twitter

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s