Failure of SSIS Package when Executed from SQL Server Agent
-
Good day,
We’ve created a basic SSIS package that connects to an Oracle Database, retrieves information, and dumps the info into a SQL Server Database Table. We’ve tested the package within BIDS, and it works 100%.
We then created a SQL Server Agent job to run this package every 30 minutes. The owner of the SQL Agent Job has sysadmin rights, and for confidentiality purposes is called [MYDOMAIN]\Bob. The first step in the Job was set to type “SQL Server Integration Services Package”. The package source is set to File System.
Initially there were issues with authentication to Oracle, so we created a credential (Using [MYDOMAIN]\Bob), and then assigned a proxy called “SSIS_Runner” to that credential. After this was done we set the first step in the job to run under the “SSIS_Runner” proxy.
The issue comes in when the scheduled job runs. While we are logged in to the server under the Bob’s credentials, the SQL Server Agent Job runs without any issues. The moment we log off the SQL Server Agent job fails to run. The error message we get from SQL Log Viewer is: "Executed as user: [MYDOMAIN]\Bob. The package execution failed. The step Failed."
Due the vagueness of the SQL Agent error message, we've enabled logging on the package. We ran the SQL Agent job again, and the log file says that there were issues validating the steps where SSIS connects to Oracle. My question is why would this SSIS package work when we are logged on to the server, and not run when we are logged off?
We’ve tried to change the owner of the package to the Service Account that runs the SSIS package, and that makes no difference.
Below is an excerpt of the error messages in the log file:
************************************************************
OnError,SERVERNAME,SERVERNAME\Bob,Load WORL Transactions,{37C87481-0A2D-4A36-ADAB-DAA463BA1CE3},{1A2B35D6-5C07-410E-BA77-09AEF9724758},2008/10/28 04:35:18 PM,2008/10/28 04:35:18 PM,-1071611876,0x,The AcquireConnection method call to the connection manager "CR4JINA”" failed with error code 0xC0202009.
OnError,SERVERNAME,SERVERNAME\Bob,Load WORL Transactions,{37C87481-0A2D-4A36-ADAB-DAA463BA1CE3},{1A2B35D6-5C07-410E-BA77-09AEF9724758},2008/10/28 04:35:18 PM,2008/10/28 04:35:18 PM,-1073450985,0x,component "WORL" (1) failed validation and returned error code 0xC020801C.
OnError,SERVERNAME,SERVERNAME\Bob,Load WORL Transactions,{37C87481-0A2D-4A36-ADAB-DAA463BA1CE3},{1A2B35D6-5C07-410E-BA77-09AEF9724758},2008/10/28 04:35:18 PM,2008/10/28 04:35:18 PM,-1073450996,0x,One or more component failed validation.
************************************************************
Your help would be greatly appreciated.
Kind Regards,
Ignacio.
-
Ignacio,
I have seen this before. And this is a tricky one. What is happening is that by default the SQL Job Agent is executing with your proxy account, using light-weight execution without loading the user profile. That's why you see this weird behavior where the execution is working when the user is logged on to the machine. You can verify that I'm correct by doing the following experiment. Run your SSIS package using the "runas" command. There is an option "/noprofile". If you run it with it, you will most probably see the same behavior as what you see now.
Now what you need to do is to find a way and execute your package with complete user profile. If you find information about this, please post it back.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
-
Hi CozyRoc,
Thanks for the reply. Yes, very odd behaviour. I'll look into loading the full user profile, and as soon as I find something I'll post it here.
I'm not working on this particular issue this week, so as soon as I have time I'll test your theory and provide feedback after the test has been completed.
Have a good weekend.
-
Same issue. has anyone found an answer to this yet??
-
Definitely hope someone has come to a conclusion on this issue, a fellow developer has encountered this exact same problem. Looking forward to a resolution. Thanks!
-
Hi Randy,
I've been looking everywhere for a solution to this but have found nothing. I'm wondering if a call should be logged with Microsoft to resolve this issue?
Kind Regards,
Ignacio
-
Ignacio,
My developer has indicated that this issue is very similar, but that he cannot run it locally as indicated above. He'd like to pursue another tangent and didn't want to confuse the issue. Hope someone comes up with something, these issues have to be related. Good luck.
-
Found a solution!! :w00t:
Basic problem I think is that the packages security level is set to EncryptSensitiveWithUserKey which when you are logged off the server the job will not execute as the userkey cannot be fully loaded.
My solution was to change the package protectionlevel to EncryptSensitiveWithPassword, set the password and then set the job owner to my server admin account.
For me this has worked and I hope it works for you guys as well.
Mike
-
Hi, I've been working with Randy On this. The package encryption level was only set to EncryptSensitiveWithUserKey while it was being edited and for testing. I tried a couple of different settings there ( both DontSaveSensitive and EncryptSensitiveWithPassword ).
Originally what appeared to be happening is that the password for a connection string was not being looked up in the ConfigDB by the package. So the package errored saying login for the userid failed.
Then we switched to using EncryptSensitiveWithPassword. And then the errors were related to decryption. I believe thats when I started using a Proxy user.
To setup the proxy we created a local user, added it to the administrators group and also the sqlagentuser group in active directory. Then we created the credential in sqlserver based on that login ID. And that credential was then used by the Proxy account. On the rights to the proxy account I basically ended up giving it ever right I could..
So when all that was done, It went back to failing on the userid login; again suggesting it couldn't lookup the password in the configdb.
So we are kind stuck right at that point.
-
I have seen this issue and the SQL Server Agent user profile is the one that is used in execution of the scheduled jobs. So if user1 creates an SSIS package, you can run it in BIDS and everything looks good. However, if the owner of the scheduled job is user2, then they have to be granted specific rights to run other packages that they do not create.
I have mine set up this way and everything works.
Tim
-
Well even if I run it manually it won't get the config data from the sql configdb.
So possibly there is something wrong other than what I am thinking?
I'm not sure what you mean by the Scheduled Job owner..
from my understanding, the owner of the schedules job is whatever logon id is used as the longon as for the sql server agent. But in this case, it's part of the proxy user.
Using the xml file way of storing the config data worked well, but the dba for the server that the database is on wants it down with the configdb..
-
I found the solution
I experienced the same Issue with my packages, and tried all these solutions posted here, but none of them worked for me.
What I then did:
1. SQL Server Configuration Manager
2. SQL Services
3. Select SQL Server Agent, right click, select properties.
4. Under log on tab, select 'this account' radio button, and select the user who created the package.
Remember the user can be changed in the Solution Explorer in visual studio/SQL Server Data tools of the package in case there is a need to do that. HOW: Right Click the Project, Properties, Common properties, Project, CreatorName, Change it to whatever name you will configure the SQL Server agent to logon using.
Redeploy your package but not necessary. Start job at Step to test execution. Hurrraaaaayyy... Your Job is up and running.
I trust that this helps. Please give me feedback if this has helped you. Thank you.
Bongani
-
skosanabv (11/19/2013)
I found the solutionI experienced the same Issue with my packages, and tried all these solutions posted here, but none of them worked for me.
What I then did:
1. SQL Server Configuration Manager
2. SQL Services
3. Select SQL Server Agent, right click, select properties.
4. Under log on tab, select 'this account' radio button, and select the user who created the package.
Remember the user can be changed in the Solution Explorer in visual studio/SQL Server Data tools of the package in case there is a need to do that. HOW: Right Click the Project, Properties, Common properties, Project, CreatorName, Change it to whatever name you will configure the SQL Server agent to logon using.
Redeploy your package but not necessary. Start job at Step to test execution. Hurrraaaaayyy... Your Job is up and running.
I trust that this helps. Please give me feedback if this has helped you. Thank you.
Bongani
Are you suggesting that the SQL Agent service needs to run with the same account as created a package?
And therefore that every deployed package needs to be created by the same account?
🙂
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply