Execute an SSIS package under a proxy account using SQL Agent job

Database administrators might sometimes come across the situations where the users need access to run SSIS packages (in environments where the DBA team does not generally own the responsibility of ancillary services). We can’t let the users have admin privileges to be able to run the packages as jobs. In such situations proxy account comes into play. A proxy account can be created and the job can be configured to be run under that proxy account. Follow the below steps to accomplish that

1. A new NT account needs to be created for the purpose of having a SQL job (SSIS package) executed under a proxy account. Most important the NT account has to be created with ‘No Password Expiry’ option.

2. Provide the SQL DBA team a list of users that need to have access and execute the jobs on SQL Server.

3. Add the userid (that needs to execute the jobs) provided by the Business Unit to SQLAgentUserRole in the msdb database. Also make that userid owner for the job that needs to be executed.

Creating a new proxy by linking it to an already existing credential:

4. Grant access to the above created NT account in this case on SQL Server with sysadmin permissions. (creating a new login).

5. Create a new credential:

Expand security --> go to credentials --> right-click and hit new credential.

Put in the password for the already existing NT account which has sysadm permissions on the SQL server

6. Expand SQL Server Agent --> right click on Proxies and hit new proxy

On ‘General’ tab enter the proxy name and enter the credential name created in the first step select the subsystems as required

7. On the ‘Principal’ hit on the button ‘Add’ and add the required logins that are required to make use of the proxy account.

8. Edit the job properties and hit ‘Edit’.

In the drop down list of ‘Run as’ select the newly created proxy account and hit ok.

The job should run successfully now.