SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software SQL Server Tools Reviews and Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




download SQL Server 2016
download SQL Server 2014
download SQL Server 2012



SQL Server Dedicated Administrator Connection DAC Tool for Database Administrators

What is DAC (Dedicated Administrator Connection) in SQL Server?
SQL Server database administrators can connect to a SQL Server instance using DAC (Dedicated Administrator Connection) which is a specific connection type even when the standard SQL Server database connections fail to connect successfully. Database administrators can use Dedicated Administrator Connection which is frequently used when a problem occurs that prevents database connections. If you are a DBA working with remote connection, you have to enable "remote admin connections" SQL Server configuration option to use DAC SQL Server tool from a remote PC to connect to a SQL Server instance.

Since DAC, Dedicated Administrator Connection tool in SQL Server is designed for troubleshooting when no other database connection can be established, in your test environment if there is already an open connection to the SQL Server instance the DAC connection attempts will fail. So if you want to try and test Dedicated Administrator Connection, be sure that all connections are dropped for the target SQL Server database instance.

I had recently used DAC tool after I create a SQL Server logon trigger which was preventing all connections by dropping them because of coding error.

DAC in SSMS (SQL Server Management Studio)

If you are using SQL Server Management Studio (SSMS) to connect to a SQL Server database instance using DAC, you have to use new database engine query window.

Database administrators can activate new query window using the New Query button at on the SQL Server Management Studio (SSMS) Toolbar.

New Query button for DAC connection in SQL Server SSMS

An other option to display new query window is following the SSMS menu options: File > New > Database Engine Query

New Database Engine Query window for SQL Server DAC connection

When connection properties screen is displayed where database administrators will provide the target SQL Server instance and a valid administrative logon user, add "ADMIN:" prior to SQL Server instance name in "server name" input textbox.
You can also enter "admin:localhost" if you are using SSMS which is installed on the server machine where the target default instance is installed.

DAC Dedicated Administrator Connection in SQL Server Management Studio SSMS

If the Dedicated Administrator Connection (DAC) is successfully established, you will see the admin:servername at the status bar of the Database Engine Query window (Query Editor window).

SQL Server DAC connection established


Troubleshooting SQL Server DAC (Dedicated Administrator Connection) in SSMS

SQL Server Dedicated Administrator Connection (DAC) using SQL Server Management Studio (SSMS) will fail on some certain circumstances.

First case happens frequently ending with error message "Dedicated administrator connections are not supported via SSMS as it establishes multiple connections by design".
Below screenshot is taken from SQL Server 2014 SSMS.

Dedicated administrator connections are not supported via SSMS

TITLE: Connect to Server
Cannot connect to admin:[SQLServerName].
ADDITIONAL INFORMATION:
Dedicated administrator connections are not supported via SSMS as it establishes multiple connections by design. (Microsoft.SqlServer.Management.SqlStudio.Explorer)

The same error is less descriptive when connected with previous versions of SQL Server Management Studio IDE. Same cause but the error description is missing to explain the reason by omitting the text "it (SSMS) establishes multiple connections by design".

Dedicated administrator connections are not supported

TITLE: Connect to Server
Cannot connect to ADMIN:[SQLServerName].
Dedicated administrator connections are not supported. (Microsoft.SqlServer.Management.SqlStudio.Explorer)

The troubleshooting in this case is easy :)
The cause of this problem is that the administrator is using SQL Server Management Studio (SSMS) Object Explorer window Connect button for connection properties screen. Actually when Object Explorer is used an addition connection is created to display server objects on the Object Explorer window.
Using Object Explorer window for DAC (Dedicated Administrator Connection) is simply violating the rule of the one and the only connection rule :)

Use New Query marked with green instead of Object Explorer marked with red!

ssms

So disconnect SQL Server on the Object Explorer window. And then connect using New Query window or using New Database Engine Query window as illustrated at the first part of this SQL Server tool tutorial for DAC.

If remote admin connections Server Configuration Option is disabled on target SQL Server database instance, administrators who want to connect to database instance using DAC (Dedicated Administrator Connection) fill fail to connect from a client other than the server itself. So still database administrators can connect to SQL Server instance using dedicated administrator connection aka DAC on the same server where database instance is installed.

You can check if remote admin connections server option is enabled or disabled by executing the sp_Configure system stored procedure. Administrators or SQL developers can see the status of the remote admin connections SQL Server option by checking the run_value column. If it is 1 then DAC (Dedicated Administrator Connection) is enabled for a client connection to the related database server. If it is 0, then to connect using DAC administrators should be logged on to the server where SQL Server database instance is installed.

ssms

To enable remote admin connections, DBA's can execute below script. This will set run_value of remote admin connections server option from 0 to 1 after "reconfigure" is executed successfully.

exec sp_configure 'remote admin connections',1
-- Configuration option 'remote admin connections' changed from 0 to 1. Run the RECONFIGURE statement to install.
reconfigure

For more on remote admin connections, please refer to SQL Server Books OnLine article.

If remote admin connections SQL Server configuration options is disabled or if you have not enable 'remote admin connections' server option, the DAC connection attempt will cause below connection errors.

network-related or instance-specific error

TITLE: Connect to Server
Cannot connect to ADMIN:SQLServerName.
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 43 - An error occurred while obtaining the dedicated administrator connection (DAC) port. Make sure that SQL Browser is running, or check the error log for the port number) (Microsoft SQL Server)

You might also provide a wrong name for the SQL Server instance like using "admin:local" instead of ""admin:localhost", etc.






Related SQL Resources

SQL Server Articles

SQL Server 2016

SQL Server 2014

SQL Server 2012

SQL Server Tools

MS SQL Server Forums









Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems