Shulou(Shulou.com)06/01 Report--

SQL Server dedicated administrator connection (Dedicated Admin Connection (DAC))

Only members of the SQL Server sysadmin role can connect using DAC. By default, connections can only be established from clients running on the server.

Open SSMS, in the "Connect to Server" window, select "Cancel", then select the File menu, and the drop-down menu select "New" and "Database Engine Query".

Enter "admin:." and click "Connect".

The network connection DAC is not allowed by default, and the "remote admin connections" option needs to be configured through sp_configure.

Let's take a look at the default values of the configuration:

SELECT * FROM sys.configurations where name = 'remote admin connections'

Or

Sp_configure 'remote admin connections'

Value defaults to 0, indicating that only local connections are allowed to use DAC. A Maximum of 1 indicates that only one remote management connection is running.

-- enable remote DAC connection to sp_configure 'remote admin connections', 1 / GORECONFIGURETOGO

Output:

Configuration option 'remote admin connections' changed from 0 to 1. Run the RECONFIGURE statement to install.

Then turn on the SQL Server Browser service, and the firewall allows access to TCP port 1434.

We set up a DAC query connection through SSMS on another server, select the File menu, and select "New" and "Database Engine Query" from the drop-down menu.

Just enter the domain name or IP.

When DAC connects to SSMS, it can only be opened by establishing a query window. When SQL Server cannot establish a database connection due to lack of system resources or other anomalies, you can use the DAC reserved by the system to connect to the database to diagnose and troubleshoot some problems. DAC can only use limited resources. Do not use DAC to run queries that consume a lot of resources, or serious blocking may occur.

Another way to open it is to use the special administrator switch (- A) through SqlCMD in the command line interface to provide support for DAC.

Local DAC connection:

Remote DAC connection:

There is a passage in "SQL Server 2012 Internals":

"

SQL Server maintains a set of tables that store information about all objects, data types, constraints,confguration options, and resources available to SQL Server. In SQL Server 2012, these tables are called the system base tables. Some of the system base tables exist only in the master database and contain system-wide information; others exist in every database (including master) and contain information about the objects and resources belonging to that particular database. Beginning with SQL Server 2005, the system base tables aren't always visible by default, in master or any other database. You won't see them when you expand the tables node in the Object Explorer in SQL Server Management Studio, and unless you are a system administrator, you won't see them when you execute the sp_help system procedure. If you log on as a system administrator and select from the catalog view called sys.objects (discussed shortly), you can see the names of all the system tables. For example, the following query returns 74 rows of output on my SQL Server 2012 instance:

USE master

SELECT name FROM sys.objects

WHERE type_desc = 'SYSTEM_TABLE'

But even as a system administrator, if you try to select data from one of the tables returned by the preceding query, you get a 208 error, indicating that the object name is invalid. The only way to see the data in the system base tables is to make a connection using the dedicated administrator connection (DAC), which Chapter 2, "The SQLOS," explains in the section titled "The scheduler." Keep in mind that the system base tables are used for internal purposes only within the Database Engine and aren't intended for general use. They are subject to change, and compatibility isn't guaranteed. In SQL Server 2012, three types of system metadata objects are intended for general use: Compatibility Views, Catalog Views, and Dynamic Management Objects.

"

For example, to connect a normal query connection in SSMS, enter:

SELECT * FROM sys.sysrmtlgns

Output:

Msg 208, Level 16, State 1, Line 1

Invalid object name 'sys.sysrmtlgns'.

To establish a DAC connection, enter:

SELECT net_transport,auth_scheme,client_net_address FROM sys.dm_exec_connections WHERE session_id=@@spid;SELECT * FROM sys.sysrmtlgns;SELECT * FROM sys.syslnklgns

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.