Sql-server – need DAC if I am able to connect to the server via sqlcmd

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.

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!