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
Is there a way to fix this?
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. It only takes a minute to sign up.Sign up to join this community
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
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
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:
Once connected, DISABLE TRIGGER can then be executed:
DISABLE TRIGGER logonTrigger ON ALL SERVER;