Microsoft SQL Server has given an option to connect to SQL Server when normal connections can’t be made because of any reasons. This feature is called as Dedicate Administrator Connection (DAC). You can read more about DAC over here. Diagnostic Connection for Database Administrators
During one of my online classes, I showed a demo to show how DAC can be useful in doing some basic diagnostics to find the cause of any issues. One of my students tried it after my class and he was not able to connect. Here is the error message which he shared with me.
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 43 – 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) (Microsoft SQL Server, Error: -1)
We verified that SQL Browser was running, and it was a local connection. I asked to check my favorite file to look for any errors related to DAC. Interestingly, we found below
2017-07-05 18:24:10.84 Server Error: 17182, Severity: 16, State: 1.
2017-07-05 18:24:10.84 Server TDSSNIClient initialization failed with error 0x2, status code 0x22. Reason: Unable to retrieve dynamic TCP/IP ports registry settings for Dedicated Administrator Connection. The system cannot find the file specified.
2017-07-05 18:24:10.84 Server, Dedicated admin connection support was not started because of error 0x2, status code: 0x1. This error typically indicates a socket-based error, such as a port already in use.
There are two messages here. First one says: “The system cannot find the file specified” and based on complete message, it looks like SQL is looking for a certain registry key. A second message is a side effect of the first message and not because of the port used by any other process.
I compared it with my system and found that under below the key:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL13.SQL2016\MSSQLServer\SuperSocketNetLib\AdminConnection\Tcp
We were not seeing TcpDynamicPorts key, which is precisely reported by SQL ErrorLog. So, we created key as shown below.
On my server MSSSQL13 is for SQL Server 2016 and SQL2016 is the instance name. If you have default instance of SQL Server 2016 then it would be MSSQL13.MSSQLSERVER. Based on your SQL Server version and instance name you need to create appropriate key.
Have you solved any issue using DAC? Please share via comments.
Reference: Pinal Dave (https://blog.sqlauthority.com)
Is it same if we follow these steps?
Sql Server Configuration Manager (SQLSERVERMANAGER10.msc)
->Sql Server Network Configuration
->Protocols for MSSQLSERVER
->TCP Dynamic Ports
No. This is different. This key is for DAC. Notice “AdminConnection” in the registry path.