Enabling admin connection on SQL Server Express to fix logon trigger
7
votes
2
answers
3535
views
Well, luckily I got this error on my test environment. I'm so scared about this error that I disabled all logon triggers I was using on production environment.
I created the famous logon trigger, and I put a database in offline mode just to mess with something else. Then I discover that because the trigger can't find the table, I can't access the instance anymore. There are plenty of questions related to this, but even doing everything I could find, I still can't acces the instance, not even with SQLCMD.
I'm doing this:
sqlcmd -S server\instance -U sa -P -A
If I don't use
-A
I receive the famous error:
logon failed for sa due trigger execution.
If I use -A
:
> Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : SQL Server
> Network Interfaces: An error occurred while obtaining the dedicated
> administrator connection (DAC) port. Make sure that SQL Browser is
> running, or check the error log for the port number [xFFFFFFFF]. .
> Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : Login timeout
> expired. Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : A
> network-related or instance-specific error has occurred while
> establishing a connection to SQL Server. Server is not found or not
> accessible. Check if instance name is correct and if SQL Server is
> configured to allow remote connections. For more information see SQL
> Server Books Online..
Why i'm not able to connect to the sql server
sql server express here.
I could enable SQL Browser, but I still can't access it.
I don't think this question is duplicated because is not about the trigger, is about Why I can't access the server with SQLCMD. I pretty much know how to fix this. I just don't know how can I acces sql server without triggering the trigger.
Asked by Racer SQL
(7546 rep)
Mar 16, 2018, 07:12 PM
Last activity: Mar 17, 2018, 09:55 AM
Last activity: Mar 17, 2018, 09:55 AM