代码改变世界

MS SQL专用管理员连接DAC

2013-09-28 11:45  潇湘隐者  阅读(10259)  评论(5)  编辑  收藏  举报

在SQL SERVER 2005中，微软引入了一个叫做数据库专用管理员连接方式（DAC Dedicated Administrator Connection）的特性，使用这个新特性，数据库管理员可以在数据库引擎不能响应正常连接时，可以通过DAC连接到数据库，执行诊断函数或T-SQL语句，对数据库服务器进行问题诊断和故障排除（即使数据库实例以锁定或非正常状态下运行）。其实DAC还有一个非常有用的用途，用来研究数据库内部的表、目录视图等。

以前我也写过关于DAC的一篇文章SQL Server数据库专用管理员DAC连接方式，当时的实验环境是SQL Server 2005，现在来看，感觉有些侧重点和问题都忽略了。

 

启用远程DAC连接

默认情况下，在SQL SERVER 中远程专用管理员连接特性是禁用的。如果要启用其远程专用管理员连接特性，可以通过T-SQL或Microsoft  SQL Server Management Studio工具两种方式来实现。

T-SQL方式：

Code Snippet
  1. USE master;
  2.  
  3. GO
  4.  
  5. SP_CONFIGURE'remote admin connections';
  7. SP_CONFIGURE 'remote admin connections', 1; --0 表示仅允许本地连接使用 DAC，1表示允许远程连接使用 DAC
  8. GO
  9. RECONFIGURE WITH OVERRIDE;
  10. GO

管理工具：

SQL Server 2005

clip_image002

SQL Server 2008

用SQL Server 2008 管理工具启用专用管理员连接特性，右键单击SQL Server实例，然后从下拉的选项中选择方面(Facets)选项，进入查看方面窗口。

clip_image004

 

数据库专用管理员连接方式

DAC 连接到数据库服务器可以通过SQL Server Management Studio,也可以通过sqlcmd方式连接服务器，我们先看看sqlcmd方式：

sqlcmd方式：

SQLCMD –S [SQL Server Name] –U [User Name] –P [Password] –A

sqlcmd的语法提示如下，你可以用sqlcmd /?来查看

clip_image006

例如，我本机环境，机器名Kerry-PC，sa账号密码为123456，现在我要从本地用DAC连接到数据库

clip_image008

其实参数和参数值之间可以不用空格，服务器可用localhost或IP替换。

clip_image010

 

错误情况1：错误截图如下

clip_image012

C:\Users\Kerry>sqlcmd -S Kerry-Pc -U sa -P 123456 -A

HResult 0x1A，级别 16，状态 1

因为在登录之前握手期间出现错误，所以客户端无法建立连接。最常见原因包括客户端试图

连接到不受支持的 SQL Server 版本、服务器太忙无法接受新连接或服务器上的资源受到限

制(内存或允许的最大连接数)。

Sqlcmd: 错误: Microsoft SQL Server Native Client 10.0 : 客户端无法建立连接。

HResult 0x40，级别 16，状态 1

TCP 提供程序: 指定的网络名不再可用。

Sqlcmd: 错误: Microsoft SQL Server Native Client 10.0 : 由于预登录失败，客户端无

法建立连接。
View Code

 

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

管理工具连接

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

clip_image014

错误情况1：

clip_image016

 

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

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

clip_image018

 

标题: 连接到数据库引擎

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

无法连接到 ADMIN:Kerry-PC。

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

其他信息:

已成功与服务器建立连接，但是在登录过程中发生错误。 (provider: TCP 提供程序, error: 0 - 您的主机中的软件中止了一个已建立的连接。) (Microsoft SQL Server，错误: 10053)

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

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

按钮:

确定

------------------------------
错误详细信息

 

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
View Code

从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也是了解数据库内部实现机制的神器啊！

select * from sys.objects where name in ('sysdbreg','syspalvalues','syssingleobjrefs')