ERROR when Executing a Job within SQL Server Agent from an SSIS package. SQL Server 2012, Visual Studio 2010
-
Hello,
I created an SSIS package within Visual Studio 2010 that takes data from cURL, exports to a XML file then imports into a specified database, and it executes fine.
I then created a job within SQL Server Agent, to attempt to schedule this SSIS package. The job I am testing only contains one step; run the package created within SSIS.
When I "Start Job at Step" the first action of "Start Job [Job Name]" is a success. The second action of "Execute Job [Job Name] errors.
Once I go to the Package within SSMS, right click, Reports\Standard Reports\ All Executions, I see my Job failed. I click on "All Messages" for my Job. And I get the following Error:
Execute Process Task:Error: In Executing "\\CRN-EDW-01\InContact\Config File\1.bat" ""at"", The process exit code was "23" while the expected was "0".For the life of me, I cannot find anything that references this exit code, nor can I figure out what the problem is. I have messed around with user rights within SSMS, as well as sharing the folders that the cURL is trying to access. The thing that really gets me is that the execution of the SSIS Package works fine, yet when it is ran as a job it errors.
Please help
Thanks
-
The exit code comes from something in the .bat file, not SSIS. My guess is you've got a double-hop of some kind going on, messing with the rights to whatever it's doing.
- Craig FarrellNever stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA -
What do you mean by "double-hop"? I apologize, but I am still learning most of this.
I am using the .bat file as a means to pull from cURL. A vendor provided me with the cURL information.
First it accesses the drive where the cURL.exe file is located: \\CRN-EDW-01\InContact\curl-7.28.0\curl
Second it outputs to a certain file: -o \\CRN-EDW-01\InContact\InContact\data\1.xml
Then there is some sort of "k" command that was provided to me by the vendor.
The weird thing is, is that it works fine when executed manually, but as a job it gives the error.
Any ideas?
Thank you!!
-
Apologies, I forget not everyone has beaten their heads on authentication before. Oops.
I have no idea what cURL is or any of that, unfortunately. I couldn't tell you what's going on under the hood. However, the troubleshooting you've done leads me to believe it's the job causing the issue, not the code itself. The most likely problem when you can manually run something, but a job can't, is one of a few issues.
First, a job runs under the authentication of the SQL Agent. That Agent has a login defined in the services on the box it's installed on. You need to find out that user, and confirm that they have equivalent access that you do to be able to run all the necessary pieces. It may not have access to one or more files/directories.
If that's all well and good, then you need to determine if you're dealing with a Kerberos double-hop issue. Google that and you'll find reams of information on the problem, but the root cause is this: If your network isn't setup the way you need, authentication only can visit one server out. Otherwise it needs the equivalent of a repeater to send that security token to the next place. So, if your .bat is then calling for something on another machine, it may (note, may. Talk to your sysadmins) not be able to pass it along to the next one. Usually my office uses wireshark to determine when there's a problem of this nature.
From there, keep examining security possibilities. That is the 99% cause (in some form) when SQL Agent won't work, but you can manually run things.
- Craig FarrellNever stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA -
Thank you. I will look into the SQL Agent access and post any successes.
My .bat file is not attempting to access any networks, it is only accessing the local machine (Though the server is a VM, but I don't believe that has anything to do with this.)
-
Thank you again for your help.
After banging our heads for a couple of hours, we simply added "Everyone" as a Read/ Write user to the drive we were accessing the cURL file from within the Job, and BING, it worked!
RESOLVED!
-
I'd... be careful of considering that resolved. Even anonymous logins can get access with that security setting. However, you've confirmed, decisively, that it's security.
Now you just need to figure out why at some point.
Glad you got it working, however.
- Craig FarrellNever stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA -
Kraig
You are correct that Anonymous logins would be a concern, but this server has tight security for login access.
Thank you
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply