Azure SQL Database – dedicated administrator connection (DAC)

March 10, 2021

As per Microsoft documentation, Diagnostic Connection for Database Administrators is available for the Azure SQL database. If you are not familiar with DAC, read this official documentation from Microsoft.

By default, the connection is only allowed from a client running on the server. Network connections are not permitted unless they are configured by using the sp_configure stored procedure with the remote admin connections option.
Ref: Diagnostic Connection for Database Administrators

There is a tip in the same document.

If you check the setting in any of your Azure SQL databases, you will see that the ‘value_in_use’ column value is zero for ‘remote admin connections’. Meaning ‘remote admin connections’ are not allowed from remote clients. There is no way to change that at the time of writing this blog post. sp_configure is not available for Azure SQL Database.

SELECT * FROM sys.configurations
WHERE NAME = 'remote admin connections'
ORDER BY NAME;

In my test, I can still connect to the Azure SQL database with DAC using SSMS or SQLCMD. I did make the Microsoft Azure SQL team aware and suggested changing the value from zero to one.

In SSMS, follow the same process that you use to connect to on-premises SQL Server. Do not use the ‘Connect Object Explorer’ connections. Open a ‘New Query’ window and then connect. Prefix server name with ‘ADMIN:’.

During my testing, I get an error message but the connection was successful (SSMS 18.8).

If you do not mention the database name, you will only connect to the master database. You cannot switch context to any user databases. You will get an error. Every database in an Azure SQL server has to be connected separately depending on which one you want to work with.

Trying to switch context after connecting to Master database

Mention Database Name for User Databases

For SQLCMD, you can use this command. You must use -d switch for the database name. If you are using Azure Active Directory for authentication use -G switch.

sqlcmd -S servername.database.windows.net -U userName -P StrongPassword -d master -G -A

For On-Premises SQL Server, you will use sys.endpoints system catalog view to see details about a DAC. This system catalog view does not exist in Azure SQL Database.

Output of sys.endpoints from SQL 2019 CU 9

For Azure SQL Database, use the below query to determine who is using DAC and all other details about the DAC session. Dimitri Furman from Microsoft provided this query.

SELECT s.*
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_resource_governor_workload_groups AS wg
ON s.group_id = wg.group_id
WHERE wg.name = 'DACGroup';