Few days ago I wrote a logon trigger that prevents unauthorized applications from connecting to the development database server. The trigger code was simple and spans only a few lines of code and I just executed the script. And after a couple of seconds, I realized a logical mistake! Due to the logical error in the code, every user trying to connect was kicked out of the server. And as expected lot of complaints were piled up in front of admin’s desk in no time (unfortunately that’s my desk).
I was thinking of dropping the logon trigger and replied them back, “Don’t worry dude. I’m the admin and I’m here to help you. This will be resolved in no time.”
I tried to connect to the server. Oh my! The server is not accepting even my connection request. So the story begins here.
“Hey, I’m the King, the mighty Administrator. You are not allowing me to enter my castle?” asked the Administrator.
“Hey, King. I’m the Logon trigger, the valiant gate keeper. I’m instructed to prevent anyone who enters
through the gate even you the King!”
“What! But who instructed you that!”
“You, the King, and your ignorant instructions”
“I, the mighty Administrator, hereby order that, I will give half of my kingdom to the one who kills this Gate keeper!” the mighty Administrator requested for help.
And here comes the valiant knight DAC. The Dedicated Administrator Connection. DAC ingeniously made another way to reach the castle and the King killed the gate keeper. In the end the King, the mighty Administrator, announced the amended instructions to his people, and gave half of the Kingdom to DAC the Knight. The king and the people in the kingdom lived happily thereafter.
Long story short
The dedicated administrator connection is a special connection that allow administrator to enter a running server to troubleshoot, even if the server is unresponsive. The DAC bypasses even the logon trigger. The administrator can connect to the server through DAC by prefixing ADMIN: before server name box in SSMS connect window, or using –A command switch in sqlcmd. Since DAC is a single thread, don’t expect any query parallelism. There many limitations in DAC. The below Microsoft article gives a detailed list on this.
You can also check whether a DAC scheduler is running or not by querying the DMV sys.dm_os_schedulers. The status column will contain a value ‘VISIBLE ONLINE (DAC)’ that indicates a DAC scheduler is running.
CAUTION: Only use DAC if the situation extremely demands.
End of Story: I connected to the server using DAC and dropped the logon trigger. Then corrected the trigger and compiled.