5

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

4

The documentation for "Diagnostic Connection for Database Administrators" states:

  • To conserve resources, SQL Server Express does not listen on the DAC port unless started with a trace flag 7806.

That TF needs to be specified either on the command line, if you are starting SQL Server via the command line, or in the Registry if you are starting via the Services interface (the typical method).

To get this into the Registry:

  1. open a command prompt
  2. run regedit
  3. navigate to the following key:

    Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL{version_number}.{instance_name}\MSSQLServer\Parameters

    where {version_number} is the 2-digit number of the release, such as "11" for SQL Server 2012, and "14" for SQL Server 2017.

  4. Add the following String Value:

    SQLArg3

  5. Edit the new String Value to provide the following value data:

    -T7806

    Please note: upper-case "T", and no space between the "-T" and the number!

  • Wow thank you. I was going to answer my question because I could find that same page about DAC. I was really scared. the more you make wrong stuff, the more you learn. – Racer SQL Mar 16 '18 at 19:43
  • 1
    no problem! I just updated with info on updating the registry for those that might not find it so obvious...and yes, the more you break, the more you learn :-) – Solomon Rutzky Mar 16 '18 at 19:44
1

Use Reset-DbaAdmin to gain your access back.

This function allows administrators to regain access to local or remote SQL Servers by either resetting the sa password, adding sysadmin role to existing login, or adding a new login (SQL or Windows) and granting it sysadmin privileges.

Note: This function is part of dbatools written in PowerShell and is open source project.

  • nice tool Kin ! I could solve my problem running my sql server with the traceflag 7806. But I will for sure study about this tool. – Racer SQL Mar 16 '18 at 19:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.