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.
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.
