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
Toggle Comment visibility. Current Visibility: Viewable by all users

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

Kev Riley avatar image Kev Riley ♦♦ commented ·
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 ·
srutzky avatar image srutzky commented ·
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 ·
Tom Staab avatar image Tom Staab ♦ commented ·
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 ·
Tom Staab avatar image Tom Staab ♦ commented ·
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 ·
Tom Staab avatar image Tom Staab ♦ commented ·
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 ·
Show more comments

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
Toggle Comment visibility. Current Visibility: Viewable by all users

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.