With SQL Server 2005, SQL Server introduced the Dedicated Administrator Connection, which enables database administrators to connect if regular connections are not possible, for whatever reason. The admin connection can, by default, only be established from the machine itself; not from the network. This can be circumvented by changing the configuration option ‘remote admin connections’ to1 instead of 0:
sp_configure 'remote admin connections', 1
RECONFIGURE WITH OVERRIDE
To connect to a SQL Server instance using DAC, simply add admin: in front of the server and instance name, and supply credentials for a member of the sysadmin group:
You can connect to the DAC either using SQL Server Management Studio (SSMS) as shown above, or from the command prompt using SQLCMD. One thing to keep in mind when using SSMS is that you cannot use the Object Explorer to connect, but only the ‘New Query Window’ option, which might not be your default method of connecting. In case you are wondering how/where to find this option, it will be accessible either through the File menu, under File -> New -> Database Engine Query, or by using the “Database Engine Query” button:
Should you try using other connection methods (like using Object Explorer), you will receive the following error:
Dedicated administrator connections are not supported. (ObjectExplorer)
Several restrictions apply when using the DAC connection, the most important of which are:
Sqlcmd –A –d master
Microsoft recommends that when using the DAC, usage is restricted to troubleshooting and diagnostic commands only, in order to conserve resources.
In case you want to verify whether or not the current session is using the DAC connection, run the following query. It will return a single row as output if the session uses the DAC, in all other cases, it will not return anything:
SELECT
C.session_id
, C.connect_time
, C.client_net_address
, C.client_tcp_port
, C.local_tcp_port
, E.state_desc
, E.is_admin_endpoint
FROM
sys.dm_exec_connections C
JOIN sys.endpoints E on C.endpoint_id = E.endpoint_id
WHERE
E.is_admin_endpoint = 1
AND C.session_id = @@SPID
sp_configure 'remote admin connections', 1
RECONFIGURE WITH OVERRIDE
To connect to a SQL Server instance using DAC, simply add admin: in front of the server and instance name, and supply credentials for a member of the sysadmin group:
Should you try using other connection methods (like using Object Explorer), you will receive the following error:
Dedicated administrator connections are not supported. (ObjectExplorer)
Several restrictions apply when using the DAC connection, the most important of which are:
- Only one DAC connection can be made per SQL Server instance. If a DAC connection is already active, any new requests will be denied.
- The DAC will by default attempt to connect to the default database associated with the login credentials supplied. If that database happens to be unavailable, you will receive an error 4060. To directly connect to the master database instead of the default database, you can use sqlcmd using the following command:
Sqlcmd –A –d master
Microsoft recommends that when using the DAC, usage is restricted to troubleshooting and diagnostic commands only, in order to conserve resources.
In case you want to verify whether or not the current session is using the DAC connection, run the following query. It will return a single row as output if the session uses the DAC, in all other cases, it will not return anything:
SELECT
C.session_id
, C.connect_time
, C.client_net_address
, C.client_tcp_port
, C.local_tcp_port
, E.state_desc
, E.is_admin_endpoint
FROM
sys.dm_exec_connections C
JOIN sys.endpoints E on C.endpoint_id = E.endpoint_id
WHERE
E.is_admin_endpoint = 1
AND C.session_id = @@SPID