Integration Services Catalog 使用哪些用户凭据来执行包? [英] Which user credentials does Integration Services Catalog use to execute packages?

问题描述

我们有一个从共享网络目录读取文本文件的 SSIS 包.当我在 SSDT 中执行这个包时，它工作正常.然而，当我们将项目部署到 Integration Services Catalog 并尝试从那里运行相同的包时，我收到一条错误消息，指示对该目录的访问被拒绝.

We have an SSIS package which reads text files from a shared network directory. When I execute this package in SSDT it works fine. When we however deploy the project to the Integration Services Catalog and attempt to run the same package from there I get an error indicating that access to the directory is denied.

我一直认为，当我登录到数据库并从 Integration Services 目录执行 SSIS 包时，它使用我的用户凭据，因此可以像我的用户配置文件一样访问有问题的目录访问它.

I've always been under the impression that when I log on to the database and execute an SSIS package from the Integration Services Catalog that it uses MY user credentials and would thus have access to the directory in question as my user profile has access to it.

难道不是这样吗?SQL Server 是否使用不同的用户来执行 IS 目录中的包?是否可以选择以其他用户身份运行?任何输入将不胜感激.

Is this not the case? Does SQL Server use a different user to execute packages from the IS catalog? Is there an option to run as another user? Any input will be appreciated.

推荐答案

包执行:

SQL Server 数据工具:

SQL Server Data Tools (SSDT) 在其下运行的用户凭据将用于执行在 SSDT 中执行的包.

Package Executions:

SQL Server Data Tools:

User credentials under which SQL Server Data Tools (SSDT) is operating will be used to execute the packages that are executed within SSDT.

在 Windows 开始\所有程序\Microsoft SQL Server 2012 下，如果您单击 SQL Server Data Tools，它将在您的凭据下运行.要在不同的用户帐户下运行，您可以按 Ctrl + Shift 选择 Run as different user 选项.

Under Windows Start \ All Programs \ Microsoft SQL Server 2012, if you click SQL Server Data Tools it will run under your credentials. To run under different user account, you could press Ctrl + Shift to select Run as different user option.

当您右键单击Integration Services Catalog \ SSISDB \ <文件夹名称> 下的一个包时；\ 项目 \ <项目名称>\ Packages \ <包名称> 并选择 Execute... 以运行包.该包将在用于连接到 SQL Server Management Studio 的凭据下运行.

When you right-click on a package under Integration Services Catalog \ SSISDB \ <Folder name> \ Projects \ <Project name> \ Packages \ <Package name> and select Execute... to run a package. The package will run under the credentials used to connect to SQL Server Management Studio.

请注意，如果您尝试使用 SQL Server 身份验证运行包，您将收到以下错误消息:

Note that if you try to run a package using SQL Server Authentication, you will get the below error message:

使用 SQL Server 身份验证的帐户无法启动该操作.使用使用 Windows 身份验证的帐户开始操作.

The operation cannot be started by an account that uses SQL Server Authentication. Start the operation with an account that uses Windows Authentication.

在您按下 Execute... 后，SQL Server 会启动一个进程 ISServerExec.exe，它会启动一个 Console Window Host 流程:

After you press Execute..., SQL Server spins a process ISServerExec.exe, which launches a Console Window Host process:

ISServerExec.exe 是一个在 SSIS 目录中执行包的程序.在这种情况下，它是在从 SQL Server Management Studio 执行 SSIS 包的同一用户下启动的.

ISServerExec.exe is a program which executes packages in SSIS Catalog. In this case, it is launched under the same user who executes an SSIS package from SQL Server Management Studio.

当您从 SQL Server 代理作业中运行 SSIS 包时，作业步骤默认在 SQL Server 代理服务帐户下运行.可以通过导航到 Windows Start\Administrative Tools\Services 找到与 SQL Server 代理服务关联的用户帐户，查找服务 SQL Server 代理(Your Instance Name) 并找到 Log On As

When you run an SSIS package from within an SQL Server Agent Job, the job step by default runs under SQL Server Agent Service Account. The user account associated SQL Server Agent Service can be found by navigating to Windows Start \ Administrative Tools \ Services, look for the service SQL Server Agent (Your Instance Name) and find the user account listed under Log On As

您还可以通过创建代理帐户以不同的凭据运行 SQL Server 代理作业.在代理账户下执行作业步骤时，作业步骤中的包将在代理账户指定的凭据下执行.

You could also run an SQL Server Agent Job under different credentials by creating a proxy account. When job steps are executed under proxy account, the package in the job step will execute under the credential specified on the proxy account.

以下 SO 答案提供了创建代理帐户以运行 SQL Server 代理作业的分步说明.

Below SO answer provides step-by-step instructions to create proxy account to run SQL Server Agent Jobs.

如何在我的 SQL Server 代理作业中创建一个步骤来运行我的 SSIS 包?

以下是我针对用于包执行的用户帐户验证上述语句的操作.

Here is what I did to verify the above statements with respect to the user accounts used for package executions.

• 打开 SQL Server Data Tools 并创建名为 SO_15289442.dtsx 的 SSIS 2012 包.

• Open SQL Server Data Tools and create and SSIS 2012 package named SO_15289442.dtsx.

创建一个名为 ExecutionUser 的数据类型为 String 的变量.将表达式 @[System::UserName] 分配给变量.UserName 是一个系统变量，提供用户执行包的信息.

Create a variable named ExecutionUser of data type String. Assign the expression @[System::UserName] to the variable. UserName is a system variable that provides the information of the user executing the package.

将发送邮件任务拖放到数据流标签上.

创建 SMTP 连接并将其分配给发送邮件任务编辑器 - 邮件页面上的 SmtpConnection.

Create an SMTP connection and assign it to the SmtpConnection on the Send Mail Task Editor - Mail page.

指定 From 和 To 电子邮件地址.

Specify the From and To email addresses.

将 MessageSourceType 更改为 Variable.

Change the MessageSourceType to Variable.

将 MessageSource 设置为 User::ExecutionUser.

右键单击包并选择部署"以将项目部署到您选择的服务器上可用的 Integration Services Catalog.

Right-click on the package and select Deploy to deploy the project to Integration Services Catalog available on a server of your choice.

• 在 SSDT 中运行包.

• Run the package within SSDT.

使用以不同用户身份运行选项打开 SSDT.提供与您不同的凭据，然后再次运行程序包.

Open SSDT using Run as different user option. Provide a different credential other than yours and run the package again.

从集成服务目录运行包.

Run the package from Integration Services Catalog.

创建 SQL Server 代理作业以使用 SQL Server 代理服务帐户运行包.

Create an SQL Server Agent Job to run the package using SQL Server Agent Service Account.

创建 SQL Server 代理作业以使用代理帐户运行包.

Create an SQL Server Agent Job to run the package using a proxy account.

对于上述每次执行，您都会收到一封电子邮件，其中包含用于执行包的用户帐户.

For every execution mentioned above, you will receive an email with the user account that was used to execute the package.

在您的情况下，如果您右键单击并从 Integration Services 目录中选择执行"，则程序包将在您的帐户下执行(假设您正在使用您的凭据访问 SSISDB).确保该帐户可以访问网络路径.

In your case, the package will execute under your account (assuming that you are using your credentials to access SSISDB) if you right-click and select Execute from Integration Services Catalog. Make sure that the account has access to the network path.

如果您从 SQL Server 代理作业中运行您的包，代理帐户是您正在查看的 作为另一个用户运行 选项.

If you are running your package from within SQL Server Agent Job, proxy account is the run as another user option that you are looking at.

这篇关于Integration Services Catalog 使用哪些用户凭据来执行包?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！