Sample Header Ad - 728x90

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