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
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.
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:
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