主页 / dba / 问题 / 120907
Accepted
Mathieu Guindon
Asked: 2015-11-13 11:28:31 +0800 CST

从非系统管理员域用户拥有的 SQL 代理作业运行 SSIS 包

  • 772

作为大型 SSIS 部署的一部分，我有两个 SSIS 包在夜间运行（通过 SQL Server 代理），没有任何问题。一切都使用 Windows 身份验证，并且计划的作业由系统管理员（好吧，我）拥有并作为SQL Server 代理服务帐户运行。

因此，数据基本上是source system ~> transit db ~> staging ~> NDS一夜之间的。

我关心的两个 SSIS 包分别处理特定数据集的transit db ~> staging和部分。staging ~> NDS

域用户（非系统管理员）在 中执行某些操作source system，并将感兴趣的数据推送到 中transit db，因此我需要一种在工作时间获取更新数据的方法来更新NDS：决定此人触发的最简单方法该 ETL 是通过单击启用宏的 Excel 工作簿中的按钮，该工作簿通过 ODBC（使用 Windows 身份验证）连接到 SQL Server 并执行存储过程。

存储过程如下所示：

create procedure dbo.UpdateMaterialInventory
as
begin
    execute msdb.dbo.UpdateMaterialInventory;
end

[msdb] 中的“姐妹”存储过程如下所示：

create procedure dbo.UpdateMaterialInventory
with execute as 'SqlAgentProxy'
as
begin
    execute msdb.dbo.sp_start_job N'NDS-ManualMaterialInventory';
end

这个 [SqlAgentProxy] 用户是我在域用户登录的 [msdb] 中创建的 Windows 用户，我授予execute了此UpdateMaterialInventory过程的权限。这避免了必须授予域用户execute权限msdb.dbo.sp_start_job，这将是过度的。

SQL 代理作业NDS-ManualMaterialInventory归域用户所有，有 2 个步骤，每个步骤类型为 [SQL Server Integration Services Package]，设置为Run as SSISProxy。

SSISProxy是一个 SQL Server 代理代理，它使用 credential name 映射到 [SQL Server Integration Services Package] 子系统SSISProxyCredentials。域用户的登录已添加到代理帐户 principals。

它们是使用在夜间运行整个 SSIS ETL 的同一域用户的身份创建的，并且其密码经过四重检查SSISProxyCredentials。

现在，如果我运行这个：

execute as login=N'DOMAIN\thatperson'
exec NDS.dbo.UpdateMaterialInventory;
go

我得到这个输出：

Job 'NDS-ManualMaterialInventory' started successfully.

然而，工作经历讲述了一个不那么令人鼓舞的故事：

The job failed.  The Job was invoked by User DOMAIN\thatperson.
The last step to run was step 1 (Extract).

第 1 步详细信息：

Executed as user: {domain user that runs SSIS ETL overnight}.
Microsoft (R) SQL Server Execute Package Utility  Version 12.0.4100.1 for 64-bit
Copyright (C) Microsoft Corporation. All rights reserved.
Started:  2:18:50 PM  Failed to execute IS server package because of error 0x80131904.
Server: {server name}, Package path: \SSISDB\Foo\Bar\foobar.dtsx, Environment reference Id: NULL.
Description: Login failed for user '{domain user that runs SSIS ETL overnight}'.
Source: .Net SqlClient Data Provider 
Started:  2:18:50 PM  Finished: 2:18:51 PM  Elapsed:  0.094 seconds.
The package execution failed.
The step failed.

作业失败，任何地方都没有记录任何内容。

如果我将作业所有者更改为我自己，并将步骤的运行更改为SQL Server 代理服务帐户，作业将运行、成功并将 1,067 行记录到 [Metadata].[dbo].[sysssislog]。

看起来代理/凭据的设置方式有些不对劲。我做错了哪一部分？

sql-server permissions

1 个回答

  1. Best Answer

    这个问题看起来比实际更复杂。由于您使用的是 SQL 2014，因此您可能会被 2012 年引入的新安全功能所困扰。

    唯一真正重要的是：

    Server: {server name}, Package path: \SSISDB\Foo\Bar\foobar.dtsx, Environment reference Id: NULL.   
Description: Login failed for user '{domain user that runs SSIS ETL overnight}'.

    您的代理用户的登录很可能无权访问 SSISDB 目录（即使他可能有权访问 SQL Server）。
    您需要将登录名映射到 SSISDB 用户并配置对 Integration Services 中 SSISDB 文件夹/项目的访问权限。

    请查看此 MSDN 博客文章SSIS 目录访问控制技巧和SQL 2012 SSIS 目录权限

    实际加载包后，您可能会遇到其他安全上下文问题，但您应该从集成服务本身获得更好的日志记录。

    • 17

相关问题