Creating a “Run As” Account for Scheduled ETL Jobs

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.


Running Visual Studio as a different user

When you are working as a consultant at clients and your machine is not on their network and you need to develop in Visual Studio with the credential they provide, it can be quite handy to run your Visual Studio with the Windows credentials they provide.

There are a few ways of doing this, in the post I shall list two different methods and how to achieve this.

Option 1:
Shift + Right-click on the Visual Studio icon and select “Run as a different user”

A pop up will come up for you to insert the Windows Credentials. Fill in the details and click ok, Visual Studio will then open up running as the user you entered.

NB: type the username as domain\username

Option 2:
Step 1:
Open up Run

Step 2:
Type in the following:

runas /netonly /user:domain\username “C:\Program Files (x86)\Microsoft Visual Studio 11.0\Common7\IDE\devenv.exe”

Step 3:
Set the domain and username for the Windows Account details you want to run Visual Studio as. Also make sure that the path specified is correct for your laptop (depends on where you installed Visual Studio)

Step 4:
A CMD prompt will popup asking you to type in the password. You need to type in the password for the Windows Account that you are impersonating.

NB! While you are typing the password in, it will not show that you are typing in anything.