For the first time, DAC appeared in the SQL Server 2005 version. It enables one user from the sysadmins group (that means, the system administrator) to connect to the running instance of SQL Server Database Engine, especially for the purpose of solving occurred problems on the server (note: DAC connection will disconnect all other users from this server.) It will be used, e.g. in cases when the server does not respond to requirements coming from the client’s side but also in decrypting the content of database objects (i.e. of stored procedures, user-defined functions, views and triggers) which were encrypted using the key word WITH ENCRYPTION (note: the possibilities of content decryption by means of implicit encryption of database objects will be dealt in some of other contributions on this topic.)
In the Express versions, this feature is implicitly deactivated. The procedure to allow DAC for the Express versions is as follows:
- starting up of “Control Panel\System and Maintenance\Administrative Tools\Services”
- stopping of “SQL Server Express” service
- opening of the dialog window “Properties” on the suspended service
- adding “-T7806” into the text box “Start parameters”
- starting of the “SQL Server Express” service
DAC is available either through the SQLCMD utility (e.g. C:>SQLCMD -E -S.\SQLEXPRESS -A) or by means of SSMS (i.e. SQL Server Management Studio). A requirement is a need to use Query Editor with the ADMIN parameter for the server name (e.g. ADMIN:MP\SQLEXPRESS2008), while it is not possible to use the Object Explorer! The connection may be established direct from the given server only. No network connection to such a server is allowed.
Figure 1: Applet Services (Windows Vista)
Figure 2: SQL Server Properties (SQLS 2008)
Figure 3: Management Studio – failure of an attempt to DAC before allowing it (SQLS 2008)
Figure 4: Management Studio – failure of an attempt to DAC before allowing it (SQLS 2005)
Figure 5: Management Studio – failure of an attempt to DAC after allowing it through Object Explorer (SQLS 2008)
Figure 6: SQLCMD – DAC performance (SQLS 2008)
Figure 7: SQLCMD – execution of a query in the database (SQLS 2008)
Figure 8: SQLCMD – failure of an attempt to DAC after restarting the SQL Server service (SQLS 2008)
Figure 9: Management Studio – DAC connection configuration through Query Editor (SQLS 2008)
Figure 10: Management Studio – execution of a query in the database through Query Editor (SQLS 2008)
Figure 11: Management Studio – a case when DAC is already used by other person (SQLS 2008)
Additional reference:
- Dedicated Administrator Connection – SQL School Video
by Andy Warren - Enabling the Dedicated Administrator Connection (DAC) in SQL Server Express
by Brian Kelley
Placko's database KB 