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
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
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
table. The following query will do the trick:
SELECT
class, id, grantee, type, state
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.
