Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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:

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.

Total article views: 4310 | Views in the last 30 days: 7
 
Related Articles
FORUM

SQL Server 2008 Express Edition

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

FORUM

Connecting to different instance of SQL Server

Connecting to different instance of SQL Server

FORUM

Can't connect to SQL Server 2005 instance

Can't connect to SQL Server 2005 instance

FORUM

Having problem connecting to SQL Server Express 2008

Having problem connecting to SQL Server Express 2008

FORUM

SQL Server Express Connection Issue

SQL Server Express

Tags
dac    
sql server express    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones