question

Tom Staab avatar image
Tom Staab asked

SQL Agent CmdExec proxy ignored

I am trying to execute a batch file from a SQL Server Agent job cmdexec step. I created a proxy to execute as my domain account (just testing on my machine for now) and assigned that proxy to the job step. Unfortunately, it seems to (sort of) ignore the proxy. I've reduced my test to 1 simple echo command instead of the batch file while I try to figure out what is going wrong. echo %date% %time% %username% The job runs successfully, and this is the output: Executed as user: DEV\tomstaab. Tue 01/12/2016 12:12:49.64 DEVTOMSTAAB$. Process Exit Code 0. The step succeeded. `DEV\tomstaab` is my domain username.
`DEVTOMSTAAB` is my machine name.
Any idea why it would appear to *start* executing as me but then switch to the machine account?
sql-server-2012sql-agentjobwindows-server-2008-r2
11 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

when I use `whoami` instead of `echo %username%` I get the 'right' result - maybe this is a red herring? In my case SQL agent is running as the virtual account SQLAgent$SQL2012 where SQL2012 is the instance name
2 Likes 2 ·
This might just be an issue of the many nuances of impersonation. I would certainly trust `whoami` over `%username%`. One difference between the "Local System" and "Network Service" accounts is that "Local System" does _not_ have a profile stored in the Registry. And if your co-worker who created the credential on your machine never actually logged into your machine as herself, then I don't think her domain account would have any Registry info yet either. I do know that when impersonating it will not refresh the environment variables or switch the current user in the Registry, and I am guessing that `%username%` changing or not changing. Have your co-worker log into your machine, log out, and then test again with her credential. Also, what local groups is your account in that might be different than the groups that your test local account is a member of?
2 Likes 2 ·
I now believe this sounds like a Windows profile issue as explained in KB2968540, but that says it's only for Windows 2012+. I am running Windows 2008 R2. *sigh* https://support.microsoft.com/en-us/kb/2968540
1 Like 1 ·
UPDATE: It works as intended (using the proxy account) when SQL Agent is configured to run as Local System. It fails (seems to ignore the proxy) when running as Network Service.
0 Likes 0 ·
I just created a new local machine account and reconfigured SQL Agent to run as that account. When I retested my job, it now starts running as me (like before) and then switches to the new account instead of the machine account. Still trying ...
0 Likes 0 ·
I managed to get it working with a different account but still not with my account. Could it be because I am a local machine admin?
0 Likes 0 ·
Not abandoning you, I just don't know.
0 Likes 0 ·
Thanks, Grant. Well, I think I just disproved my earlier theory. I had a coworker create a credential on my machine. The job step then executed correctly as her. I then promoted her to local admin, but then it still worked. Hmm... I know changing Windows groups with a domain login requires logging out/in. What about local machine groups for a domain account just used as a credential? I'll try restarting SQL Agent.
0 Likes 0 ·
I changed the same credential 3 different times using different accounts: my domain account (local admin), a local account (non-admin), and a local account (admin). Both of the local accounts worked correctly, but mine did not. But I know it's not *only* an issue of local vs domain account because my coworker's account worked correctly both as a local admin and not.
0 Likes 0 ·
Just not sure. It's not an area I have knowledge of. I just felt bad reading through all the informational content with no responses. I'll see if I can't drum up business.
0 Likes 0 ·
Thanks, Kev. I hadn't thought to try whoami. That returns the correct username for me as well. Whatever is making it only partially work for my account is causing problems with my Oracle client. We have 1 Agent/SSIS box running 2012 packages through the catalog and 2008 packages via dtexec from a batch file. The Oracle ETLs called from 2008 packages are the ones not working correctly. Since that's the only remaining issue from my new workflow design, we will be deploying to a development integration server soon. I'm hopeful we won't have the same issues there.
0 Likes 0 ·

1 Answer

·
Tom Staab avatar image
Tom Staab answered
After trying many techniques, the final solution was to run SQL Agent as a domain account and have the job step execute the batch file using the same account. That allowed it to properly retrieve the account's profile, and therefore the Oracle client worked correctly. Thank you all for your assistance in figuring this out.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.