Skip to content

Posts from the ‘DAC’ Category

Dedicated Administrator Connection (DAC)

Some times we are not able to connect to SQL Server database instance because of huge blocking, etc and the only option available for us, as a DBA is to try connecting to the server using dedicated admin connection (DAC). DAC allows database administrators to connect to the instance when database engine is running abnormally and is not responding to any connection requests. Using this connection you can run SQL queries and other diagnostic commands to find and resolve the issue.

From where can I connect using DAC?

By default you can connect using DAC only from the system where SQL Server is installed. To check if DAC is allowed from outside the box, run the below query: –

sp_configure ‘remote admin connections’

If run_value is ‘0’ that mean it’s not allowed to connect using DAC from outside the box. We can always change this to accept DAC connections from outside the box by running: –

sp_configure ‘remote admin connections’,1

For clustered environments, it is always recommended to enable remote admin connections. Changes takes effect immediately, no SQL Server services restart is required.

How to make dedicated admin connection?

We can connect as using DAC only from sqlcmd or query analyzer. If we are trying to connect using sqlcmd, then we can connect using –A parameter at the end like: –

sqlcmd –S Servername –U sa -P password –A

And if we are connecting from query analyzer, then we need to type ‘admin:’ before the instance name like: –

admin:servername\instancename

Which port number does DAC use?

By default dedicated admin connection always listen on loop-back IP 127.0.0.1 at port 1434 for making connection.

If multiple database instances are installed on the server and port number 1434 is used by some other instance, then SQL Server will dynamically assign port for DAC during instance startup.  The port number assigned to DAC can be found by looking at the SQL Server log entry. Entry will be like: –

Date  8/11/2011 8:08:08 PM
Log  SQL Server (Current – 8/24/2011 10:47:00 AM)

Source  Server

Message
Dedicated admin connection support was established for listening locally on port 3357.


Date  8/11/2011 8:08:08 PM
Log  SQL Server (Current – 8/24/2011 10:47:00 AM)

Source  Server

Message
Server is listening on [ 127.0.0.1 <ipv4> 3357].