从SQL Server 代理作业步骤调用时，SSIS 包不会运行

  • 项目

本文可帮助你解决从SQL Server 代理作业步骤调用 SSIS 包时出现的问题。

原始产品版本：SQL Server
原始 KB 编号： 918760

症状

从SQL Server 代理作业步骤调用 Microsoft SQL Server Integration Services (SSIS) 包时，SSIS 包不会运行。 但是，如果不修改 SSIS 包，它将在 SQL Server 代理 外部成功运行。

解决方案

若要解决此问题，请使用以下方法之一。 最合适的方法取决于环境和包失败的原因。 包可能失败的原因如下：

  • 用于在 SQL Server 代理 下运行包的用户帐户不同于原始包作者。
  • 用户帐户没有进行连接或访问 SSIS 包外部资源所需的权限。

包在以下情况下可能无法运行：

  • 当前用户无法解密包中的机密。 如果当前帐户或执行帐户与原始包作者不同，并且包的 ProtectionLevel 属性设置不允许当前用户解密包中的机密，则可能会出现这种情况。
  • 使用集成安全性的SQL Server连接失败，因为当前用户没有所需的权限。
  • 文件访问失败，因为当前用户没有写入连接管理器访问的文件共享所需的权限。 例如，不使用登录名和密码的文本日志提供程序可能会出现这种情况。 此方案也可能发生在依赖于文件连接管理器的任何任务（例如 SSIS 文件系统任务）中。
  • 基于注册表的 SSIS 包配置使用 HKEY_CURRENT_USER 注册表项。 注册表项 HKEY_CURRENT_USER 特定于用户。
  • 任务或连接管理器要求当前用户帐户具有正确的权限。

若要解决此问题，请使用以下方法：

  • 方法 1：使用 SQL Server 代理 代理帐户。 创建SQL Server 代理代理帐户。 此代理帐户必须使用允许SQL Server 代理作为创建包的帐户或具有所需权限的帐户运行作业的凭据。

    此方法可用于解密机密，并满足用户的密钥要求。 但是，此方法的成功可能有限，因为 SSIS 包用户密钥涉及当前用户和当前计算机。 因此，如果将包移动到另一台计算机，即使作业步骤使用正确的代理帐户，此方法仍可能失败。

  • 方法 2：将 SSIS 包 ProtectionLevel 属性设置为 ServerStorage。 将 SSIS 包 ProtectionLevel 属性更改为 ServerStorage。 此设置将包存储在SQL Server数据库中，并允许通过SQL Server数据库角色进行访问控制。

  • 方法 3：将 SSIS Package ProtectionLevel 属性设置为 EncryptSensitiveWithPassword。 将 SSIS 包 ProtectionLevel 属性更改为 EncryptSensitiveWithPassword。 此设置使用密码进行加密。 然后，可以修改SQL Server 代理作业步骤命令行以包含此密码。

  • 方法 4：使用 SSIS 包配置文件。 使用 SSIS 包配置文件存储敏感信息，然后将这些配置文件存储在安全文件夹中。 然后，可以将 属性更改为 ProtectionLevelDontSaveSensitive ，以便包未加密，并且不会尝试将机密保存到包中。 运行 SSIS 包时，将从配置文件加载所需的信息。 如果配置文件包含敏感信息，请确保它们受到充分保护。

  • 方法 5：创建包模板。 对于长期解决方案，请创建一个包模板，该模板使用不同于默认设置的保护级别。 将来的包中不会出现此问题。

重现问题的步骤

  1. 以不属于 SQLServerSQLAgentUser 组的用户身份登录。 例如，可以创建本地用户。
  2. 创建 SSIS 包，然后添加 ExecuteSQL 任务。 使用以下字符串将 OLE DB 连接管理器用于本地 msdb 文件： 'Windows Authentication' -SQLSourceType: "Direct Input" -SQLStatement: "sp_who"。
  3. 运行包以确保它成功运行。
  4. ProtectionLevel 属性设置为 EncryptSensitiveWithPassword。
  5. 创建SQL Server 代理作业和作业步骤。 在“运行方式”列表中，单击“SQL Server 代理服务”以运行作业步骤。 SQL Server 代理作业历史记录中的文本显示类似于以下内容的信息：

解密包机密

SSIS 包 ProtectionLevel 属性的默认设置为 EncryptSensitiveWithUserKey。 保存包时，SSIS 仅加密包含标记 sensitive的属性的包部分，例如密码、用户名和连接字符串。 因此，重新加载包时，当前用户必须满足要解密的属性的 sensitive 加密要求。 但是，当前用户不必满足加密要求来加载包。 通过SQL Server 代理作业步骤运行包时，默认帐户是 SQL Server 代理 服务帐户。 此默认帐户很可能是与包作者不同的用户。 因此，SQL Server 代理作业步骤可以加载并开始运行作业步骤，但包会失败，因为它无法完成连接。 例如，包无法完成 OLE DB 连接或 FTP 连接。 包失败，因为它无法解密它必须连接的凭据。

重要

请考虑开发过程和环境，以确定每台计算机上需要和使用哪些帐户。 属性的 ProtectionLevel EncryptSensitiveWithUserKey 设置是一个功能强大的设置。 此设置不应打折扣，因为它最初会导致部署复杂。 登录到相应的帐户后，可以加密包。 还可以使用 Dtutil.exe SSIS 命令提示符实用工具通过.cmd文件和SQL Server 代理命令子系统来更改保护级别。 例如，请按照以下步骤操作。 由于可以在批处理文件和循环中使用 Dtutil.exe 实用工具，因此可以同时对多个包执行这些步骤。

  1. 使用密码修改要加密的包。

  2. 通过操作系统 (cmd Exec) SQL Server 代理作业步骤使用 Dtutil.exe 实用工具将 ProtectionLevel 属性更改为 EncryptSensitiveWithUserKey。 此过程涉及使用密码解密包，然后重新加密包。 用于加密包的用户密钥是运行方式列表中的SQL Server 代理作业步骤设置。

    注意

    由于密钥包含用户名和计算机名称，因此将包移动到另一台计算机的效果可能会受到限制。

确保具有有关 SSIS 包失败的详细错误信息

可以使用 SSIS 日志记录来确保包含有关 SSIS 包失败的错误信息，而不是依赖于SQL Server 代理作业历史记录中的有限详细信息。 还可以使用 exec 子系统命令而不是 SSIS 子系统命令来运行包。

关于 SSIS 日志记录

SSIS 日志记录和日志提供程序可用于捕获有关包执行和失败的详细信息。 默认情况下，包不记录信息。 必须将包配置为记录信息。 将包配置为记录信息时，将显示如下所示的详细信息。 在这种情况下，你会知道这是一个权限问题：

OnError,DOMAINNAME,DOMAINNAME\USERNAME,FTP Task,{C73DE41C-D0A6-450A-BB94-DF6D913797A1},{2F0AF5AF-2FFD-4928-88EE-1B58EB431D74},4/28/2006 1:51:59 PM,4/28/2006 1:51:59 PM,-1073573489,0x,Unable to connect to FTP server using "FTP Connection Manager".

OnError,DOMAINNAME,DOMAINNAME\USERNAME,Execute SQL Task,{C6C7286D-57D4-4490-B12D-AC9867AE5762},{F5761A49-F2F9-4575-9E2B-B3D381D6E1F3},4/28/2006 4:07:00 PM,4/28/2006 4:07:00 PM,-1073573396,0x,Failed to acquire connection "user01.msdb". Connection may not be configured correctly or you may not have the right permissions on this connection.

关于 exec 子系统命令和输出信息

通过使用 exec 子系统命令方法，将详细控制台日志记录开关添加到 SSIS 命令行，以调用 Dtexec.exe SSIS 命令行可执行文件。 此外，使用输出文件的“高级作业”功能。 还可以使用“在历史记录中包含步骤输出”选项将日志记录信息重定向到文件或SQL Server 代理作业历史记录。

下面是命令行的示例：

 dtexec.exe /FILE "C:\_work\SSISPackages\ProtectionLevelTest\ProtectionLevelTest\AgentTesting.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V /CONSOLELOG NCOSGXMT

控制台日志记录将返回如下所示的详细信息：

Error: 2006-04-27 18:13:34.76 Code: 0xC0202009 Source: AgentTesting Connection manager "(local).msdb" Description: An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Login failed for user 'DOMAINNAME\username'.". End Error

Error: 2006-04-28 13:51:59.19 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "DTS:Property" with error 0x80070002 "The system cannot find the file specified.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error

Log: Name: OnError Computer: COMPUTERNAME Operator: DOMAINNAME\username Source Name: Execute SQL Task Source GUID: {C6C7286D-57D4-4490-B12D-AC9867AE5762} Execution GUID: {7AFE3D9E-5F73-42F0-86FE-5EFE264119C8} Message: Failed to acquire connection "(local).msdb". Connection may not be configured correctly or you may not have the right permissions on this connection. Start Time: 2006-04-27 18:13:34 End Time: 2006-04-27 18:13:34 End Log

References

遗憾的是，用户不知道默认代理作业步骤设置使他们处于此状态。 有关SQL Server 代理代理和 SSIS 的详细信息，请参阅 SQL Server 2005 联机丛书中的以下主题：

  • 在 SQL Server 代理 中计划包执行
  • 创建SQL Server 代理代理