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
4. DBCC TRACESTATUS
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

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 e.name='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
3. RECONFIGURE;
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

 

Reference:

How to: Use the Dedicated Administrator Connection with SQL Server Management Studio http://msdn.microsoft.com/en-us/library/ms178068.aspx

Using a Dedicated Administrator Connection http://msdn.microsoft.com/en-us/library/ms189595.aspx