SQL Server中的代理帐户

dn001 2009-05-26 11:14:15

　　如果连接到SQL server 的应用程序只需要访问SQL server 实例内部的对象和资源，那这是非常理想的。但是，通常一个应用程序需要访问外部系统的资源，例如文件、网络、环境变量或注册表。举例来说，应用程序可能需要运行xp_cmdshell 扩展存储过程来调用一个Windows shell命令，并执行一个shell命令来获取一个目录下的文件列表。或者，这个应用程序安排一个SQL server Agent工作来执行维护任务。这个工作有一个Active Scripting工作步骤或一个Web Service任务来调用一个Web Service，以便验证地理位置和邮编信息。

　　默认情况下，在SQL server 2000中，只有sysadmin固定服务器角色的成员才可以执行xp_cmdshell扩展存储过程和Active Scripting工作步骤。当xp_cmdshell扩展存储过程被sysadmin固定服务器角色的一个成员执行时，shell命令的Windows进程在SQL Server服务帐户的安全上下文中运行。当sysadmin角色的一个成员的一个工作运行时，它的Active Scripting工作步骤运行在SQL server Agent服务帐户的安全之下。但是，在大多数公司里，数据库管理员角色和应用程序开发人员角色通常是分开的。基于安全考虑，应用程序开发人员不被允许具有sysadmin权限。为了使应用程序开发人员可以访问外部资源而不必给他们过多的权限，SQL Server提供了代理帐户的解决方案。

　　扩展存储过程xp_sqlagent_proxy_account设置SQL server Agent和xp_cmdshell在执行工作或命令时对于不是sysadmin固定服务器角色成员的用户所使用的代理帐户信息。例如，下面的命令设置代理帐户为一个域帐户PowerDomainPowerUser，然后使得非sysadmin登录进来在域帐户的安全上下文中执行Active Scripting工作步骤和xp_cmdshell。

　　;;;USE master
　　GO

　　-- Create a test login called testuser

　　EXEC sp_addlogin 'testuser', 'testuser'

　　-- Add a windows domain account PoweDomainPowerUser as the proxy account.

　　EXECUTE xp_sqlagent_proxy_account N'SET' , N'PowerDomain' , N'PowerUser' , N'P@ssw0rd'

　　-- Enable non-sysadmin logins to run active Scripting job steps and execute xp_cmdshell.

　　EXECUTE msdb..sp_set_sqlagent_properties @sysadmin_only = 0

　　-- Grant database access to the sql server login account that you want to provide access.

　　EXEC sp_grantdbaccess 'testuser'

　　-- Grant execute permission on xp_cmdshell to the sql server login account.

　　GRANT exec ON xp_cmdshell TO [testuser]

　　GO

　　请注意，在SQL server 2000中只能指定一个代理帐户。这个帐户是用来执行xp_cmdshell和Active Scripting工作步骤的。

　　在SQL server 2005和2008中，为了允许一个非sysadmin登录进来从而执行xp_cmdshell，你需要创建一个特定的系统凭证##xp_cmdshell_proxy_account##，这是通过运行外部存储过程sp_xp_cmdshell_proxy_account并指定一个Windows帐户来实现的。这个帐户将被非sysadmin角色的成员用户用来运行xp_cmdshell。

USE master
　　GO

　　-- Create a test login called testuser

　　CREATE LOGIN testuser WITH PASSWORD='P3h4jek@x'

　　-- Create a proxy credential for xp_cmdshell.

　　EXEC sp_xp_cmdshell_proxy_account 'PowerDomainPowerUser', 'P@ssw0rd'

　　-- Grant database access to the sql server login account that you want to provide access.

　　EXEC sp_grantdbaccess 'testuser'

　　-- Grant execute permission on xp_cmdshell to the sql server login account.

　　GRANT exec ON sys.xp_cmdshell TO [testuser]

　　为了确认##xp_cmdshell_proxy_account##凭证确实被创建了，你可以选择sys.credentials视图。

　　你还可以对SQL server 2005 and 2008中的SQL server Agent工作的代理进行更多和更好的控制。你可以指定不止一个的代理帐户。你还可以指定你想应用一个代理帐户到哪个子系统(工作步骤类型)上去。

　　在创建一个代理帐户之前，你需要定义一个Windows凭证。在凭证创建之后，你可以创建一个代理帐户并分配这个凭证给它。然后你授权这个代理访问给一个或多个子系统。如果你使用sp_grant_proxy_to_subsystem存储过程来授予访问权限给多个子系统，那么你将需要多次执行这个存储过程。在这之后，你可以授权这个权限给多个SQL Server登录、msdb角色、和/或服务器角色来使用这个代理帐户。

　　例如，我们想为执行SSIS包的工作步骤创建一个叫做SSISProxy的代理。这个代理将使用域帐户PowerDomainPowerUser的凭证。我们想允许testUser使用这个代理帐户登录进来执行它所拥有的SQL server Agent工作中的SSIS包。

　　;;;-- Create a credential containing the domain account PowerDomainPowerUser and its password
　　CREATE CREDENTIAL PowerUser WITH IDENTITY = N'PowerDomainPowerUser', SECRET = N'P@ssw0rd'

　　USE [msdb]

　　-- Create a new proxy called SSISProxy and assign the PowerUser credentail to it

　　EXEC msdb.dbo.sp_add_proxy @proxy_name=N'SSISProxy',@credential_name=N'PowerUser',@enabled=1

　　-- Grant SSISProxy access to the "SSIS package execution" subsystem

　　EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N'SSISProxy', @subsystem_id=11

　　-- Grant the login testUser the permissions to use SSISProxy

　　EXEC msdb.dbo.sp_grant_login_to_proxy @login_name = N'testUser', @proxy_name=N'SSISProxy'

　　GO

　　在登录进去之后，testUser授权这个访问给代理帐户SSISProxy，在testUser所拥有的一个工作中，如果一个工作步骤是要执行一个SSIS包，那么testUser可以选择代理SSISProxy并在这个代理帐户下运行这个步骤。

　　sql server 2005有11个子系统，罗列如下：

　　1 Value Description

　　2 microsoft ActiveX Script

　　3 Operating system (CmdExec)