SQL Server专用管理员连接(Dedicated Admin Connection(DAC))-阿里云开发者社区

开发者社区> 数据库> 正文
登录阅读全文

SQL Server专用管理员连接(Dedicated Admin Connection(DAC))

简介:

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

 

打开SSMS,在“Connect to Server”窗口,选择“Cancel”,然后选择File菜单,下拉菜单选择“New”、“Database Engine Query”。

clip_image001

输入“admin:.”,点击“Connect”。

clip_image002

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

 

先来看看配置的默认值:

1
SELECT FROM sys.configurations where name 'remote admin connections'

或者

1
sp_configure 'remote admin connections'

clip_image003

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

 

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

输出:

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

 

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

我们通过另一台服务器上的SSMS建立DAC查询连接,选择File菜单,下拉菜单选择“New”、“Database Engine Query”。

clip_image004

输入域名或IP即可。

 

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

 

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

 

本地DAC连接:

clip_image005

远程DAC连接:

clip_image006

 

《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中连接普通查询连接,输入:

1
SELECT FROM sys.sysrmtlgns;

输出:

Msg 208, Level 16, State 1, Line 1

Invalid object name 'sys.sysrmtlgns'.

 

建立DAC连接,输入:

1
2
3
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;

clip_image007
















本文转自UltraSQL51CTO博客,原文链接: http://blog.51cto.com/ultrasql/1846482,如需转载请自行联系原作者






版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章
最新文章
相关文章