Monday, September 17, 2012

SQL Server: Disable Logon Trigger Using DAC to Resolve Login Problem


Recently I have received a mail from one of blog reader, who explained his problem as following:
“I have tried scrip to create logon trigger from your blog post Restrict Login from Valid Machine IPs Only (Using Logon Trigger) BUT problem is that, I forgot to put localhost in my safe list, and now I am unable to login to my instance.”

Well, if same happened to you, then you need to login using Dedicated Administrator Connection. What is DAC and how to you use it Read This.
DAC can be established using sqlcmd or through SSMS. On command prompt, type this to establish connection.

Sqlcmd –S localhost –d master –A
You can provide instance name instead of localhost. Next thing is to disable our logon trigger, using following command.

DISABLE TRIGGER tr_LogOn_CheckIP ON ALL SERVER
Where “tr_LogOn_CheckIP” is the name of our logon trigger. On next line type GO to execute DISABLE command.


Now you can login to your database server. Once login, check out trigger is disabled.

You can achieve all this through SQL Server Management Studio. To establishing dedicated connection, click on  FILE----NEW----Database Engine Query

Login through valid SYSADMIN user, by providing server name with extra word and a colon, i.e. Admin:

In query window, type same tsql and execute to disable trigger.

And never forget to add your server IP or <localhost> in safe list, while creating logon trigger.

2 comments:

  1. Nice Article. Resolved my issue today.

    Thanks,
    Pavan Kumar KVN

    ReplyDelete

All suggestions are welcome