Note:  This is for the new package deployment process in Sql Server 2012.

Situation: I need to execute a Sql Server 2012 Integration Services package from the new SSISDB database by a Sql Server Agent job.  This package needs to run across servers and across domains that has only a one way trust relationship. Due to security issues, I will not use Sql Server Authentication accounts.  Domain B Server B will pull data from Domain A Server A.  Domain B trust Domain A but Domain A does not trust Domain B.  Server A is a Sql Server 2008 R2 server and Server B is a Sql Server 2012 server. All SSIS packages will reside on Sql Server 2012 in the Integration Services Catalog SSISDB database.

Setup:

Domain A  –  Server A

Domain B – Server B

Solution:

When we create a new job to run the SSIS package, by default the package will run with the account which runs Sql Server Agent.  In our scenario, Sql Server agent is running on Server B with a domain account which resides in Domain B.  This Sql Server agent account can’t be added to Server A so it will not be able to access data on Server A.  I need the SSIS package to get data from Server A.  Because of the domain relationship, I can’t use the Sql Server Agent account running on Server B to run my SSIS package as it does not have access to Server A.

Here is how I solved the issues.  First, we need to setup a new domain account which can talk to both domains.  The new Windows Authentication account is setup in Domain B.

Add Windows Login

We added the new Windows Account into Sql Server login and added the account to the database on Server A where I need to get my data from.

Add Database User

In my case, we then gave the account the right to select the data from the tables we need to access on Server A. You could also give the account db_datareader but since you have gone to this extent to secure the database, I would only give it access only to the tables it needs to read. To make it easier to move between environments like Dev, QA and Prod, I would add a database role and assign all security to the database role.

Add Database Role

Next we need to add the Windows Account login to the database role.

Add Database User to Database Role

All security on the tables and stored procedures should be granted to the database role.

Grant Object Security to Database Role

On Server B, we add the new Windows Account into Sql Server login.  Since this is going to running the job, we need to create a proxy setting for this account. To setup the proxy, first I added the Windows Account as a Security Credential.  For this you will need the password of the Windows Account.

Add Credential

After setting the Windows Account as a Security Credential account, under Sql Server Agent, I added the Windows Account to Proxies.

Add Credential to Sql Agent Proxy for SSIS

The new Windows Account was added under SSIS Package Execution in the Proxies area.  Next we will add principals, SqlAgentOperatorRole and db_ssisadmin to the new proxy account.  Both of these principals are found under type, select the MSDB role.  In addition to the proxy principals, we also add proxy reference to each job step which uses the proxy account.

Add Job steps with Proxy account to add to this screen

Next we need to add security to the SSISDB.

Add Security to SSIS DB

At the project folder level in the SSISDB, we need to add permissions of read and execute objects.  When you deployed the SSIS packages, a new folder is created under project folder called projects.  This will create a Project with the same name as your solution.

Add Security to SSIS Project

You will right click on your project, select properties. On the properties page, you will see the permission information.  Add the Windows Account and give it Read and Execute permissions. You can now create your Sql Agent Job using new security.

Add Job Step with new security

You should now be able to execute your job. For assistance, please contact Tom at ArmorDBA@gmail.com.