Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I was testing a logon trigger to restrict connections to certain IPs. It looks like the trigger was not correct. Now I can't log in to the server even as SA.

Is there a way to fix this?

share|improve this question
2  
Can you log in using the dedicated admin connection? technet.microsoft.com/en-us/library/ms178068(v=sql.105).aspx – Mr.Brownstone Feb 12 at 15:56
up vote 14 down vote accepted

You can connect to the instance using the Dedicated Administrator Connection (DAC). It only accepts local connection by default.

Sqlcmd can be used:

sqlcmd -S localhost -U sa -P password -d master -A

The DAC is available and supported through the sqlcmd command-prompt utility using a special administrator switch (-A)

You can also connect prefixing admin: to the instance name in the format sqlcmd -Sadmin:<_instance_name_>.

SQL Server Management Studio can also be used:

You can also initiate a DAC from a SQL Server Management Studio Query Editor by connecting to admin:<_instance_name_>.

However trying to establish a connection from Object Explorer using Connect to Server in SSMS will fail with this message:

Dedicated administrator connections are not supported via SSMS as it establishes multiple connections by design.

From a disconnected Query Editor or connected with a regular user, reconnect using either of:

  • Query menu / Connection / Change Connection
  • Popup menu / Connection / Change Connection

Once connected, DISABLE TRIGGER can then be executed:

DISABLE TRIGGER logonTrigger ON ALL SERVER;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.