Enabling the Dedicated Administrator Connection (DAC) in SQL Server Express

,

A recent forum post asked about using the Dedicated Administrator Connection

(DAC) with SQL Server Express. The poster was unable to connect via the DAC,

even though he had specified what he had found to be the correct connection

settings. The reason for this is that SQL Server Express does not enable the DAC

by default. You can see this in the SQL Server Error Log such as in Figure 1.

Figure 1:

The reason DAC is not enabled in SQL Server Express (called "Express" from

here on) is the design mindset behind Express. Express is supposed to be as

lightwight as possible. One of the things that can be trimmed from the running

engine is the DAC. As indicated in

Books

Online (see the Restrictions section), DAC is disabled to

conserve resources. It can be enabled using a startup trace flag, 7806.

An easy way to turn on this trace flag is through SQL Server Configuration

Manager. If you click on SQL Server Services, right-click on

the SQL Server Express instance, and choose Properties, you'll

be able to edit the startup properties for the instance. By clicking on the

Advanced tab, you'll get access to the Startup

Parameters, as in Figure 2. You'll need to add the "-T7806" to the

Startup Parameters to specify use of that trace flag. And when there are

multiple parameters specified, you must separate each with a semi-colon (;). So

if you look closely at Figure 2, you'll see I've added ";-T7806" to the Startup

Parameters.

Figure 2:

Once you click OK, you should get a prompt indicating that

the change won't take effect until the next restart. That's truly the case.

Since this is a startup parameter, it is only read when the instance first

starts. Once you've made the change (and started or restarted the instance), you

can verify that the DAC is enabled by looking at the SQL Server Error Log. One

of the first things you should see is the fact that the trace flag is

recognized, as is shown in Figure 3.

Figure 3:

A little further in the log, you should see a message indicating that the DAC

was established and a port should be given, such as in Figure 4.

Figure 4:

The port is key. By default, the DAC listens on TCP port 1434. However, in

cases where the Express instance is a named instance (as is the case with mine)

or when TCP port 1434 is in use, the DAC could use a different port. Figure 4

shows my Express instance listening on TCP port 1762. This is important because

if its not listening on TCP port 1434, you're either going to need to know the

port ahead of time or SQL Browser must be enabled on the server where the SQL

Server Express instance is running. The reason for this is when you specify a

connection to the DAC, a connection to TCP port 1434 will be attempted first. If

that should fail, however, the client will attempt to use the SQL Server

Resolution Protocol (SSRP) to make a connection to the SQL Server Browser

service to determine the correct port to listen on (see the DAC Port

section in the

Books

Online topic). Assuming you have SQL Browser running, you can connect using

SQLCMD with the -A flag. This tells SQLCMD to attempt a DAC connection. If

you're using SQL Server Management Studio, you can specify ADMIN:

before the server\instance, as shown in Figure 5.

Figure 5:

Once you get connected, you can verify that you are connected to the DAC by

issuing a query against a

system base

table. The following query will do the trick:

SELECT 

  classidgranteetypestate

FROM sys.sysprivs;

 

If you try to issue this from a connection that is not the DAC, you'll get

the error message shown in Figure 6.

Figure 6:

Otherwise, you should get a result set consisting of those columns. If you

get that result set, you're connected via the DAC and free to use the

functionality it provides.

Rate

4.92 (13)

Share

Share

Rate

4.92 (13)