Which user credentials does Integration Services Catalog use to execute packages?

Question

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.

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.

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.

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

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 Catalog Services:

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.

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

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

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

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

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

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

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.

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

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

• Specify the From and To email addresses.

• Change the MessageSourceType to Variable.

• Set the MessageSource to 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.

Package executions

• Run the package within 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.

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

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

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

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.