How to access a running instance of SQL Server Database Engine to troubleshoot MSSQL from a remote computer

  • Microsoft provides the Dedicated Administrator Connection or DAC to fix problems when the SQL Server becomes unresponsive [Microsoft's euphemism for hanged]. The Dedicated Administrator Connection allows the administrator or anybody belonging to the sysadmin group to log in even when nobody else can.
  • The Dedicated Administrator Connection allows the administrator or anybody belonging to the sysadmin group to close other people connections... and to resolve the issues including restarting the SQL Server.
  • By default Microsoft SQL Server only allow the Dedicated Administrator Connection or DAC from the SQL Server console.

Applies to:

  • Microsoft SQL Server 2005
  • Microsoft SQL Server 2008

Connecting with the Dedicated Administrator Connection from a remote computer

  1. SP_CONFIGURE 'remote admin connections', 1;
  2. go
  3. RECONFIGURE;
  4. go
  • By default the Dedicated Administrator Connection only listens to the loopback address (127.0.0.1) and on port 1434.
  • Line 1: sp_configure 'remote admin connections', 1; allows remote connections through the Dedicated Administrator Connection or DAC.
  • Using sp_configure 'remote admin connections', 0; restricts the the Dedicated Administrator Connection or DAC to a local connection from the console.
  • There is only 1 Dedicated Administrator Connection allowed.
  • If the Dedicated Administrator Connection is already used, it will fail.