7

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?

15

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;
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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