September 9, 2008 at 12:40 am
Hi all,
I am tring run an SSIS package throught the sql server agent jobs and trying to run the SSIS package but i am getting the following error.
Executed as user: domain\serviceadmin. ...Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 9:37:13 AM Error: 2008-09-09 09:37:14.28 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "DTS:Password" with error 0x80070002 "The system cannot find the file specified.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error Error: 2008-09-09 09:37:14.30 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "DTS:Password" with error 0x80070002 "The system cannot find the file specified.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error Error: 2008-09-09 09:37:59.53 Code: 0xC0202009 Source: Step1 Connection manager "server1. The step failed.
What should be the problem? can anyone please help me out.......! This package is running some SQL statements and transfer sql server object tasks.
Thank you very much in advance.
Zombi.
September 9, 2008 at 4:56 am
The error you have posted is because you left the default setting for the ProtectionLevel property of your package set to EncryptSensitiveWithUserKey. This basically means that only the NT Login that created the package can decrypt any passwords that are stored directly in connection strings (a few other things are encrypted that are not important right now).
If you are using NT Authentication for all of your connections, this does not matter because there are no encrypted passwords in the package itself. If there are logins and passwords in your connections, you need to use package configurations or create the packages using the NT login that will run them (either the SQL Agent service account or a proxy). If you use package configurations, change the ProtectionLevel of the package to DontSaveSensitive to make this error go away.
If you are using NT Authentication, this error is not the cause of the package failing. The job agent only shows the beginning of the error messages from the package. You need to turn on package logging to get the details of the failure.
September 9, 2008 at 7:31 pm
Thanks for ure reply.
How to Turn on the logging mode?? which logging mode? in sql server agent or in business intellegent studio.
One more think is i cehanged the serviceadmin account to my admin account but still it was showing that running as a user domain\serviceadmin, it is not showing that domain\myadminaccount.
I changed that in the business intellegent studion in the properties of package "creator name"
where can i change the owner of the package?
Thanks.
September 11, 2008 at 12:57 am
Hi,
you know what, i am trying to delete entried from other server which is in different domain.
There is already a SSIs package to do this job created with the otherdomain\serviceadmin account. Now when i am creating a same package with my account then it does not show me the connection of other server. I do have a ccess but it is taking windows authentication and so i can't add connection to that server.
I want to add the sql task to that ssis package. If i run it then it is failing and if i login with that serviceadmin user and run it then it success. why????????
please help.
Thanks.
October 15, 2009 at 11:05 am
Hi,
i've the same problem, but created an credential and a proxy for the sql agent to run the package under the account the package was created.
So there should be no need to change the protection level to anything else than EncryptSensitiveWithUserKey.
While playing around I recognized a funny thing. The job executes fine as long as i'm logged in as the user referenced in the credential. When logging of the job fails.
Any idea what's going wrong here?
Further more you mentioned to turn on logging for the package. Where should that be done?
Kind regards
Gregor
October 16, 2009 at 8:07 am
fey 40736 (10/15/2009)
Further more you mentioned to turn on logging for the package. Where should that be done?Kind regards
Gregor
Right click anywhere in the design surface of the 'control flow' for the package, then click logging......
I usually go for SQL logging, but if you are having authentication problems then you'll probably be best just sticking to text file for just now. Don't forget to actually select some events to log!
October 16, 2009 at 8:46 am
@nahk.fussuy: Many thanks for your tip
Regards
Gregor
November 9, 2010 at 11:59 am
"The job executes fine as long as i'm logged in as the user referenced in the credential. When logging of the job fails.
Any idea what's going wrong here?"
Hi Grasshopper, did you ever figure that one out? I am having the same issue. Along with the "Failed to decrypt protected XML node "DTS:Password" with error 0x80070002 "The system cannot find the file specified.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available." error.
All this because I have a connection to an Oracle Server.
November 19, 2010 at 8:21 am
I've had the same error and just solved it.
As well as changing the protection level of the packages to DontSaveSensitive, you also need to make sure that anywhere within the package that points to a network drive i.e. all connection managers, and within any execute package containers, all the the file paths do not start off with the letter of the drive, instead use the server name.
i.e. K:\foldername\inputfile.xls
becomes: -
\\ServerName\foldername\inputfile.xls
November 23, 2010 at 9:19 am
Hi Wildster,
thank you for your hint. It might be the real solution for the problem, due to the fact that there is no correct drive mapping for the system account.
Unfortunatly i'm no longer responsible for this project.
Regards
Gregor
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply