SQL Server – No DAC Connection on Default Port: Ensure DAC is Enabled

connectionsdacsql serversql-server-2016ssms

I could not establish a local DAC connection using SSMS while I was logged into the Sql-Server's Operating System's Remote Desktop. First reason was SQL-Browser Service turned off.
Lateron, second reason I was pointed to was this "DAC is not activated" error:

"No DAC connection could be established on the default port. Make sure
DAC is enabled".

I looked out for a solution. The suggested solution I found was to enable the "RemoteDacEnabled" Facet property or run 

sp_configure 'remote admin connections', 1

This makes me wonder, why I should enable a REMOTEDacEnabled property in case I wanted to connect from LOCAL SYSTEM? Do I get REMOTE wrong here?

MSDN explains:

"The connection is only allowed from a client running on the server. No network connections are permitted."

If I HAVE TO logon from local server and NOT over the network, what sense does a REMOTE DAC setting ever make?

EDIT/part of the answer:
The main reason for my question was, that I did NOT understand why we have to allow /enable the REMOTEDAC settings just in case we want to establish a LOCAL DAC connection from the server itself (that was what I tried with no luck in the first place).

NOW after giving it more tries I figured out that using…

ServerName.SubDomain.Domain.de

caused this error connecting to DAC while…

ServerName

DOES WORK (without activating the RemoteDac setting).

(Weird about this is that I can connect fine using a "normal" connection with both options)

Root cause was that the FQDN was interpreted to be a remote server and this caused the error above.

However, after I have enabled REMOTEDAC, both options from above work for a DAC, no matter if I try it local on the server or from remote clients.

Best Answer

From Using a Dedicated Administrator Connection:

By default, the connection is only allowed from a client running on the server. Network connections are not permitted unless they are configured by using the sp_configure stored procedure with the remote admin connections option.

From that latter page:

SQL Server provides a dedicated administrator connection (DAC). The DAC lets an administrator access a running server to execute diagnostic functions or Transact-SQL statements, or to troubleshoot problems on the server, even when the server is locked or running in an abnormal state and not responding to a SQL Server Database Engine connection. By default, the DAC is only available from a client on the server. To enable client applications on remote computers to use the DAC, use the remote admin connections option of sp_configure.

I think the page you read is just a little light on the details, that's all.

As for why a local DAC connection might not work, it's possible that it's because you're running SQL Server Express, which requires TF 7806 to enable. Another possibility is the network protocols that are enabled.

Related Question