How to enable Dedicated Admin Connection (DAC) in SQL server

There are possibilities that SQL server becomes unresponsive, may be because of heavy work load and poor writing of queries.

It’s a good option to configure the DAC to check, what is going on the SQL server.

Requirements for configuring DAC.

1. SQL Server above 2005.

2. Port number 1434 (default port number) needs to enabled.

3. TCP/IP needs to enabled from configuration manager.

4. SQL management tools (SSMS) or SQL CMD need to be installed on the remote computer, in case the windows itself not responding.

Enabling DAC using TSQL Command:

Use master

GO

sp_configure ‘remote admin connections’, 1

GO

RECONFIGURE WITH OVERRIDE

GO

Enabling DAC from GUI:

Step 1: Right click on the Instance name and select the option Facets.

Step 2: In Facet drop down select the value Surface Area Configuration.

Step 3: In facet properties, select the RemoteDacEnabled to true.

Connecting to SQL Server using DAC

Once DAC is enabled, we can connect to SQL server using SSMS or SQL CMD.

By using SSMS (GUI based)

Unlike the connect option in object explorer, here we have to use the database Engine query option as shown below.

Once the Connect to Engine window flashes, input the server name but before add admin: like shown below.

Once connected, please confirm whether we connected using DAC by checking the status bar below.

By using SQLCMD

SQL CMD is a command based tool for accessing the sql server. Unlike the SSMS it is consume less space.

At first you have to download SQLCMD from official site and install it on your machine from where you want to access the SQL instance.

Open Command prompt and type below mentioned lines to login as DAC account

If its connected using DAC account, waits for user input.

Issues we have faced while configuring the DAC.

Once we have configured the DAC, when we tried to access the server using the SSMS, we got the below mentioned error.

Then we tried to ping the server, it is pinging

Then by using the telnet, check server ports are opened but we got the below mentioned error.

We identified that the error was with the port number 1434, was not enabled. Once we enabled the particular port number we are able to access the SQL using DAC

Further reading

If you found this article helpful, you might want to check out these related resources,

• Dynamic Data Masking in SQL Server
• Top 7 factors to look for Managed DBA service provider