SQL Server Agent 代理

引言

所有作业都使用分配给 SQL Agent 服务的帐户运行。 如果我们必须执行一些需要额外凭据的任务，或者反过来，我们应该怎么做？ 答案是 SQL Server Agent 代理。 代理是为了获得额外的安全性。

您可以为需要不同安全上下文的作业步骤指定使用指定代理的安全上下文。

背景

SQL Server 作业在分配给 SQL Server Agent 的帐户的安全上下文中运行。 我们想要在不同的安全上下文中运行作业的特定步骤。 答案是 SQL Agent 代理。

凭据 (Credential)

在创建代理帐户之前，必须先创建一个凭据。

当您在上面显示的上下文菜单中选择新建凭据时，将出现以下表单

USE [master]

CREATE CREDENTIAL [TstCredential] WITH
IDENTITY = N'SHUJAAT-PC\shujaat',

SECRET = N'shujaat'

 FOR CRYPTOGRAPHIC_PROVIDER MySecurityProvider

GO 

子系统 (Sub System)

这是一个具有预定义功能的 SQL Server 对象。 它们用于限制提供给代理的功能。 可以使用一个或多个子系统定义代理。 子系统列表如下

1. ActiveX 脚本 (ActiveX Script)
2. 操作系统
3. 复制分发器 (Replication Distributor)
4. 复制合并 (Replication Merge)
5. 复制队列读取器 (Replication Queue Reader)
6. 复制快照 (Replication Snapshot)
7. 复制事务日志读取器 (Replication Transaction-Log Reader)
8. SQL Server Analysis Services 命令 (SQL Server Analysis Services Command)
9. SQL Server Analysis Services 查询 (SQL Server Analysis Services Query)
10. SQL Server Integration Services 包 (SQL Server Integration Services Package)
11. 未分配的代理 (Unassigned Proxy)

要访问指定的子系统，代理必须使用 Windows 用户的安全上下文。

现在可能会有一个问题，我们是否可以添加我们自己的子系统。 答案是否定的。 我们只能使用 SQL Server 提供的那些子系统，而不能添加任何新的子系统。

在“主体”选项卡上，您可以指定登录信息。

它是如何工作的？

当 SQL Server 执行具有使用任何特定代理的步骤的作业时，SQL Server 会模拟代理的用户帐户来运行特定的作业步骤。

是否存在例外情况？

是的！ 如果没有特别指定用户，则定义为 T-SQL 步骤的作业步骤在作业所有者的安全上下文中运行。

如图所示，一旦您选择作业步骤类型为 Transact-SQL 脚本 (T-SQL)，**以...身份运行 (Run as)** 选择框将变为禁用状态。

Transact SQL 支持

sp_enum_sqlagent_subsystems: 列出系统中定义的 SQL Server Agent 子系统。

EXEC sp_enum_sqlagent_subsystems 

sp_enum_proxy_for_subsystem: 列出分配给代理的子系统。

EXEC dbo.sp_enum_proxy_for_subsystem 

sp_enum_login_for_proxy: 添加新的作业步骤。 这也可能添加代理信息。

USE msdb
EXEC dbo.sp_enum_login_for_proxy ;
GO 

sp_grant_proxy_to_subsystem: 将子系统分配给代理。

USE msdb
EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N'MyProxy',
@subsystem_id=3
GO

sp_grant_login_to_proxy: 将登录名分配给代理。

USE msdb
EXEC msdb.dbo.sp_grant_login_to_proxy
          @proxy_name=N'MyProxy',
          @msdb_role=N'UserRole' 

sp_delete_proxy: 删除指定的代理。

USE msdb
EXEC dbo.sp_delete_proxy
    @proxy_name = N'MyProxy' ;
GO 

sp_update_proxy: 使用新信息更新指定的代理。

USE msdb
EXEC dbo.sp_update_proxy
         @proxy_name = N'NewProxyName',
         @enabled = 0;
GO 

sp_add_proxy: 添加新的代理

USE msdb
EXEC msdb.dbo.sp_add_proxy
          @proxy_name=N'MyProxy',@credential_name=N'TstCredential',
          @enabled=1
GO 

在您的设计中加入代理的步骤

1. 创建凭据

CREATE CREDENTIAL [TstCredential] WITH IDENTITY = N'SHUJAAT-PC\shujaat',
SECRET = N'shujaat'
FOR CRYPTOGRAPHIC_PROVIDER MySecurityProvider
GO 

2. 定义代理

EXEC msdb.dbo.sp_add_proxy
@proxy_name=N'MyProxy',
@credential_name=N'MyCredential',
@enabled=1
GO
--Run operating system command (sub-system = 3)
EXEC msdb.dbo.sp_grant_proxy_to_subsystem
@proxy_name=N'MyProxy',
@subsystem_id=3
GO
EXEC msdb.dbo.sp_grant_login_to_proxy
@proxy_name=N'MyProxy',
@msdb_role=N'MyUserRole'
GO

3. 添加包含代理信息的作业步骤

USE msdb;
GO
EXEC sp_add_jobstep
@job_name = N'Weekly Sales Data Backup',
@step_name = N'Set database to read only',
@subsystem = N'TSQL',
@command = N'ALTER DATABASE SALES SET READ_ONLY',
@retry_attempts = 5,
@retry_interval = 5
@proxy_name = N'MyProxy';
GO

历史

• 2008 年 8 月 28 日：首次发布