SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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

By Brian Kelley,

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:

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.

Total article views: 4481 | Views in the last 30 days: 2
Related Articles

SQL Server 2008 Express Edition

SQL Server 2008 Express Edition - cannot connect to instance on local machine


Having problem connecting to SQL Server Express 2008

Having problem connecting to SQL Server Express 2008


Connecting to different instance of SQL Server

Connecting to different instance of SQL Server


Can't connect to SQL Server 2005 instance

Can't connect to SQL Server 2005 instance


SQL Server Express Connection Issue

SQL Server Express

sql server express