SQL Server Management Studio DAC connection error

Question

I'm trying to connect to SQL Server 2012 via DAC using Management Studio. I type admin: before my server name, yet I keep getting the same error:

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: TCP Provider, error: 0 - The remote computer refused the network connection.) (Microsoft SQL Server, Error: 1225)

What I've checked:

• I can login with no problems when using regular connection(non DAC)
• I can also login when forcing tcp: in connection string
• user is a sysadmin (sa)
• I am logging in from a local server (physical sql machine)
• SQL Server browser is enabled
• Remote connections are enabled and firewall was disabled
• TcpDynamicPorts in regedit was set to 59036

• I've executed this:

  exec sp_configure 'show advanced options', 1  
  RECONFIGURE WITH OVERRIDE  
  exec sp_configure 'remote admin connections', 1  
  RECONFIGURE WITH OVERRIDE  
  

So far, nothing has helped me.

Answer 1

TcpDynamicPorts in regedit was set to 59036

Your instance is named and is configured to use dynamic ports. The default instance is listening on 1433 for all the connections and on 1434 for DAC, your port for DAC is still unknown but you can find it in error log:

xp_readerrorlog 0,1,N'Dedicated admin connection'

Then you need to check that it's open in your firewall and you can connect specifying directly this port:

myPCname,port

Answer 2

Since you have already on local machine of SQL Server, there is no need of configuring exec sp_configure 'remote admin connections', 1. It's good practice in security terms to have DAC available only from Local Server.

However, try following to establish DAC connection:

1. Verify the port# that DAC listening locally (as mentioned by @sepupic). the message shuould look like

Dedicated admin connection support was established for listening lcoally on port

2. Probably, you would find dynamic port in Step 1, in that case use ServerName at SSMS as ADMIN:localhost\InstanceName
3. I would prefer connect DAC via SQLCMD, you may try as follows
   SQLCMD -S "Localhost,xxxx" -U Login -P yourpassword -A
   or
   SQLCMD -S "Localhost\InstanceName" -U Login -P yourpassword -A
4. If you are trying it on SQL Express, first set following trace on
   DBCC TRACEON (7806, -1)