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

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

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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Arshad Ali 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


Article Last Updated: 2010-11-11

Comments For This Article




Thursday, August 3, 2023 - 9:20:39 PM - Nick Ryan Back To Top (91463)
Thank you for a very clear explanation of why I might use a proxy account and how to configure it. I do have one additional question. If the job step running under the proxy is an SSIS package, which account will determine the permissions of that package to write files to disk, spill to temp buffer storage, etc.? I've found contradictory articles on this matter in a search. ChatGPT told me definitively that it is the Agent account but I don't always trust the answers from that as it is getting the information from the same kind of search that I've made.

Monday, October 17, 2022 - 12:39:23 AM - Sunil Rohilla Back To Top (90607)
Awesome!!! It is working very much. Thanks

Thursday, October 8, 2020 - 10:17:15 AM - Abhijith Back To Top (86623)
EXEC msdb.dbo.sp_add_proxy
@proxy_name = N'SSISProxyDemo',
@credential_name=N'SSISProxyCredentials',
@enabled=1
GO

When i executed the above SP i am getting this error Msg 14529, Level 16, State 1, Procedure msdb.dbo.sp_add_proxy, Line 45 [Batch Start Line 24]
Proxy "XXXX\MSSQLSERVER2020" is not a valid Windows user.

How to solve this?

Thursday, December 5, 2019 - 4:59:15 AM - shital Back To Top (83293)

i have one mailer sp but it fails every time .it sends mail to people betwn 10 to 20 mens some time 10 some time 14 inthis way and i got the error msg as QL Server Execute Package Utility  Version 11.0.6020.0 for 64-bit  Copyright (C) Microsoft Corporation. All rights reserved.    Started:  9:00:01 AM  Package execution on IS Server failed. Execution ID: 236794, Execution Status:4.  To view the details for the execution, right-click on the Integration Services Catalog, and open the [All Executions] report  Started:  9:00:01 AM  Finished: 9:06:07 AM  Elapsed:  365.891 seconds.  The package execution failed.  The step failed.

how can i resolve this error


Friday, February 15, 2019 - 6:00:13 PM - Eunice Harris Back To Top (79046)

 This worked just fine, thank you for the information!


Wednesday, March 29, 2017 - 5:59:58 AM - P a s c a L Back To Top (53860)

Hello,


Many thanks for the 'WKI'

I did follow the instructions to setup and configure a SSIS Job running under a Proxy account.

The SSIS Step has failed due to Execute permission Denied on several SSIS Stored Proc (sp_ssis_x) e.g. "The EXECUTE permission was denied on the object 'sp_ssis_addlogentry', database 'AMCSCube', schema 'dbo'.".

The SQL Server version is SQL 2012 SP3

I would greatly appreciate your help if you could provide some advise about the root cause of that problem.

I thank you in advance.


Friday, February 24, 2017 - 8:53:16 AM - Makbul Back To Top (46642)

 

hi sir.

 

How to run ssis report manager perticular task using sql server job.

 

Thanks


Friday, January 20, 2017 - 2:37:55 PM - Rajendra Kottakota Back To Top (45448)

Hi Arshad,

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

 

Regards,

Rajendra Kottakota


Monday, November 7, 2016 - 2:09:53 PM - Anne Back To Top (43714)

I created the credential and proxy account using the same SQL agent sevice account. And Run the job use the proxy account, But I got a message:  cannot open the database... requested by the login.

The user is the same user as the SQl agent service account, it should has sysadmin permission by default as a SQL agent service to the local database, why it lost the access after it is created as a proxy account?

 

Thanks


Tuesday, April 19, 2016 - 8:00:40 PM - Forrest Back To Top (41275)

Thanks for this succinct step by step guide. 

 


Monday, March 28, 2016 - 11:31:00 AM - Kumar Back To Top (41074)

Hi,

When I run script#4, I get below message and not able to get any rows after executing EXEC dbo.sp_enum_login_for_proxy. And my job still fails with 'system error: Logon failure: unknown user name or bad password.'

 'Domain\User' is a member of sysadmin server role and cannot be granted to or revoked from the proxy. Members of sysadmin server role are allowed to use any proxy.

 Please let me know if there's something I'm missing here.

 

Thanks,

Kumar

 


Wednesday, November 4, 2015 - 2:26:11 AM - Jason Back To Top (39016)

Script #5 returns this error:

Msg 137, Level 15, State 2, Line 2

Must declare the scalar variable "@jobId".


Monday, September 22, 2014 - 2:15:01 PM - Moyz Khan Back To Top (34660)

Very useful and helpful atrical for SQL Agent Proxies . In your script 4 , shouldn't it be the user or account of person trying to impersonate the user associated with the Proxy. In the example we are using SSISProxyDemoUser for both creating proxy and who should be allowed to use the proxy . 

 

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


Tuesday, July 22, 2014 - 1:32:22 PM - Bernie Black Back To Top (32829)

Great article.  Concise, to the point, and well written.  Thanks.

 

 


Wednesday, June 11, 2014 - 9:34:28 AM - anurag Back To Top (32192)

bless you dear, saved my day


Thursday, April 10, 2014 - 6:20:41 AM - Lohith KS Chalam Back To Top (30034)

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.

 


Thursday, January 30, 2014 - 8:57:01 AM - Ravi Back To Top (29286)

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, June 27, 2013 - 3:29:22 AM - ehnatone Back To Top (25588)

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

 


Wednesday, May 29, 2013 - 9:38:40 AM - cubed Back To Top (25179)

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, February 28, 2013 - 9:12:09 AM - Alejandro Back To Top (22474)

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

So just what I need, thanks so much,

 

Alex


Tuesday, December 11, 2012 - 3:35:16 AM - askmlx Back To Top (20864)

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?


Friday, October 26, 2012 - 6:30:32 AM - DRS Back To Top (20100)

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


Monday, September 10, 2012 - 5:36:44 AM - arun Back To Top (19446)

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


Wednesday, June 6, 2012 - 12:44:50 PM - KRK Back To Top (17817)

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!


Friday, April 6, 2012 - 4:26:00 AM - Vaibhav Mehta Back To Top (16800)

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

[email protected]


Saturday, April 2, 2011 - 11:42:08 AM - Arshad Back To Top (13426)

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.


Thursday, March 31, 2011 - 10:05:26 AM - anup Back To Top (13397)

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















get free sql tips
agree to terms