SQL Agent proxy account permissions in SQL 2005

Question

SQL Agent proxy account permissions in SQL 2005

larry_a_wilson

SSC Veteran

Points: 265
More actions
January 24, 2008 at 1:03 pm

#185851

I am trying to set up a SQL Agent job in 2005 with a CmdExec job step. I have created a domain account under which to run the job step. I have created a credential for that account and a proxy for the credential. When I select the proxy for the jobstep and execute the job, it fails. I get the following error:
Executed as user: BAMDEV\SA_HCO231ABCD01. The process could not be created for step 1 of job 0x7372BB18EDB9764DABD67C1D0F245D16 (reason: A required privilege is not held by the client). The step failed.
I have granted Read/Execute permission on the exe file to this user and have also given it the following permissions:
Log on as a service
Log on as a batch job
Replace a process-level token
Bypass traverse checking
Adjust memory quotas for a process
None of this has helped. If I run the job step as the SQL Agent Service Account, it runs successfully.
Does anyone have any suggestions on what to try or to look for?
Thanks,
Larry

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

KenpoDBA SSCrazy Eights Points: 9811 More actions · Answer 1

It has to have a minimum of DTSOperator rights in msdb. And of course, whatever rights it needs to do what you're asking it to do. But DTSOperator is what it needs to run as a proxy.

Hope this helps.

Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARant

Minion Maintenance is FREE:

larry_a_wilson SSC Veteran Points: 265 More actions · Answer 2

I tried that and it still does not work. However, I am not running an SSIS package (which the db_dts... roles in msdb are generally for) in the job step. I am running a CmdExec step, trying to execute a C# program.

KenpoDBA SSCrazy Eights Points: 9811 More actions · Answer 3

Did you give that acct specific rights to that type of action?

Check under sql server agent\proxies...

Maybe you're missing an OS perm... have you run this same thing from the OS as that particular user to see if it will go?

Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARant

Minion Maintenance is FREE:

larry_a_wilson SSC Veteran Points: 265 More actions · Answer 4

Yes, I have been able to run the same program from the command prompt logged in as the same user.

Chris-215489 SSC Enthusiast Points: 134 More actions · Answer 5

This should get you working. This is in BOL

--xp_cmdshell Proxy Account

--When it is called by a user that is not a member of the sysadmin fixed server role, xp_cmdshell connects to --Windows by using the account name and password stored in the credential named --##xp_cmdshell_proxy_account##. If this proxy credential does not exist, xp_cmdshell will fail.

--The proxy account credential can be created by executing sp_xp_cmdshell_proxy_account. As arguments, this --stored procedure takes a Windows user name and password. For example, the following command creates a proxy --credential for Windows domain user SHIPPING\KobeR that has the Windows password sdfh%dkc93vcMt0.

-- Use this only in SQL Server 2005

sp_xp_cmdshell_proxy_account [ NULL | { 'account_name' , 'password' } ]

EdVassie SSC Guru Points: 60472 More actions · Answer 6

Larry,

The FineBuild Reference manual I put on Codeplex has some useful advice on creating SQL Agent and xp_cmdshell proxy accounts, and the authorities needed by the SQL service accounts, that may be of help to you.

Regarding Windows permissions, you only need 'Log on as a batch job'. Anything else just gives elevated privileges tha are not needed.

My experience is that a missing file permission is the normal cause of this type of problem. You certainly need to try running exactly the same script interactively that you have in the SQL Agent job, while logged on using your proxy account.

One debugging tip when logged on with the proxy account is to open a Command window, navigate to each folder your script needs to use and run a DIR command. Do not forget program libraries and Temp folders. If this all works, then run the DIR commands in your Agent job. Eventually the missing permission gets found.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara