Click here to Skip to main content
15,436,489 members
Articles / Database Development / SQL Server / SQL Server 2008
Technical Blog
Posted 17 Sep 2012

Stats

24.9K views
3 bookmarked

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

Rate me:
Please Sign up or sign in to vote.
5.00/5 (1 vote)
17 Sep 2012CPOL1 min read
Using logon trigger, users from valid IP addresses can be restricted, but what if you forgot to add in safe list. You will be unable to login though you have sysadmin rights. Use DAC to disable logon trigger.
 

Recently, I received a mail from one of the blog readers, who explained his problem as follows:

“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.”

Sqlcmd –S localhost –d master –A

Well, if the 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.

You can provide instance name instead of localhost. The next thing is to disable our logon trigger, using the following command.

SQL
DISABLE TRIGGER tr_LogOn_CheckIP ON ALL SERVER

Where tr_LogOn_CheckIP is the name of our logon trigger. On the next line, type GO to execute DISABLE command.

Image 1

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

Image 2

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

Image 3

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

Image 4

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

Image 5

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

Image 6

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Team Leader CureMD
Pakistan Pakistan
Aasim Abdullah is working as SQL Server DBA with CureMD (www.curemd.com) based in NY, USA. He has been working with SQL Server since 2007 (Version 2005) and has used it in many projects as a developer, administrator, database designer. Aasim's primary interest is SQL Server performance tuning. If he finds the time, he like to sketch faces with graphite pencils.

Comments and Discussions

 
GeneralMy vote of 5 Pin
junaid10931-Jan-19 2:45
Memberjunaid10931-Jan-19 2:45 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.