When working in large SQL production environments that have multiple jobs doing different things you might want the ETL jobs to run as another account other than SQL Server Agent.

This can help with security by limiting the amount of access the account has to the databases the packages in the ETLs are connecting to.

This is quite simple to set up. I recommend using a Windows AD account. In my example below of the setup I shall be demonstrating how to create an account to run the ETL jobs.

Step 1:
You will need to add the account to the Logins under Security on the server where the jobs and are executing and the ETL projects are deployed to.
Step 1

Step 2:
You will need to create a Credential under Security that links to the account you created. Right click on Credential and select New Credential…
Step 2

Step 3:
You will give the Credential a name and select the ellipse just below it so that you can select the Windows account you added under Logins in step 1 and click Ok. You will then type in the same password for the Windows AD account and click Ok. Now your credential is set up.
Step 3
Step 3a

Step 4:
Next you will need to add the Credential to the SSIS Package Execution Proxy so that it can execute SSIS packages on the server. Expand the SQL Server Agent node, expand the Proxies node and right click on SSIS Package Execution and select New Proxy…
Step 4

Step 5:
Give your Proxy a name. I choose to use the same name as the Windows AD account throughout the entire setup. Then select the ellipse next to Credential so that you can add the Credential you created in step 3 and click Ok.
Step 5

You will notice that SQL Server Integration Services Package is checked under Active to the following subsystems. This allows the account to execute SSIS packages. You can click Ok and the setup is complete.
Step 5a

You will now see the Proxy you created under SSIS Package Execution.
Step 5b

Step 6:
When you create the new job and in the steps section set the type to SQL Server Integration Services Package, you will then be able to click on the drop down below Run as and you shall see the Proxy account you set up. You can select it and then your job will run as that account.
Step 6

An important note to remember is that the Windows AD account you are using will need Read/Write access on the databases that are used as connections in the ETL packages.


Leave a Reply

Your email address will not be published. Required fields are marked *