SQL Server Agent Job fails to run CmdExec step under proxy account

1
votes

I'm currently trying to run a SQL Server Agent Job that executes a CmdExec job step under a specific domain account. We've set up the job so that, when deployed, it sets up the following:

  1. Create a Credential DOMAIN\CustomUser with the correct password
  2. Create a SQL Server Agent Proxy DOMAIN\CustomUser
  3. Grant access to the Operating System (CmdExec) subsystem for the DOMAIN\CustomUser proxy
  4. Create a SQL Server Agent Job owned by sa
  5. CmdExec Job Step in the above job that runs under the DOMAIN\CustomUser proxy account

The job has been identically deployed (same parameters for all the accounts, passwords, job names, etc.) in two separate environments with different results on three machines.

  1. SQL Server 2008 R2 SP2 + SQL Server Agent Service running as LocalSystem
    • The job runs correctly under this environment for all machines
  2. SQL Server 2012 SP2 + SQL Server Agent Service running as DOMAIN\ServiceAccount. The DOMAIN\ServiceAccount is a sysadmin in SQL Server and is in the Adminstrators group on the machines.
    • The job fails two two different error messages on two computers in this environment

On one machine in Environment #2 we receive the following error:

Executed as user: DOMAIN\CustomUser. The process could not be created for step 1 of job 0x657C6E86850D684D9238816B8548D63B (reason: A required privilege is not held by the client). The step failed.

On a separate machine in Environment #2 we receive the following error:

Unable to start execution of step 1 (reason: Error authenticating proxy DOMAIN\CustomUser, system error: The user name or password is incorrect.). The step failed.

I've tried following the guidance here which suggests you need to use SQL Server Configuration Manager to set the service account. No luck with it though.

Disclaimer: I did not configure the machines in Environment #2 so I have no idea if my predecessors did something different when they configured the machines.

Why would I get three separate results (two failures, one success)? The scripted job is bit-for-bit (short of the Job ID) identical across all our machines but it completely fails to run in Environment #2.

2
sql-serverproxysql-server-2008-r2sql-server-2012sql-server-agent
I've put up a SSSCE gist here: gist.github.com/mbantegui/d561ef7e786a27c189c3. This works for me in Environment #1 and on my development machine (where I use LocalSystem for the agent service) but not on our two other environments. - Mike Bailey

2 Answers

1
votes

Is something somewhere in the job process calling a local variable? If so, this might be a Windows 6.2 'feature' per MS KB2968540. Server 2008 works as designed; Server 2012 forward is broken. The suggested workaround is hit and miss. If this is your issue, running a ProcMon trace should allow you to see what is being run under the incorrect context.

What we had to do in my shop was switch the Agent jobs over to scheduled tasks, and explicitly declare the USERPROFILE and APPDATA variables from within a batch file. It would have been nice to set those in an agent job but agent running a CMDEXEC job seems to handle things differently than CMD.

MS broke lots of backwards compatibility in Server 2012; good luck.

0
votes

Task scheduler can be used to schedule and execute the jobs (run SSIS packages). Save the file in .bat on your desktop and use task scheduler to schedule the job to run automatically:

  1. Create SSIS Package
  2. Save the package on a mapped server (or drive)
  3. Copy the path "D:\ARMSTEE\SSISPACKAGE\GiganticWeeklyUpload_1.dtsx"
  4. Add the path to dtexec /f "D:\ARMSTEE\SSISPACKAGE\GiganticWeeklyUpload_1.dtsx"

It is faster and easier to do. It took me a while to find it and hence wonna share. This can be used when you have permission issues, especially.