April 17, 2013 at 7:17 am
I'm working on migrating an existing Server 2003 / SQL 2005 system to Server 2008 R2 / SQL 2008 R2, and am having some problems getting a proxy account to work. The account is needed to execute some OS operations, NOT to affect DBs on the server. Things like unzipping files that are recieved to a particular folder and deleting the files when done.
The SQL Agent is running using a domain account for this purpose. There's also a domain-level account for the proxy account.
I've added the Proxy account to the SQL Logins, created a Credential for it, then created the Proxy in Agent. I've granted the appropriate users access to the Proxy (the "Principals" section), and assigned the proxy to execute a step in a test job (dir e:\)
Now, the problem.
It fails.
If I run the step using the Agent account, it works. If I set the Agent to use the "Network Service" account and the proxy, it works. If I set the Agent to use the domain account and the job to use the proxy account, it fails with:
"Executed as user: domain\svcaccount. The process could not be created for step 1 of job 0x2E0030DE6B7C444E8C0E4759A405B8E5 (reason: A required privilege is not held by the client). The step failed"
I have verified the proxy account does have access to the E:\ drive by running a command prompt as the account. I also "cloned" the local group membership for the proxy from the Server 2003 system, so it belongs to the local Admins account.
I've looked through the Windows Security log, and it shows it to be logging in OK. I see a logon event with a subject account name of the Agent service account, and a new logon security ID of the Proxy account. So it seems the impersonation is working...
Any thoughts?
April 17, 2013 at 9:58 am
Further note on this:
I tried logging into the server as the Proxy account, and was able to log in with no issues, copy a file, etc.
So the account does have the rights.
It seems that the problem is after the Agent account impersonates that things go south...
I'm starting to think about removing SQL and reloading, as the only thing that *MIGHT* be a factor is I didn't have the domain Agent service account during the initial setup. But I did switch the service account (with SQL Config Manager) once I got it.
Help, please?
Thanks,
Jason
April 17, 2013 at 12:52 pm
I may have found the solution to this...
It seems the SQL Agent Service Account may need the "Replace a Process Level Token" right. Seeing as the domain is locked down, I've requested they "clone" the GPOs that are applying to the existing (working) system to the new system. Failing that, I'll ask them to configure a GPO to do this.
Further updates (and hoepfully the solution) as the situation warrants.
😉
Jason
April 22, 2013 at 3:30 am
This link may give some useful details about proxy accounts, but it does assume that the SQL Agent service account is already set up with the required rights... http://sqlserverfinebuild.codeplex.com/wikipage?title=SQL%20Server%20Proxy%20Accounts
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
October 18, 2013 at 2:37 am
jasona.work (4/17/2013)
I may have found the solution to this...It seems the SQL Agent Service Account may need the "Replace a Process Level Token" right. Seeing as the domain is locked down, I've requested they "clone" the GPOs that are applying to the existing (working) system to the new system. Failing that, I'll ask them to configure a GPO to do this.
Further updates (and hoepfully the solution) as the situation warrants.
😉
Jason
Thanks a lot for sharing!!! It lead me to the problem - we have a default server GPO that restricts that user rights assignment to LOCAL SERVICE and NETWORK SERVICE - once overrode that everything worked as expected. Thanks again!
September 21, 2016 at 11:17 am
Thanks as it worked like a charm
Rudy
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply