Execute SSIS package using domain(windows) account through SQL job

Environment:

In my client's production environment, we use service account (Domain based) having read/write/execute access and SQL login for read only (support) access. Since the interactive login is disabled at the group policy, no users can directly connect to SSMS with servicce account.

To execute any jobs to do a push/pull of data, the user needs to execute it from a different/remote server where as to share the load of ETL. 

Problem statement:

In the above setup, user needs to run his job\packages only using the service account (Windows) as it has the read/write/execute access.

How to execute a ssis package job using windows account?

Solution:

Answer is to setup a proxy and provide the credentials in the job step.

1.      Open SQL Server Management Studio (2008 R2) and connect to the SQL server that would run the SSIS package as a SQL Server system admin or local server admin.

2.      Run the following script (provide the actual service account (AD) and its network password that would be used to connect to the database) to create a proxy.

USE msdb

CREATE CREDENTIAL UserSvc WITH IDENTITY = 'XYZ\UserAcc', SECRET = 'Domain pwd of xyz'

GO

Note: SQL Server will store the above-given network password in an encrypted way; no security issues.

EXEC dbo.sp_add_proxy @proxy_name = 'UserProxy',

      @enabled = 1,

      @description = 'Proxy to execute User SSIS as service account',

      @credential_name = 'UserSvc'

GO

EXEC dbo.sp_grant_proxy_to_subsystem

      @proxy_name = 'UserProxy',

    @subsystem_name = N'Dts'

GO

3. Assuming the above script ran without errors, open the SQL Agent job (from SQL Server Management Studio) used to run the SSIS package and edit the SSIS package step. 

Choose "UserProxy" from the Run As drop-down and click OK. See the screenshot below for reference. 

4. Edit  the connection string to include password as below:

5. By setting up above, we can execute a package using domain account.