集成服务目录使用哪些用户凭证来执行包?

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.

我们有一个SSIS包，它从共享网络目录中读取文本文件。当我在SSDT中执行这个包时，它可以正常工作。然而，当我们将项目部署到集成服务目录并试图从该目录运行相同的包时，我得到一个错误，指示拒绝对目录的访问。

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.

我一直认为，当我登录到数据库并从集成服务目录执行SSIS包时，它使用我的用户凭证，因此可以访问相关目录，因为我的用户概要文件可以访问它。

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是否使用不同的用户来执行来自IS目录的包?是否有另一个用户运行的选项?任何输入都将被欣赏。

3 个解决方案

#1

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.

使用SQL Server数据工具(SSDT)操作的用户凭据将用于执行在SSDT内执行的包。

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.

在Windows启动\所有程序\ Microsoft SQL Server 2012中，如果您单击SQL Server数据工具，它将在您的凭据下运行。要在不同的用户帐户下运行，可以按Ctrl + Shift选择run作为不同的用户选项。

Integration Catalog Services:

When you right-click on a package under Integration Services Catalog \ SSISDB \ <Folder name> \ Projects \ <Project name> \ Pakages \ <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 Management Studio的凭据下运行。

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

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

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

不能由使用SQL Server身份验证的帐户启动该操作。使用使用Windows身份验证的帐户启动操作。

SQL Server Agent Job Without Proxy:

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代理作业中运行一个SSIS包时，默认的作业步骤是在sqlserver代理服务帐户下运行。用户帐户关联的SQL Server代理服务可以通过导航到Windows Start \管理工具\服务找到，查找服务SQL Server代理(您的实例名)，并找到在登录时列出的用户帐户。

SQL Server Agent Job With Proxy:

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.

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

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

因此，answer提供了创建代理帐户以运行SQL Server代理作业的步骤说明。

How do I create a step in my SQL Server Agent Job which will run my SSIS package?

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

How to verify:

Sample SSIS 2012 package:

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

下面是我对用于包执行的用户帐户进行的验证。

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

  打开SQL Server数据工具并创建和SSIS 2012包SO_15289442.dtsx

• 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.

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

• Drag and drop Send Mail Task onto the Data Flow tab.

  拖放“发送邮件”任务到“数据流”选项卡。

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

  创建一个SMTP连接，并将其分配到发送邮件任务编辑器-邮件页面上的SmtpConnection。

• Specify the From and To email addresses.

  指定从和到电子邮件地址。

• Change the MessageSourceType to Variable.

  将MessageSourceType更改为变量。

• Set the MessageSource to User::ExecutionUser.

  将MessageSource设置为User::ExecutionUser。

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

  右键单击包并选择Deploy，以便将项目部署到您选择的服务器上可用的集成服务目录。

Package executions

• 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.

  使用运行作为不同的用户选项打开SSDT。提供与您不同的证书，并再次运行该包。

• Run the package from Integration Services Catalog

  从集成服务目录运行包。

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

  创建一个SQL Server代理作业，使用sqlserver代理服务帐户运行包。

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

  创建一个SQL Server代理作业，以使用代理帐户运行包。

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

对于上面提到的每个执行，您将收到一封带有用于执行包的用户帐户的电子邮件。

Your Issue:

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 folder has access to the network path.

在您的示例中，如果您右键单击并从集成服务目录中选择execute，那么该包将在您的帐户下执行(假设您正在使用您的凭据访问SSISDB)。确保文件夹能够访问网络路径。

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.

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

#2

It sounds like you don't have Kerberos configured on your SQL Server (double hop issue).

听起来您没有在SQL服务器上配置Kerberos(双跳问题)。

Here is the issue logged with MS which has been fixed now. https://connect.microsoft.com/SQLServer/feedback/details/767088/with-the-new-ability-to-execute-ssis-packages-from-tsql-kerberos-delegation-should-be-supported

这是MS记录的问题，现在已经修复了。https://connect.microsoft.com/SQLServer/feedback/details/767088/with-the-new-ability-to-execute-ssis-packages-from-tsql-kerberos-delegation-should-be-supported

Here is a great blog post on the details of this: http://www.sqlscientist.com/2014/01/setup-kerberos-authentication-for-sql.html

这里有一个很好的博客文章:http://www.sqlscientist.com/2014/01/setup- kerberos-authenticationfor sql.html。

Once this is configured, you should be able to remotely kick off the job using a Stored Procedure or SSMS on your local machine. It will pass your credentials if it is configured correctly, of course you have to make sure you have the proper permissions to access the network resource.

配置好后，您应该能够使用本地机器上的存储过程或SSMS远程启动作业。如果配置正确，它将传递您的凭据，当然，您必须确保具有访问网络资源的适当权限。

#3

This is an old thread, but I imagine that the problem you are experiencing is actually with the path to the file. Are you using a UNC name or a drive letter. That is, is your file path something like "Z:\path\to\file.csv" or "\server\share\path\to\file.csv"?

这是一个旧的线程，但是我认为您遇到的问题实际上是文件的路径。你使用的是UNC名称还是驱动器号?也就是说，您的文件路径是“Z:\路径\到\文件”之类的。csv”或“\ server \ \分享路径\ \ file.csv”?

If it is a drive letter, it will work fine from your local machine as you have that drive mapped, but will fail on the server as the drive letter is not mapped.

如果它是一个驱动器号，那么它将在您的本地机器上正常工作，因为您有该驱动器映射，但是当驱动器号没有映射时，它将在服务器上失败。