How to Use Dedicated Administrator Connection in SQL Server
Sept 18, 2013
To use a Dedicated Administrator Connection 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".
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.
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
In order to use DAC with SQLCMD you must be a member of SYSADMIN fixed server role.
SQLCMD –S [ServerName] –U [UserName] –P [Password] –A
SQLCMD –S [ServerName\InstanceName] –U [UserName] –P [Password] –A
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.
Geeks who read this article also read…
- Performance Dashboard Reports in SQL Server 2012
- How to Backup an Analysis Services Database Using SQL Server Management Studio
- How to Restore an Analysis Services Database Using SQL Server Management Studio
- Automate Backup of Analysis Services Database Using SQL Server Agent Job
- How to Attach Analysis Services Database in SQL Server
- How to Detach Analysis Services Database in SQL Server
- How to Backup Database in SQL Server
- How to Restore Database in SQL Server
- How to Attach Database Without a Transaction Log File in SQL Server
- New Features in SQL Server 2012 Reporting Services for Developers
- New Features in SQL Server 2012 for Database Administrators
- New Features in SQL Server 2012 for Database Developers
- Read More SQL Server Articles…