Running a SSIS Package from SQL Server Agent Using a Proxy Account

By: Arshad Ali   |   Updated: 2010-11-11   |   Comments (27)   |   Related: > SQL Server Agent

Problem

Recently when I created a SQL Server Agent job to execute a SSIS package, it failed with the message "Non-SysAdmins have been denied permission to run DTS Execution job steps without a proxy account.". What is this exception about? What causes it and how do I resolve it?

Solution

When we try to execute a SSIS package from a SQL Server Agent job it fails with the message "Non-SysAdmins have been denied permission to run DTS Execution job steps without a proxy account." if the account under which SQL Server Agent Service is running and the job owner is not a sysadmin on the box or the job step is not set to run under a proxy account associated with the SSIS subsystem.

Below is what this error looks like in SSMS.

For such a scenario, SQL Server Agent allows creating a proxy account which defines the security context for the job step. This means each job step of the job can be run under a different security context using different proxies. SQL Server Agent impersonates the credentials (Windows User accounts) associated with the proxy when the job step is executed if the job step is set to run under that proxy. You can create a proxy and grant access to as many of the available subsystems as needed. Also a single proxy can be used by more than one job step to run under it if all of them require the same level of permissions.

Example

In this example I am going to create a job with a single job step which will execute a SSIS package using a proxy account (since the job owner is not a sysadmin). This might be very helpful if the job step needs to have additional permissions than available to the account under which SQL Server Agent service is running (by default a job step runs under the security context of the account under which the SQL Server Agent service is running).

Create Credential

The first thing that we need to do is to create a credential to be used by the proxy account. Script #1 demonstrates how to create a credential with the CREATE CREDENTIAL command. For this, you specify the name of the credential, account name which will be used to connect outside of SQL Server etc. For more about its syntax refer here. Note that the T-SQL command is not the only way to create credentials, you can create them using SSMS as shown in the image below.

--Script #1 - Creating a credential to be used by proxy
USE MASTER
GO 
--Drop the credential if it is already existing 
IF EXISTS (SELECT 1 FROM sys.credentials WHERE name = N'SSISProxyCredentials') 
BEGIN 
DROP CREDENTIAL [SSISProxyCredentials] 
END 
GO 
CREATE CREDENTIAL [SSISProxyCredentials] 
WITH IDENTITY = N'ARSHADALI-LAP\SSISProxyDemoUser', 
SECRET = N'abcd@0987' 
GO 

Create Proxy

Next you need to create a proxy account, please note that there is no DDL command available as of now for creating a proxy account, but rather in the msdb database you use the sp_add_proxy system stored procedure. You can enable or disable the proxy account using sp_update_proxy system stored procedure and use sp_delete_proxy to delete a proxy account as demonstrated below in Script #2. You can even use SSMS to create a proxy as shown in the next image.

--Script #2 - Creating a proxy account 
USE msdb
GO 
--Drop the proxy if it is already existing 
IF EXISTS (SELECT 1 FROM msdb.dbo.sysproxies WHERE name = N'SSISProxyDemo') 
BEGIN 
EXEC dbo.sp_delete_proxy 
@proxy_name = N'SSISProxyDemo' 
END 
GO 
--Create a proxy and use the same credential as created above 
EXEC msdb.dbo.sp_add_proxy 
@proxy_name = N'SSISProxyDemo', 
@credential_name=N'SSISProxyCredentials', 
@enabled=1 
GO 
--To enable or disable you can use this command 
EXEC msdb.dbo.sp_update_proxy 
@proxy_name = N'SSISProxyDemo', 
@enabled = 1 --@enabled = 0 
GO 

Associate Proxy with Subsystem

Once a proxy account is created, you need to associate it with the SQL Server Agent subsystems using sp_grant_proxy_to_subsystem system stored procedure. A single proxy account can be granted access to one or all of the available subsystems. In this example, I want my created proxy to have access to the SSIS subsystem and hence I am associated it with subsytem_id 11 (SSIS).

--Script #3 - Granting proxy account to SQL Server Agent Sub-systems 
USE msdb
GO 
--You can view all the sub systems of SQL Server Agent with this command
--You can notice for SSIS Subsystem id is 11 
EXEC sp_enum_sqlagent_subsystems 
GO

--Grant created proxy to SQL Agent subsystem 
--You can grant created proxy to as many as available subsystems 
EXEC msdb.dbo.sp_grant_proxy_to_subsystem 
@proxy_name=N'SSISProxyDemo', 
@subsystem_id=11 --subsystem 11 is for SSIS as you can see in the above image 
GO 
--View all the proxies granted to all the subsystems 
EXEC dbo.sp_enum_proxy_for_subsystem 

Grant Permissions to Use Proxy

Next you need to grant permissions to logins/roles who will be using the created proxy account using sp_grant_login_to_proxy system stored procedure. You can use sp_enum_login_for_proxy system stored procedure to view all the logins/roles who have access to the proxies.

--Script #4 - Granting proxy access to security principals 
USE msdb
GO 
--Grant proxy account access to security principals that could be
--either login name or fixed server role or msdb role
--Please note, Members of sysadmin server role are allowed to use any proxy 
EXEC msdb.dbo.sp_grant_login_to_proxy 
@proxy_name=N'SSISProxyDemo' 
,@login_name=N'ARSHADALI-LAP\SSISProxyDemoUser' 
--,@fixed_server_role=N'' 
--,@msdb_role=N'' 
GO 
--View logins provided access to proxies 
EXEC dbo.sp_enum_login_for_proxy 
GO 

Specify Proxy to Use for Job Step

Finally you need to specify the proxy account to use for the job step as shown/highlighted below. Then when SQL Server Agent executes the job step it impersonates the credentials of the proxy instead of the SQL Server Agent service account.

--Script #5 - Granting proxy account to SQL Server Agent Sub-systems 
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'SSISPackageCall', 
@step_id=1, 
@cmdexec_success_code=0, 
@on_success_action=1, 
@on_success_step_id=0, 
@on_fail_action=2, 
@on_fail_step_id=0, 
@retry_attempts=0, 
@retry_interval=0, 
@os_run_priority=0, @subsystem=N'SSIS', 
@command=N'/FILE "C:\Package.dtsx" /CHECKPOINTING OFF /REPORTING E', 
@database_name=N'master', 
@flags=0, 
@proxy_name = N'SSISProxyDemo'; 

Alternatively, you can change the same job step using SSMS to use the proxy as shown below. As I said before, by default a job step runs under the security context of the SQL Server Agent Service account, but the moment you start creating proxies and associating it with SSIS subsystem it will appear under the Run As combo box for a job step as shown below. Select the appropriate proxy you want your job step to use.

Notes

• SQL Server Agent impersonates the credentials associated with the proxy to run the job step if it has been defined to use the proxy instead of using the default security context of SQL Server Agent Service account.
• When you create a job step to execute T-SQL, you cannot define a proxy because T-SQL does not use proxies but rather it runs under the security context of the job owner. If you want to change the security context for Transact-SQL job step, you can use the database_user_name parameter of sp_add_jobstep system stored procedure.
• You need to be sysadmin to create, modify or delete proxy accounts. However to use it, you need to be sysadmin or a member of either of these SQL Server Agent fixed database roles: SQLAgentUserRole, SQLAgentReaderRole, or SQLAgentOperatorRole.

Next Steps

• Review SQL Server Integration Services Package Scheduling with SQL Server Agent
• Review SQL Server Agent Proxies
• Review Creating a SQL Server proxy account to run xp_cmdshell

About the author

Arshad Ali is a SQL and BI Developer focusing on Data Warehousing projects for Microsoft.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips