Using a Proxy for SSIS Execution

When executing packages for SSIS the default option would be to use the SQL Server Agent service account. We might not want to share an account between our services and Integration Services packages due to security risks.

Let’s take a common example: Suppose we have a package which reads from a file share. If permissions aren’t set up correctly this can fail. Even if access is corrected, the service account can cache that failure. The only way to resolve may be to restart the service. This isn’t something we’d want to be doing on production systems.

An alternative is to use a proxy to execute the SSIS packages under it’s own credentials. This will execute the package under its own account and isolate those security permissions. Let’s look at setting one up.

Database permissions

As we’re using a new account for the proxy we’ll need to set it up like a regular account. For example:

USE [master];

CREATE LOGIN [MyDomain\SSIS_Production]
FROM WINDOWS;

For the databases I’d prefer to set up Roles to assign permissions. This makes scripting changes easier as roles can be consistent across environments where the accounts may differ. For example:

USE MyDb;

/* Create a role to assign permissions */
CREATE ROLE SSIS_Proxy;
GO

/* Create the user in the database */
CREATE USER [MyDomain\SSIS_Production]
FOR LOGIN [MyDomain\SSIS_Production];
GO

/* Add the user to the new role */
ALTER ROLE [SSIS_Proxy]
ADD MEMBER [MyDomain\SSIS_Production];
GO

/* Apply as many permissions as required */
GRANT EXECUTE ON dbo.MyProc
TO [SSIS_Proxy];
GO

Additionally if we want to allow access to sending emails we can do similar through msdb:

USE msdb;

/* Create a role to assign permissions */
CREATE ROLE SSIS_Proxy;
GO

/* Create the user in the database */
CREATE USER [MyDomain\SSIS_Production]
FOR LOGIN [MyDomain\SSIS_Production];
GO

/* Add the user to the new role */
ALTER ROLE [SSIS_Proxy]
ADD MEMBER [MyDomain\SSIS_Production];
GO

/* Add the proxy as a dbmail user */
ALTER ROLE [DatabaseMailUserRole]
ADD MEMBER [SSIS_Proxy];

/* Set the default mail profile for our login (can't use a role!) */
EXEC dbo.sysmail_add_principalprofile_sp
    @principal_name = N'MyDomain\SSIS_Production',
    @profile_name = N'DbMail',      /* Change as needed! */
    @is_default = 1;

Creating the proxy

Now that we’ve got our account set up, roles created and permissions assigned, we want to create a proxy for that account.

Firstly we need to add the credentials for the account so that SQL Server can authenticate when it needs to use it. These are the Windows domain credentials so adjust accordingly:

CREATE CREDENTIAL [SSISProxyAccount]
WITH IDENTITY = N'MyDomain\SSIS_Production',
    SECRET = 'My5uP3rSecR3tP4ssw0rd';       /* Domain password */

Finally we can create the proxy to use and make sure we assign it access to the SSIS subsystem too:

USE msdb

/* Create the proxy */
EXEC dbo.sp_add_proxy
    @proxy_name = N'SSISProxy',
    @credential_name = N'SSISProxyAccount'

/* Allow it to execute SSIS packages */
EXEC dbo.sp_grant_proxy_to_subsystem
    @proxy_name = N'SSISProxy',
    @subsystem_id = 11;

Folder permissions

Assigning permissions to shared areas or anywhere to collect files from will be the same as other account. There are a variety of options for this so I’m not going to try and cover those.

What I will mention specifically is a local directory on the server. You’ll need to allow access to the directory C:\Windows\Temp\ as it will be used when executing Script components. The account will need access to Read, Write, List contents and Modify files in the folder:

Using the proxy

With everything set up we’re finally able to set up a SQL Agent job step to use it.

The job and step can be set up like any other. The difference is specifically within the step where we will need to change the account being used.

On the Job Step interface when we change the Type to ‘SQL Server Integration Services Package’ we’ll now have the proxy available under the Run As dropdown:

The remainder of the step can be configured as needed for the package, parameters, etc.

When executing, the job itself and other steps within the job may execute under the regular service account. When executing our package step however, the package for this specific step will run under our new proxy.

Wrap up

In this post we’ve looked at how to create a proxy to execute our SSIS packages. This avoids using service accounts for multiple purposes which could lead to issues down the road.

Strictly speaking we could have stuck with the ‘Creating the proxy’ section to meet the goal.

We’ve gone a little further to include other common elements: Firstly, database mail for sending alerts. Secondly we’ve created a security Role to assign permissions to. The security role can help with assigning permissions to objects consistently across environments.

If you’re switching existing jobs over to use a new proxy it isn’t uncommon for more permission issues to come out of the woodwork. Hopefully I’ve covered enough here so you’ll be off to a good start with your new proxy!