SQL Server Logon Trigger Preventing Connections

sql servertrigger

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?

Best Answer

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;
Related Question