Dedicated Administrator Connection

                There will be times when a standard connection to SQL Server isn’t possible and as a DBA you’ll need to connect to troubleshoot issues with the instance.  You can achieve this through the Dedicated Administrator Connection (known as the DAC).  The DAC has its own set of resources so that even upon memory contention, you will be able to obtain a connection (provided you are utilizing a login in the sysadmin fixed server role).

Enabling Remote Admin Connections

                By default, you can only connect to the instance’s DAC on the server locally (i.e. Network connections aren’t allowed).  This ensures the surface area of the instance is as small as possible out of the box.  But to enable remote connections to the DAC, you simply need to set the instance configuration appropriately:

-- set 'remote admin connections' enabled

exec sp_configure 'remote admin connections', 1

go

reconfigure

go

DAC Port

                When SQL Server starts up, it will establish a listening port for the DAC.  For a Default instance SQL Server will attempt to establish TCP 1434.  But if it can’t allocate this port, or if it is a named instance then SQL Server will dynamically reserve a port.  During instance startup when this happens, SQL Server logs the established port in the Error Log.  In order to find out what port this is, you can do the following:

exec sp_readerrorlog 0, 1, 'dedicated admin connection'

go

The Text field will show something like this:

Dedicated admin connection support was established for listening remotely on port 1434.

Connecting to the DAC

                There are two ways to connect to the DAC: through SQLCMD and SSMS.

SQLCMD

                To connect to the DAC through SQLCMD, you will need to utilize the –A switch.  A sample connection would look as follows:

      sqlcmd –S YourServer\YourInstance –U sa –P My$ecur3P@$$w0rd –A

SQL Server Management Studio (SSMS)

                To connect through SSMS, there’s a bit of a “gotcha”.  You can’t connect the Object Explorer to the DAC due to the nature of SSMS using multiple connections normally.  To get around this, you will need to follow the below steps:

1.       Click on the “New Query” button to open a New Query Window: 

2.       Right-click in the query window and mouse-over “Connection”.  If this window already has a connection, you can select “Change Connection…” or if not simply select “Connect…”: 

3.       Now you will be prompted with the connect dialog, and to connect to the DAC through SSMS you just need to prefix the ServerName\InstanceName with the string “admin:”, as follows: 

As shown above, the DAC is a relatively easy portion of SQL Server to analyze and connect to, but equally an essential instrument in the troubleshooting DBA’s toolbox.  If there are any comments, questions, or issues please feel free to leave a comment or email me at sqlsalt@gmail.com.

Code

                As always, this code/information can be found on my SQLSalt GitHub repository:

AdminMisc_DAC_ConnectToDAC.txt

AdminMisc_DAC_EnableRemoteDAC.sql

AdminMisc_DAC_GetDACPort.sql