June 22, 2012 at 8:28 am
Hi all,
Got a bit of a problem regarding a security issue when using CmdExec.
As a bit of a background, here's my situation.
I have a script, written in VB.NET, and posted on a remote machine, that, when executed, will perform some data manipulation and output an Excel file. If I execute this script manually, it runs fine.
What I need to do, is execute this script through SQL Server. The method that I've found which does this best for my purposes is to have a job which is of type CmdExec that runs the following script:
schtasks /run /tn "[Name]" /s [Server]
What this does is it executes a scheduled task located on the server, which in turn launches the script. The reason for this extreme complexity is that the script uses the Excel Interop library, and if I launched it directly through the server, it would fail, since Excel is not installed on the server.
Okay, so all that said, here's my problem. I'm getting an Access Denied error when I run the job on SQL Server. Now, this job worked perfectly fine when the script was located on another PC, so I'm fairly sure it has to do with some folder not having the right access. But I can't figure out which one. So I'm hoping someone can help me figure out where the access failure is coming from. I looked in the Event Viewer on the machine, and couldn't find any info. The only lines there indicate that the SQL Server account connected to the machine successfully.
June 22, 2012 at 8:34 am
Okay, got a bit more info now ... Pretty sure the problem is that the SQL Server account doesn't have permissions to run the scheduled task itself, not the batch file ... so I'll keep looking 😛 Sometimes just posting the question helps me figure out the right way to go!
June 22, 2012 at 8:51 am
One thing I would do to test that nothing is going dodgy anywhere else is
Create a credential for your account, use that credential in a proxy under the CmdExec folder, then assign the proxy to the job. If the job runs you know its permissions issue, if not then something else somewhere is causing the issue or you also dont have the nessesary rights to run the scheduled task controller on the remote machine.
June 22, 2012 at 9:18 am
Figured it out - As you were suggesting, I had already created the credential and was using the proxy. The problem was that the proxy account was not a member of the administrators group. Added it, and now it works perfectly.
Thanks for the help!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply