By: Arshad Ali | 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
