SQL Server Remote Dedicated Admin Connection (DAC)

Remote DAC Disabled

What is the DAC Connection SQL Server?

It’s a diagnostic connection for a database administrator to access SQL. The DAC can make remote troubleshooting easier when SQL Server is unresponsive. When SQL is having issues, DAC can be used to login and fix problems and avoid downtime.

By the way, this check is a part of our SQL Server Health Check service.

How to identify the issue?

DAC is only allowed from a client running on the server by default. It’s a best practice to enable network connections to use it from a remote machine. You can check if the feature is enabled following the steps below:

  • Using SSMS, connect to SQL Server Instance and then right-click the Server and select FACETS  as shown below.
Figure 1 – Facets (SSMS).
  • In View Facets window you need to choose the Facet as “Surface Area Configuration” and then check for RemoteDacEnabled.
Figure 2 – SQL Server RemoteDacEnabled Property.

How do I enable DAC? How to fix it?

You can enable it using the interface above, just changing the property to ENABLED. Another way is to implement the change running the following script:

sp_configure ‘show advanced options’, 1 go reconfigure with override go sp_configure ‘remote admin connections’, 1 go reconfigure with overrideNotes:

  • Only members of the SQL Server sysadmin role can connect using the DAC, and only one connection can be made at a time.
  • Make sure to get firewall ports opened, this will be probably port 1434, but that will vary depending on your installation.

More information
Microsoft Diagnostic Connection for Database Administrators

Mark Varnas

Mark Varnas

Hey I'm Mark, one of the guys behind Red9. I make a living performance tuning SQL Servers and making them more stable. I channel my SQL into our SQL Managed Services, SQL Consulting and our internal database products.

Leave a Reply

Your email address will not be published. Required fields are marked *