SQL Server Articles, SQL Server Tips, SQL Server Tutorials, SQL Server Tuning, SQL Server DBA, SQL Server Basics, Training, etc - MyTechMantra.com

How to Use Dedicated Administrator Connection in SQL Server

To use a Dedicated Administrator Connection (DAC) in SQL Server, open new query window and in Connect to Server dialog box one need to prefix the word “admin:” before the SERVERNAME as shown in the below snippet in case of a default instance of SQL Server.

DAC connection works with SQL Server and Windows Authentication Mode as long as you have SYSADMIN Privileges on the SQL Server Instance.

If you are not aware how to enable Dedicated Administrator Connection in SQL Server then read “Different Ways to Enable Dedicated Administrator Connection in SQL Server“.

Who is Connected to SQL Server

The word “admin:” is case insensitive hence “Admin:” or “ADMIN” all works perfectly. If you have a named instance then type “ADMIN:SERVERNAME\INSTANCENAME”.

Port Number Used by DAC

By default, SQL Server assign dedicated administrator connection port 1434 if the port is not available then SQL Server will dynamically allocate a Port Number to DAC. The port number used by DAC is written to SQL Server Error Log File. Below mentioned is the sample entry on SQL Server Error Logs.

Dedicated admin connection support was established for listening remotely on port 1434. 
Server is listening on ['any' <ipv4> 1434]. 
Server is listening on ['any' <ipv6> 1434].

How to Identify if connection made to the database engine is DAC

You would be able to see the connection name at the bottom of the query window as shown in the snippet below.

Connet to Server to Use Dedicated Administrator Connection in SQL Server

Error Message

In case if you unable to establish a successful DAC connection or a DAC connection already exist. Then, you would end up receiving below mentioned error message.

Dedicated administrator connections are not supported via SSMS as it establishes multiple connections by design. (Microsoft.SqlServer.Management.SqlStudio.Explorer)

Once you have established a successful DAC connection you can execute troubleshooting queries to identify the bottleneck and take action accordingly.

/* Returns one row per authenticated session on SQL Server */
SELECT * FROM sys.dm_exec_sessions 

/* Returns information about each request that is executing within SQL Server. */
SELECT * FROM sys.dm_exec_requests 

/* Returns information about currently active lock manager resources in SQL Server */
SELECT * FROM sys.dm_tran_locks

How to Use Dedicated Administrator Connection Using SQLCMD

To use DAC with SQLCMD you must be a member of SYSADMIN fixed server role.

Default Instance

SQLCMD –S [ServerName] –U [UserName] –P [Password] –A

Named Instance

SQLCMD –S [ServerName\InstanceName] –U [UserName] –P [Password] –A

Conclusion

This one very important feature which DBA’s must consider enabling it across all servers which they manage. This feature can help you troubleshoot issue rather than just restarting SQL Services or rebooting servers remotely.

Chetna Bhalla

LESS ME MORE WE

Chetna Bhalla, the founder of MyTechMantra.com, believes that by sharing knowledge and building communities, we can make this world a better place to live in. Chetna is a Graduate in Social Sciences and a Masters in Human Resources and International Business. She is an alumnus of Vignana Jyothi Institute of Management, Hyderabad, India. After graduation, Chetna founded this website, which has since then become quite a favorite in the tech world. Her vision is to make this website the favorite place for seeking information on Databases and other Information Technology areas. She believes that companies which can organize and deploy their data to frame strategies are going to have a competitive edge over others. Her interest areas include Microsoft SQL Server and overall Database Management. Apart from her work, Chetna enjoys spending time with her friends, painting, gardening, playing the violin, and spending time with her son.

Newsletter Signup! Join 15,000+ Professionals




Be Social! Like & Follow Us

Follow us

Don't be shy, get in touch. We love meeting interesting people and making new friends.

Advertisement