Problem
I created a SQL Server Agent job to execute a SSIS package and, it failed. The message was “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. The error message is “Non-SysAdmins have been denied permission to run DTS Execution job steps without a proxy account.” If the account SQL Server Agent Service is using and the job owner is not a sysadmin it will fail. If the job step does not run under a proxy account associated with SSIS subsystem it will not work.
Below is what this error looks like in SSMS.
To fix this, SQL Server Agent allows creating a proxy account which defines the security context for the job step. Each job step can run under a different security context using different proxies.
SQL Server Agent impersonates the credentials (Windows User accounts) associated with the proxy when executing the job step. You can create a proxy and grant access to as many of the available subsystems as needed. Multiple job steps can use a single proxy if all require the same level of permissions.
Example
I am going to create a job with a single step to execute a SSIS package using a proxy account. 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
