Azure SQL Database – dedicated administrator connection (DAC)

azureazure-sql-databasedac

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 question. sp_configure is not available for Azure SQL Database.

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

Does that mean Remote admin connections are not allowed for Azure SQL Databases?

Best Answer

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. enter image description here

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:’.

enter image description here

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

enter image description here

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 if you try to change the context from Master to another database. Every database in an Azure SQL server has to be connected separately depending on which one you want to work with.

enter image description here

To connect to any database other than master type your database name in the connection string.

enter image description here

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.

enter image description here Caption: 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';
Related Question