SSIS package to run using SQL Proxy account in SQL job

By /

Recently I’ve came across issue on Production, while running SSIS package using SQL agent job it was failed with error “Non-sysadmin have denied permission to run DTS execution job steps without a proxy account”.

On Production environment due to security context its difficult to provide high privileged access and to avoid this issue, we need to create proxy account which will run SQL job.

In such cases SQL Proxy uses Credentials which should be windows user and we can create Proxy and grant access. By default SQL agent run under the account in which SQL server agent service is running.

To overcome this issue we can create SQL proxies and each sql agent jobs steps can run with different security context.

Step 1 : Create SQL Credentials using TSQL

First, you need to create a credential which is an identity that contains the authentication information (like username and password) used by the proxy.

USE MASTER

CREATE CREDENTIAL ProxyCredential 
WITH IDENTITY = 'Domain\User', 
SECRET = 'Password';

Step 2 : Create a Proxy

After creating the credential, you can create a proxy account. The proxy will allow the SQL Server Agent jobs to run under the security context of the credential

USE msdb

EXEC msdb.dbo.sp_add_proxy 
@proxy_name = 'Proxy', 
@credential_name = 'ProxyCredential',
@enabled =  1

Step 3 : Assign a Proxy to the sub-system

Now you need to grant appropriate permissions to the proxy account so that it can access the necessary resources.

Below is the reference for subsystem, you can check this using below query

USE msdb
EXEC sp_enum_sqlagent_subsystems

In our case we want to execute SSIS using SQL Proxy, so we will grant access to subsytem SSIS.

USE msdb

EXEC msdb.dbo.sp_grant_proxy_to_subsystem 
@proxy_name = 'Proxy', 
@subsystem = 'SSIS';

Below query helps to view all the proxies access to subsytems

EXEC dbo.sp_enum_proxy_for_subsystem

Step 4 : Grant permission to use Proxy

We need to provide permissions to logins which will be using the proxy account

USE msdb

EXEC msdb.dbo.sp_grant_login_to_proxy 
@proxy_name=N'PROXY',
@login_name=N'Domain\accountname'

Below query helps to view logins which has access to proxy

EXEC dbo.sp_enum_login_for_proxy

Step 5 : Assign the proxy to a Job Step

Finally, the proxy is set up, you can assign it to a specific SQL Server Agent job step, we can change this in sql agent job step to run under Proxy account.

EXEC msdb.dbo.sp_add_jobstep 
@job_name = 'JobName',
@step_name = 'JobStep',
@subsystem = 'SSIS',
@command = 'EXEC MyStoredProc;',
@proxy_name = 'Proxy';

Scroll to Top