Thursday, October 27, 2011

1 Dedicated administrator connections(DAC)


Enable for SQL Express

DAC connection is disable by default in sql express. It can be enable via trace flag 7806. There are 2 ways to enable the trace flag. DBCC TRACEON or via sql server start up parameters.

Method 1: DBCC

  1. -- Enable the Trace
  2. DBCC TRACEON (7806,-1)
  3. -- Check the trace flag status
  5. -- Turn off the trace flag
  6. DBCC TRACEOFF(7806)


However, I can not get that working and still see the error message.

Method 2: Trace flag on the start up parameters

Trace flag -T 7806


After bonce the sql server, you can see the DAC port being enable in the sql server error log.

SQL Server error log shows the DAC port


Lets try to connect to the DAC.


SQLCMD -S Servername -E –A


If you try to connect to the DAC from SSMS, you will get below error message

Dedicated administrator connections are not supported. (Microsoft.SqlServer.Management.SqlStudio.Explorer)


That’s because we are trying to connect through object explorer.

The supportive way is

SSMS>New>Database Engine Query > ADMIN:Servername (on the server name)



To check whether the connection is really using the DAC, you can join the sys.dm_exec_connections and sys.endpoints as below.

  1. select * from sys.dm_exec_connections ec
  2. join sys.endpoints e on (ec.endpoint_id=e.endpoint_id)
  3. where'Dedicated Admin Connection'
Only one DAC is allowed at any given moment.
Below shows the error message if 2nd DAC try to connect.
By default, DAC only allow to connect to the sql server locally. To enable the remote connection:
  1. sp_configure 'remote admin connections', 1
  2. GO
  4. GO

All my previous example shows connect sql server using name instance, this will require SQL browser running to allocate the port number for DAC

if you need to connect to DAC via hostname and port number,

1. Remote connection must be enable

2. Must manually find the port number form error log

  1. sqlcmd -S tcp:PO7,51515



How to: Use the Dedicated Administrator Connection with SQL Server Management Studio
Using a Dedicated Administrator Connection


  1. Breitling replica watches is one of the world's most respected Swiss horologists. replica breitling Founded in 1884 in Saint-Imier, the brand cemented its reputation as an aviation specialist, but is now an authority in diving and chronograph categories, too. Specialist watchmaking All COSC certified chronometers.



SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates