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.
An other option to display new query window is following the SSMS menu options: File > New > Database Engine Query
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.
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).
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.
TITLE: Connect to Server
Cannot connect to admin:[SQLServerName].
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".
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!
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.
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.
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.
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.