Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006
join MSSQLTips for free SQL Server tips







Learn more about SQL Server tools








Learn more about SQL Server tools


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

MSSQLTips author Arshad Ali By:   |   Read Comments (12)   |   Related Tips: More > 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.

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

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 a job with a single job step which will execute a SSIS package using a proxy account

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

You can even use SSMS to create a proxy

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

you need to associate it with the SQL Server Agent subsystems

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

you can change the same job step using SSMS to use the proxy

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


Last Update: 11/11/2010


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

View all my tips
Related Resources


print tip Print  
Become a paid author




Recommended For You








Learn more about SQL Server tools
Comments and Feedback:
Thursday, March 31, 2011 - 10:05:26 AM - anup Read The Tip

HI Arshad sir,

  your solution works perfect in my application thanks for your Awesome Artical

but i have one more issue is my client is not giving Sys admin and Proxy Permission to me to run my ssis packages they are asking to run this packages with the Normal User so  what should i do? please give me some thing solution

please reply

thanks


Saturday, April 02, 2011 - 11:42:08 AM - Arshad Read The Tip

As I understand you need to have either of SYSADMIN or PROXY account to execute your SSIS package from a job. Please note you don't need to be a SYSADMIN in order to execute your SSIS package using PROXY account. Please have a talk with your client and explain the situation.

Hope it helps.


Friday, April 06, 2012 - 4:26:00 AM - Vaibhav Mehta Read The Tip

Hello Arshad -

Your article was really helpful.

Would like to know while creating a credential do we need to specify actual password of account we specify as identity ?

Can we specify Window Group as identity ?

Thanks

Vaibhav

vaibhavmht@gmail.com


Wednesday, June 06, 2012 - 12:44:50 PM - KRK Read The Tip

You article was extermly helpful.

The only part that I did not use or fully understand was the part about "Specify Proxy to Use for Job Step".

Great Job!


Monday, September 10, 2012 - 5:36:44 AM - arun Read The Tip

Hi,

I have done the same as given in above article. but my problem is whenever my wndows loggin is activated in system the job executes. if I log off it failed. please help.

thanks

AS


Friday, October 26, 2012 - 6:30:32 AM - DRS Read The Tip

good post.

The article helped me getting the SSIS package to execute , but it now fails when trying to upload a file via ftp. Any ideas..? I  think it might be security or permission related.. 


Tuesday, December 11, 2012 - 3:35:16 AM - askmlx Read The Tip

YOur Article super................Great to see ...........it helps for new DBA's

 

But I have same problem that is

 

My organization DBA person has left from company. He was created the 5 jobs.I am a new dba i have a sysadmin role but last week we faced the non logged agent shutedown error in one snapshot replication job . so i logged in and reinitilized the subscription in my login have sysadmin role. then runs the job it suceeceds. but my problem is next week DBA created 4 jobs and replication snapshot jobs are throw the error as Non-SysAdmins have been denied permission to run DTS Execution job steps without a proxy account. 

 


Above Error came for following failed job list:

 

Weekly log file shrink

 

Weekly index rebuild

 

Weekly statistics update

 

Weekly log file shrink one db

 


Live machine one db copy to Dev machine

 


Live machine one db snapshot to Dev machine

 


 how to solve and prevent in future?


Thursday, February 28, 2013 - 9:12:09 AM - Alejandro Read The Tip

In my case, I applied the steps of the article and got it working!

So just what I need, thanks so much,

 

Alex


Wednesday, May 29, 2013 - 9:38:40 AM - cubed Read The Tip

Thanks for the article, it was very helpful.

I have the same problem as above in that I want to create a Credential in a script without including the password in plain text. Are there any ways around this?

Thanks

Phil


Thursday, June 27, 2013 - 3:29:22 AM - ehnatone Read The Tip

I am trying to cut off all unneded permissions for my customer, but let them run their jobs.

I am not sure I understood the roles SQLAgentUserRole, SQLAgentReaderRole, or SQLAgentOperatorRole. I read these about on microsoft .com, but still not clear.

Also, in your post, I am unable to find this clearness, since you mention this:

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.

and then, at the end, add this:

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.

So, can we clarify what is actually needed for a login to be able to USE the proxy? Other articles add the proxy accoutn to these SQLAgent Roles which also makes no sense for me!

 

Thank you for your help!

SQL Agent Service accoutn is SYSTEM and is a member of syadmins

Owner of jobs is a simple windows login, having access to the proxy

 


Thursday, January 30, 2014 - 8:57:01 AM - Ravi Read The Tip

Hello Sir,

May be a real stupid question but need help. I have sql server jobs which are calling for the SSIS packages. I have Visual studio installed on my pc. I want to look into the contents of the package, even if its in xml file.

How can I access those packages from management studio or from visual studio? please help.


Thursday, April 10, 2014 - 6:20:41 AM - Lohith KS Chalam Read The Tip

Hi Arshad,

Thank you for the useful article. Just want to why the credential indentity name and the the login name for which proxy was  granted is same.

 



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates

Signup for our newsletter


Comments
*Enter Code refresh code


 

Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.