March 9, 2010 at 10:46 am
Hi All,
Thanks for reading this post !
I need some assistance regarding the following:
SSIS package description: runs/performs all operations within BIDS. It uploads data to SQL from text,excel sources and performs all kinds of transformations. It also calls Stored Procedures. It has send mail task using SMTP as Event handler which uses an text (log) file as an attachment in case of error or task failed scenario to notify the admin.
Protection level- encrypt sensitive with user key
owner - domain account with sysadmin/local admin permissions
Problem: When scheduled as a SQL agent job, the package runs fine if there isn't any error. But, when there is an error and it tries to send e-mail with an attachment - it fails to do so.
Error: either the file(log) does not exist or you do not have permission to access it !!
SQL AGENT: Running under domain account with sysadmin/local admin permissions(different than owner)
I suspect the error is with user key protection level.
Can anyone throw some light on this and help me resolve this issue?
I'll appreciate your replies.
Thanks 🙂
________________________________________________________________
"The greatest ignorance is being proud of your learning"
March 9, 2010 at 12:31 pm
Where is the log file located?
CEWII
March 9, 2010 at 12:50 pm
Elliott W (3/9/2010)
Where is the log file located?CEWII
It's located on the same server. "C:\App\ErrorLogs\"
Also,
SQL Agent Services Account has local admin rights on the server. Same permissions as the owner of the SSIS Package.
Thanks !
________________________________________________________________
"The greatest ignorance is being proud of your learning"
March 9, 2010 at 1:43 pm
I think it would be helpful to include the output from the SSIS output window, I'm wondering if there is some other text that might provide some additional insight.
Also, what is the process that creates this log file. Please enlighten me..
CEWII
March 9, 2010 at 3:06 pm
Elliott W (3/9/2010)
I think it would be helpful to include the output from the SSIS output window, I'm wondering if there is some other text that might provide some additional insight.Also, what is the process that creates this log file. Please enlighten me..
CEWII
Thanks !! Elliott W
Error: Either the file"C:\App\ErrorLog.txt" does not exists or you do not have permisssions to access the file
Error: there were erroes during task validation
I ran this process from Dtexecui. The package runs fine from Visual Studio.
The file is an existing file which is used by SSIS text log provider.
Please ask if additional info is required.
I am grateful to you.
________________________________________________________________
"The greatest ignorance is being proud of your learning"
March 9, 2010 at 7:51 pm
As you may have noticed, I'm in the same boat. In the past, setting up a proxy to point to valid credential, then setting the job up to use that proxy has worked like a charm for me, not this time. I'll tell ya, getting packages to execute as a job is regular voo doo. It needs to be simpler. There needs to be an easier way to make this happen. A quick search of the web will reveal that this is clearly too hard. This is where everybody gets stuck. I'm just about ready to give up on SSIS for just this reason.
Good luck! If I come up with something, I'll be sure to share it with you!
.
March 9, 2010 at 8:10 pm
Elliot W bailed me out. I don't know if your problem is security related, but this saved me, and I get it now:
If a package is "encrypted with user key". When run as a job, it must be executed with the same account that encrypted it, or it can't be decrypted. SA does no good.
Hope this helps someone.
I think a good argument can be made that admins should be able to decrypt ANYBODY's package. I think that would make packages much easier for us ordinary folks to deal with!
.
March 9, 2010 at 8:15 pm
I don't know if I agree about admins decrypting anybodies.
I think the bigger issue is that package protection level is not obvious and the error messages don't clearly point to it. For those of us who have dealt with it in the past it is obvious as soon as I read the error, which coincidently is why I'll often ask for the actual error.. As far as this gents problem.. I'm still stewing on it..
CEWII
March 9, 2010 at 8:28 pm
I'm just trying to get everything into one place.
It runs ok from BIDS (running as you)
It fails in DTExecUI (running as you)
It fails as a SQL Job (running as a domain user in the local admins group)
The log file is created by the SSIS text log provider during the current run?
Ok, then I have some more questions..
Have you verified that the file exists when it tries to send it?
Have you verified the security of that file, even though the user is running as a local admin it is still possible to have permissions issues, best to verify.
Are there any expressions that are tied to the attachment setup on the SMTP component.
Are my observations above correct?
CEWII
March 9, 2010 at 9:45 pm
Elliott W (3/9/2010)
I'm just trying to get everything into one place.It runs ok from BIDS (running as you)
It fails in DTExecUI (running as you)
It fails as a SQL Job (running as a domain user in the local admins group)
The log file is created by the SSIS text log provider during the current run?
Ok, then I have some more questions..
Have you verified that the file exists when it tries to send it?
Have you verified the security of that file, even though the user is running as a local admin it is still possible to have permissions issues, best to verify.
Are there any expressions that are tied to the attachment setup on the SMTP component.
Are my observations above correct?
CEWII
Yes, Elliott
Your observations are correct. I'll throw in some more details.
The log file is not created in the current session. It is an existing file to which error log would be written in case of Error or failure.
Yes, file exists during the time of execution.
Yes, I have made sure that folder in which the file is sitting is accessible by the local admins group.
No, there are no expressions being used
Is there a chance that package protection level could be the issue? decryption of user key? as BSavoie mentioned in the above posts.
Thanks !! for your time Elliott & BSavoie
________________________________________________________________
"The greatest ignorance is being proud of your learning"
March 10, 2010 at 10:54 am
BSavoie (3/9/2010)
Elliot W bailed me out. I don't know if your problem is security related, but this saved me, and I get it now:If a package is "encrypted with user key". When run as a job, it must be executed with the same account that encrypted it, or it can't be decrypted. SA does no good.
Hope this helps someone.
I think a good argument can be made that admins should be able to decrypt ANYBODY's package. I think that would make packages much easier for us ordinary folks to deal with!
UPDATE:
I also tried using proxy account to run the job which uses the package owners credentials but again no luck.
Here's the error from Job History:
Executed as user: Domain\myuser. Microsoft (R) SQL Server Execute Package Utility Version 10.0.2531.0 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 12:24:22 PM Error: 2010-03-10 12:24:22.94 Code: 0xC00291CB Source: Send Mail Task 1 Send Mail Task Description: Either the file "C:\EmailLogTest.txt" does not exist or you do not have permissions to access the file. End Error Error: 2010-03-10 12:24:22.94 Code: 0xC0024107 Source: Send Mail Task 1 Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 12:24:22 PM Finished: 12:24:22 PM Elapsed: 0.578 seconds. The package execution failed. The step failed.
Hope I find the fix before it bambooooozle me. 😉
Thanks.
________________________________________________________________
"The greatest ignorance is being proud of your learning"
March 11, 2010 at 8:31 pm
Sorry, I was distracted by work the last couple days. I hope you solved your problem by now and you already know this:
Just to be clear, it's not the package owner that matters though. It's the package creator, or maybe author is a better word. If you are using "encrypt... with user key", then the last person who saved that package will be the only one that can open that package since it was encrypted with a key specific to that user. So the proxy must reference the credential of the user that saved that package. is absolutely right though, forget all that nonsense. get the connection strings from configuration settings that are persisted, then turn off that encryption nonsense. That's the sum of my light bulb moment! 🙂
.
March 11, 2010 at 8:39 pm
Actually, this error does not look like the dreaded unattended execution problem. This error looks like it's specific to the send mail task. Maybe you could try getting just the email task to work in a job. It might make tracking your problem down a little easier.
.
March 11, 2010 at 9:04 pm
BSavoie (3/11/2010)
Actually, this error does not look like the dreaded unattended execution problem. This error looks like it's specific to the send mail task. Maybe you could try getting just the email task to work in a job. It might make tracking your problem down a little easier.
Well, thanks for your time. BSavoie
I modified the package for sake of narrowing my scope in hunting the problem.
This time I'm using SendMail task in Control Flow(only task in the package).
And, the owner, the author, the creator, and the 'saver' - all are the same - that's me. 🙂
SQL Agent account is different but same permissions/role.
Again, Proxy is pointing to my credentials
I wonder if using encrypt with password or do not save sensitive as a protection level will work !!
Regards -:-)
________________________________________________________________
"The greatest ignorance is being proud of your learning"
March 11, 2010 at 9:27 pm
Well I think you just hit the nail on the head. "SQL Agent account is different". Permissions have absolutely nothing to do with it. That's what kept tripping me up as well. Permissions are everything in SQL Server, NOT for this though!
SQL Agent cannot decrypt the package saved by you. SQL agent runs under another user and consequently has it's own encryption key. You must either find a way to get SQL Agent to run the package using YOUR credentials, OR you must figure out how to get the package saved using the encryption key of the account SQL Agent is using.
So, here's what I suggest: make sure the "run as" for the job is pointing to a proxy, then make sure that proxy is pointing to the credential that your SQLAgent runs under.
Make sense? I know this is really frustrating stuff. It's by far the number one problem you see people posting about in the SSIS forums!
.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply