November 30, 2007 at 9:52 am
I have been trying to set up a proxy account to allow non-sysadmins to run jobs. I set up a simple cmdexec job that merely performs a "Dir". I set up a credential which happens to be the same user as the one that the SQLServerAgent runs under (and the default credential). I also set up a proxy account and linked it to the credential. The job runs fine when run under the default "run as" but refuses to run under the proxy.
The Job xxx (reason: A required privilege is not held by the client). The step failed.
error is dsplayed under job history. There are no error in the event viewer. Since the proxy credential account is the same as the default, I can't figure out why I can't get the proxy account to work. I am a sysadmin so I have a choice between the default "proxy" and the one that I created manually.
Any insight?
November 30, 2007 at 2:52 pm
Jim,
Had the same problem this is what I did and you need to check:
1. Proxy Users is shown Under SQL Server Agent/Proxies/Operating System
2. Under General properties of that agent make sure Operating system is checked
3. Under Principals properities add the sql login that HAS the rights to run the process if the xp_cmdshell was done in a studio management
4. Make sure under references that the job name is ran here (if you have this I don't)
Now what I did to check after this is you have to go back into permissions
1. Go to the server where this proxy windows user is and check to see if they are in the local groups (administrtors, etc)
2. Check the permissions on the directory to make sure this proxy user and/or its group has full control on the directory
Another good test (we are a small company so we can do this) add the proxy user to the Domain admin accounts and then run the job if it works if it does then permissions on the file server is the problem.
JTS
November 30, 2007 at 3:13 pm
Thanks for the post
Unfortuneately I've tried all of your suggestions except adding the user to the domain admin group. I did the 2nd best thing and added it to the local admin group. Nothing works.
I am a sysadmin and when I edit the job step, I have a choice of proxies (the original and the one I created manually). I can't link my login to the proxy as a principal because I am a sysadmin and they have it as default. The link just doesn't do anything. The default proxy works create for me being a sysadmin. But the manually created proxy won't work for anyone. I even performed a trace on the job and all of the proxy permission checks passed with a retun code of 0. I can't help but think it is an OS permission (policy, user right) that is missing. The weird thing is that both credentials (the default: installed with SQL Server using the SQL Server Agent login ID and the credential that I created are identical. If the default proxy works, I can't understand why the new one doesn't.
Also, the job history log looks great as the step is being executed by the credential I defined.
I'm stumped.
December 10, 2007 at 2:21 pm
Jim,
Check one more place...in Studio manager Security/Credentials
Do you see your Proxy user underneath this folder?
December 10, 2007 at 2:41 pm
I ended up opening a support issue with Microsoft last week, and we went through everything. The configuration was correct. It finally turned out to be a local policy rights assignment that was missing. "Replace a Process Level Token" was not assigned to the SQL Server agent service account. They were not able to explain though why the initial "default" proxy worked fine with the same credential. Our network folks lock down our server policies very tightly and a series of forms need to be filled out to open up any rights assignments. Apparently the SQL Server install was unable to make the rights assignment but returned no errors or warnings. Probably if I scanned through the installation log, I might find a reference to the failure. Microsoft said it should have been assigned at installation time. They again were unable to explain the lack of an installation error/warning.
Lesson learned and thanks for the assistance.
December 11, 2007 at 5:55 am
I have attached an extract of an install guide I am working on. This covers the Windows authorities needed by SQL Server, and the process of creating proxy accounts.
I hope you find it useful.
16 March 2009 - I have updated the attachment to show the process for SQL Server 2008.
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
February 3, 2010 at 9:34 am
I restarted SQL Server Service and resolved issue. I think there are some permission changes require that service to be restarted.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply