Windows – exe hangs when scheduled from SQL Agent, but fine when run by user

sql serversql-server-2008-r2windowswindows-server-2003

I have a SQL Agent job on a clustered SQL 2008R2 server on Windows 2003 Enterprise. It's an Operating System (CmdExec) step, running an executable. When it runs on schedule, the process does start up, and the job shows as running. However, it never completes the job.

When i run the executable interactively, i.e. I double click it, it runs and it completes its processing in 10 mins or so as expected.

I've monitored the exe with procmon when it hangs, and it logs no errors, it just stops processing (but the exe is still running)

I'm 90% sure this is something to do with the user account running SQL agent, and the local security policy. The account has all the privileges I think it needs – log on as service, log on as batch, etc. I think over the testing period I've pretty much assigned it every right in the policy.

Any ideas why an exe would run fine interactively but fail with SQL agent?

Best Answer

You need to verify that you can run the command as the user SQL Server Agent runs as:

RUNAS /user:SqlAgentUser <command>