x

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?

more ▼

asked Jan 12, 2016 at 05:20 PM in Default

avatar image

Tom Staab ♦
14.5k 7 14 21

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.

Jan 12, 2016 at 06:28 PM Tom Staab ♦

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 ...

Jan 12, 2016 at 07:43 PM Tom Staab ♦

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

Jan 13, 2016 at 03:43 PM Kev Riley ♦♦

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

Jan 13, 2016 at 05:59 PM Tom Staab ♦

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?

Jan 14, 2016 at 05:01 PM srutzky
show all comments (comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.

more ▼

answered Jan 19, 2016 at 04:42 PM

avatar image

Tom Staab ♦
14.5k 7 14 21

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x403
x110
x102
x15

asked: Jan 12, 2016 at 05:20 PM

Seen: 156 times

Last Updated: Dec 11, 2016 at 06:08 AM

Copyright 2017 Redgate Software. Privacy Policy