CmdExec Proxy Account for SQL Server Agent doesn't load full Profile / User Registry Hive
I have a SQL Server agent job that has a single CmdExec step that executes a NetBackup program. This job works fine when the account running the job is a member of the sysadmin server role. In this case the command is executed under the context of the domain account to which SSA is configured to run with. Both the database engine and SSA are using the same domain account which is also in the local administrators group in the OS.
If I remove the account running the job from the sysadmin server role, I then need to setup a proxy account. I then create a credential, the proxy account for CmdExec, grant permissions, and set the step to run using that proxy. The account in the credential is in the local administrators group in the OS.
After all this, I find that the program doesn't run successfully. I can see from process monitor that the program is starting up and running under the account, but that it isn't succeeding. After some extensive troubleshooting and reviewing the output of process monitor, I found that if you log on to the server with the account used as the proxy and stay logged in, then the program will run successfully. After some additional troubleshooting, I found that it must be due to the user profile and registry hive not being fully loaded when using the proxy. This is why it works if you login with the account used as the proxy.
This seems like a bug to me, or at the very least, should be a configurable option.
Sarah Catherall commented
Any further word on this? I'm running SSIS 2017, and even when I run the package myself (right click execute) because the package kicks off an Execute Process Task, that task is unable to access the assigned identity's user profile (where we are holding important credentials!). I even created a super simple package to prove this that just executes a .bat file containing the below. When run via Visual Studio the package finds the cmdkey list just fine, when run via SSIS hosting manually it doesn't.
C:\Windows\System32\whoami.exe >> \\myserver\Experiments\whoami.txt
C:\Windows\System32\cmdkey.exe /list >> \\myserver\Experiments\whoami.txt
The output of which I then get is
Currently stored credentials:
* NONE *
Nicolas Penin commented
George Brown commented
Just to add a note here; if you compare the use of proxy with the CmdExec job step against the RUNAS command line utility, you can see that the default behavior of each is different.
RUNAS by default will load the profile of the account specified unless you use the /NOPROFILE swich.
The CmdExec job step with proxy will NOT load the profile of the account specified and there is no option to do so.