DAC IN SQL SEREVR

Published on August 27 2015 by LakshmiSaahul,Dhana Royal

SQL Server provides a dedicated administrator connection (DAC). The DAC lets an administrator access a running server to execute diagnostic functions or Transact-SQL statements, or to troubleshoot problems on the server, even when the server is locked or running in an abnormal state and not responding to a SQL Server Database Engine connection. By default, the DAC is only available from a client on the server. To enable client applications on remote computers to use the DAC, use the remote admin connections option of sp_configure.

By default, the DAC only listens on the loop-back IP address (127.0.0.1), port 1434. If TCP port 1434 is not available, a TCP port is dynamically assigned when the Database Engine starts up. When more than one instance of SQL Server is installed on a computer, check the error log for the TCP port number.

Indicates only local connections are allowed by using the DAC.

Indicates remote connections are allowed by using the DAC.

Example

The following example enables the DAC from a remote computer.

sp_configure 'remote admin connections', 1;
GO
RECONFIGURE;
GO

To use SQL Server Management Studio with the DAC, connect to an instance of the SQL Server Database Engine with Query Editor by typing ADMIN: before the server name. Object Explorer cannot connect using the DAC.

To connect to a server using the DAC
In SQL Server Management Studio, with no other DACs open, on the toolbar, click DATABASE ENGINE QUERY.
In the CONNECT TO DATABASE ENGINE dialog box, in the SERVER NAME box,
type ADMIN: followed by the name of the server instance. For example, to connect to a server instance named ACCT\PAYABLE, type ADMIN:ACCT\PAYABLE.
Complete the AUTHENTICATION section, providing credentials for a member of the sysadmin group, and then click CONNECT.
The connection is made.
If the DAC is already in use, the connection will fail with an error indicating it cannot connect.

Advertising