Dedicated connection

In SQLServer 2005 one can connect to the SQL Server with a special connection called “Dedicated Administrator Connection” called as DAC.

To start a dedicated connection using sqlcmd.
Example:
E:\> sqlcmd -A
1> select getdate()
2> GO

———————–
2010-02-28 18:39:19.557

(1 rows affected)
1> exit

To start DAC using the mangement studio on the “File” menu, choose “New” and then “Database Enginer Query” and on the connection to database engine dialog in the server name prefix it with ADMIN:. Example ADMIN:localhost it will start the SQL Query window with DAC.

To find if your current connection is DAC you can find by running the following SQL, if it returns a row then your current connection is a dedicated connection.
select *
from sys.dm_exec_connections ec
join sys.endpoints e on (ec.endpoint_id=e.endpoint_id) where e.name='Dedicated Admin Connection' and session_id=@@spid

To find if there is a dedicated connection to the database, if it returns a row then there is a Admin connection on the server
select *
from sys.dm_exec_connections ec
join sys.endpoints e on (ec.endpoint_id=e.endpoint_id) where e.name='Dedicated Admin Connection'

By default DAC is available through local connection unless remote connection is enabled through “SQL Server Surface Area Configuration” then SQL Server configuration for Features -> DAC -> Click on check box “Enable remote DAC”

There can be only one DAC connection made to the server, if one tries to connect multiple you will get the following error.
E:\>sqlcmd -A
HResult 0x2746, Level 16, State 1
TCP Provider: An existing connection was forcibly closed by the remote host.

Sqlcmd: Error: Microsoft SQL Native Client : Communication link failure.

Additional information on DAC SQL Server 2005
http://msdn.microsoft.com/en-us/library/ms189595%28SQL.90%29.aspx
Additional information on DAC SQL Server 2008
http://msdn.microsoft.com/en-us/library/ms189595.aspx

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s