SQL Server 专用管理员连接（DAC）

SQL Server专用管理员连接（Dedicated Admin Connection（DAC））

 

只有 SQL Server sysadmin 角色的成员可以使用 DAC 连接。默认情况下，只能从服务器上运行的客户端建立连接。

 

打开SSMS，选择文件菜单，下拉菜单选择 “新建”、“数据库引擎查询”。

 

 

 

输入“admin:主机名\实例名”，点击“连接”。

 

 

 

默认不允许使用网络连接DAC，需要通过sp_configure配置“remote admin connections”选项。

 

先来看看配置的默认值：

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

或者

sp_configure 'remote admin connections'

 

 

 

开启远程连接

管理界面: 实例->方面

 

 

 

T-SQL

--启用远程DAC连接
sp_configure 'remote admin connections', 1;
GO
RECONFIGURE;
GO

Value默认为0，指明仅允许本地连接使用 DAC。Maximum为1，表明只运行一个远程管理连接。

输出：

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

 

然后开启SQL Server Browser服务，防火墙允许TCP 1434端口的访问。

我们通过另一台服务器上的SSMS建立DAC查询连接，选择文件菜单，下拉菜单选择 “新建”、“数据库引擎查询”。

 

查看DAC连接

select e.name,
       s.session_id,
       s.program_name,
       e.protocol_desc,
       s.host_name
from sys.endpoints e
inner join sys.dm_exec_sessions s
        on e.endpoint_id = s.endpoint_id
where e.is_admin_endpoint = 1

 

DAC在SSMS连接时，只能通过建立查询窗口的方式打开。当SQL Server因系统资源不足，或其它异常导致无法建立数据库连接时， 可以使用系统预留的DAC连接到数据库，进行一些问题诊断和故障排除。DAC只能使用有限的资源。请勿使用DAC运行需要消耗大量资源的查询，否则可能发生严重的阻塞。

 

另一种打开方式是在命令行界面通过SqlCMD使用特殊的管理员开关（-A），提供对DAC的支持。

 

本地DAC连接：

远程DAC连接：

 

《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.

”

 

例如，在SSMS中连接普通查询连接，输入：

SELECT * FROM sys.sysrmtlgns;

 

输出：

Msg 208, Level 16, State 1, Line 1

Invalid object name 'sys.sysrmtlgns'.

 

建立DAC连接，输入：

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;

 

 

 

 

错误解惑：为了保证有可用的连接资源，每个 SQL Server 实例只允许使用一个 DAC。如果 DAC 连接已经激活，则通过 DAC 进行连接的任何新请求都将被拒绝，实际上上面的错误是我开了两个命令窗口，第一个使用DAC连接到数据库，第二个连接的啥时候就报如上错误。

管理工具连接

使用SSMS以DAC连接到服务器时需要在服务器前面加上ADMIN：，通常为ADMIN：主机名\实例名，如果实例使默认实例则 ADMIN:主机名

 

错误情况1：

 

错误解惑：出现这个错误，是因为数据库已经有一个DAC连接了，此时再通过DAC连接到数据库，就会报如上错误。

错误情况2：出现下面错误，可以在SQL Server Management Studio的菜单“文件 --> 新建 --> 数据库引擎查询”，再输入admin：主机名\实例名。这样就不会有下面错误了。

 

错误情况3：如果直接在SSMS 中选择文件,点击"连接对象资源管理器",会出现以下错误.

 

 

标题: 连接到服务器
------------------------------

无法连接到 admin:PC-20160822VIC\MYSQL。

------------------------------
其他信息:

已成功与服务器建立连接，但是在登录前的握手期间发生错误。 (provider: TCP Provider, error: 0 - 指定的网络名不再可用。) (Microsoft SQL Server，错误: 64)

有关帮助信息，请单击: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=64&LinkId=20476

------------------------------

指定的网络名不再可用。

------------------------------
按钮:

确定
------------------------------

错误解惑：同错误2.

 

DAC的特殊用途 

由于 DAC 仅用于在极少数情况下诊断服务器问题，因此对连接有一些限制，具体参考MSDN，这里不做过多的叙说。下面简单说说DAC的另外一个用途：

在默认连接下，有很多内部表和目录试图是无法获取，往往需要深入研究的时候，非常想知道它内部的实现，那么DAC绝对就是一件利器，我下面举一个例子，有兴趣的可以试试：

目录试图sys.databases，这个大家都很熟悉，那么这个目录视图的数据是从那些表获取的呢，那么我们找到sys.databases的SQL脚本，

SET QUOTED_IDENTIFIER ON 
SET ANSI_NULLS ON 
GO 
CREATE VIEW sys.databases AS 
    SELECT d.name, d.id AS database_id, 
        r.indepid AS source_database_id, 
        d.sid AS owner_sid, 
        d.crdate AS create_date, 
        d.cmptlevel AS compatibility_level, 
        convert(sysname, CollationPropertyFromID(p.cid, 'name')) AS collation_name, 
        p.user_access, ua.name AS user_access_desc, 
        sysconv(bit, d.status & 0x400) AS is_read_only,            -- DBR_RDONLY 
        sysconv(bit, d.status & 1) AS is_auto_close_on,            -- DBR_CLOSE_ON_EXIT 
        sysconv(bit, d.status & 0x400000) AS is_auto_shrink_on,        -- DBR_AUTOSHRINK 
        p.state, st.name AS state_desc, 
        sysconv(bit, d.status & 0x200000) AS is_in_standby,        -- DBR_STANDBY 
        sysconv(bit, d.status & 0x40000000) AS is_cleanly_shutdown,    -- DBR_CLEANLY_SHUTDOWN 
        sysconv(bit, d.status & 0x80000000) AS is_supplemental_logging_enabled,    -- DBR_SUPPLEMENT_LOG 
        p.snapshot_isolation_state, si.name AS snapshot_isolation_state_desc, 
        sysconv(bit, d.status & 0x800000) AS is_read_committed_snapshot_on,        -- DBR_READCOMMITTED_SNAPSHOT 
        p.recovery_model, ro.name AS recovery_model_desc, 
        p.page_verify_option, pv.name AS page_verify_option_desc, 
        sysconv(bit, d.status2 & 0x1000000) AS is_auto_create_stats_on,            -- DBR_AUTOCRTSTATS 
        sysconv(bit, d.status2 & 0x40000000) AS is_auto_update_stats_on,        -- DBR_AUTOUPDSTATS 
        sysconv(bit, d.status2 & 0x80000000) AS is_auto_update_stats_async_on,    -- DBR_AUTOUPDSTATSASYNC 
        sysconv(bit, d.status2 & 0x4000) AS is_ansi_null_default_on,            -- DBR_ANSINULLDFLT 
        sysconv(bit, d.status2 & 0x4000000) AS is_ansi_nulls_on,                -- DBR_ANSINULLS 
        sysconv(bit, d.status2 & 0x2000) AS is_ansi_padding_on,                    -- DBR_ANSIPADDING 
        sysconv(bit, d.status2 & 0x10000000) AS is_ansi_warnings_on,            -- DBR_ANSIWARNINGS 
        sysconv(bit, d.status2 & 0x1000) AS is_arithabort_on,                    -- DBR_ARITHABORT 
        sysconv(bit, d.status2 & 0x10000) AS is_concat_null_yields_null_on,        -- DBR_CATNULL 
        sysconv(bit, d.status2 & 0x800) AS is_numeric_roundabort_on,            -- DBR_NUMEABORT 
        sysconv(bit, d.status2 & 0x800000) AS is_quoted_identifier_on,            -- DBR_QUOTEDIDENT 
        sysconv(bit, d.status2 & 0x20000) AS is_recursive_triggers_on,            -- DBR_RECURTRIG 
        sysconv(bit, d.status2 & 0x2000000) AS is_cursor_close_on_commit_on,    -- DBR_CURSCLOSEONCOM 
        sysconv(bit, d.status2 & 0x100000) AS is_local_cursor_default,            -- DBR_DEFLOCALCURS 
        sysconv(bit, d.status2 & 0x20000000) AS is_fulltext_enabled,            -- DBR_FTENABLED 
        sysconv(bit, d.status2 & 0x200) AS is_trustworthy_on,                -- DBR_TRUSTWORTHY 
        sysconv(bit, d.status2 & 0x400) AS is_db_chaining_on,                -- DBR_DBCHAINING 
        sysconv(bit, d.status2 & 0x08000000) AS is_parameterization_forced,    -- DBR_UNIVERSALAUTOPARAM 
        sysconv(bit, d.status2 & 64) AS is_master_key_encrypted_by_server,    -- DBR_MASTKEY 
        sysconv(bit, d.category & 1) AS is_published, 
        sysconv(bit, d.category & 2) AS is_subscribed, 
        sysconv(bit, d.category & 4) AS is_merge_published, 
        sysconv(bit, d.category & 16) AS is_distributor, 
        sysconv(bit, d.category & 32) AS is_sync_with_backup, 
        d.svcbrkrguid AS service_broker_guid, 
        sysconv(bit, case when d.scope = 0 then 1 else 0 end) AS is_broker_enabled, 
        p.log_reuse_wait, lr.name AS log_reuse_wait_desc, 
        sysconv(bit, d.status2 & 4) AS is_date_correlation_on,         -- DBR_DATECORRELATIONOPT 
        sysconv(bit, d.category & 64) AS is_cdc_enabled, 
        sysconv(bit, d.status2 & 0x100) AS is_encrypted,                        -- DBR_ENCRYPTION 
        sysconv(bit, d.status2 & 0x8) AS is_honor_broker_priority_on                -- DBR_HONORBRKPRI 
    FROM master.sys.sysdbreg d OUTER APPLY OpenRowset(TABLE DBPROP, d.id) p 
    LEFT JOIN sys.syssingleobjrefs r ON r.depid = d.id AND r.class = 96 AND r.depsubid = 0    -- SRC_VIEWPOINTDB 
    LEFT JOIN sys.syspalvalues st ON st.class = 'DBST' AND st.value = p.state 
    LEFT JOIN sys.syspalvalues ua ON ua.class = 'DBUA' AND ua.value = p.user_access 
    LEFT JOIN sys.syspalvalues si ON si.class = 'DBSI' AND si.value = p.snapshot_isolation_state 
    LEFT JOIN sys.syspalvalues ro ON ro.class = 'DBRO' AND ro.value = p.recovery_model 
    LEFT JOIN sys.syspalvalues pv ON pv.class = 'DBPV' AND pv.value = p.page_verify_option 
    LEFT JOIN sys.syspalvalues lr ON lr.class = 'LRWT' AND lr.value = p.log_reuse_wait 
    WHERE d.id < 0x7fff 
        AND has_access('DB', d.id) = 1 
GO

 

从SQL脚本可以看出这个目录的数据来自sys.sysdbreg、 sys.syspalvalues、sys.syssingleobjrefs内部表，但是如果你去查看这些内部表的数据，你回发现这些对象都不存在

 

SELECT * FROM sys.sysdbreg

SELECT * FROM sys.syspalvalues

SELECT * FROM sys.syssingleobjrefs

消息 208，级别 16，状态 1，第 1 行
对象名 'sys.sysdbreg' 无效。

 

然而通过查询sys.objects你会发现，其实是有这样的系统表的，但是查询的时候就会报对象不存在错误，其实你只需要通过DAC连接到数据库，上面的SQL就能顺利执行，很多系统内部表都可以查看了。所以不得不说，DAC也是了解数据库内部实现机制的神器啊！

 

参考:

https://blog.csdn.net/weixin_34348111/article/details/85087978

https://www.cnblogs.com/kerrycode/p/3344085.html