Logon Trigger preventing any connection

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?

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:.

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:.

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;

StackExchange Database Administrators Q#129115, answer score: 15