Running an SSIS package from a SQL Agent job as a specific Windows account: credential, proxy and step

#Why a proxy

By default, SSIS job steps run as the SQL Server Agent service account. That account then needs access to every file share, FTP folder and remote database every package touches. A proxy lets each package run as an account with only the rights it needs.

#1. Create the Windows account

Ask AD admins for a domain account, for example CONTOSO\svc-ssis-finance. Grant it:

• Access to the file shares and folders the package uses.
• Logins/permissions on the databases the package connects to with Windows authentication.
• Log on as a batch job on the SQL Server host. Check this; GPOs often restrict it.

#2. Credential and proxy

USE master;
CREATE CREDENTIAL cred_ssis_finance
  WITH IDENTITY = N'CONTOSO\svc-ssis-finance', SECRET = N'<password>';

USE msdb;
EXEC dbo.sp_add_proxy @proxy_name = N'SSIS Finance', @credential_name = N'cred_ssis_finance',
     @enabled = 1, @description = N'Runs Finance SSIS packages';

EXEC dbo.sp_grant_proxy_to_subsystem @proxy_name = N'SSIS Finance', @subsystem_name = N'SSIS';

#3. Who can use the proxy

sysadmins can use any proxy. Anyone else who owns or edits the job must be granted it:

EXEC msdb.dbo.sp_grant_login_to_proxy @proxy_name = N'SSIS Finance', @login_name = N'CONTOSO\grp-etl-developers';

#4. Package access in SSISDB

The job owner (not the proxy) needs permission to execute the package in the catalog. Grant the owner, or the group, READ and EXECUTE on the SSISDB folder or project. The proxy account needs access to whatever the package touches at runtime.

#5. The job step

In SSMS: New Step → Type: SQL Server Integration Services Package → Run as: SSIS Finance → Package source: SSIS Catalog → server, then pick the package. On the Configuration tab, choose the environment reference and set parameters.

Or in T-SQL (the command string is what SSMS generates; script a job from SSMS to get the exact format for your version):

EXEC msdb.dbo.sp_add_jobstep @job_name = N'ETL - Finance Daily', @step_name = N'Run LoadGL',
     @subsystem = N'SSIS', @proxy_name = N'SSIS Finance',
     @command = N'/ISSERVER "\"\SSISDB\Finance\GL\LoadGL.dtsx\"" /SERVER "\".\"" /ENVREFERENCE 3 /Par "\"$ServerOption::SYNCHRONIZED(Boolean)\"";True /CALLERINFO SQLAGENT /REPORTING E';

SYNCHRONIZED=True makes the step wait for the package to finish, so the step fails if the package fails.

#Troubleshooting

Details are in SSISDB.catalog.event_messages for the execution ID shown in job history.

#When the password changes

Update the credential straight away, or every job using the proxy fails:

ALTER CREDENTIAL cred_ssis_finance WITH IDENTITY = N'CONTOSO\svc-ssis-finance', SECRET = N'<new password>';

Tip

Use one proxy per business area or data domain, not per package. A proxy per package becomes unmanageable; one shared proxy becomes a super-user.

#Checklist

• Dedicated domain account with batch logon right
• Credential and proxy, SSIS subsystem granted
• Job owner can use the proxy and execute the package
• Step uses SYNCHRONIZED and the right environment
• Password rotation includes ALTER CREDENTIAL