July 23, 2013 at 2:11 am
The Gurus,
I am tasked with managing a package which was working without any problem but a colleague errorneously reported a problem and I had to delete the job that run the package and the package as well but that was after I have saved the SSIS package. I observed the package in BIDS and run it successfully in BIDS. However, it has been failing since I deployed it back into SQL Server. I have been on it for like a week now and I am tired of trying. The account that it is using has full sysadmin access even though it used to have public + dbo before. I had to elevate it to see if it will solve the problem that I am having. The error message is below. Can anyone advise on how to get past this error message? Many thanks in advance.
Message
Executed as user: Domain\AccountName. Microsoft (R) SQL Server Execute Package Utility Version 10.50.2500.0 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 09:01:57 Error: 2013-07-23 09:01:57.34 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". 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: 2013-07-23 09:01:57.61 Code: 0xC0202009 Source: Package Connection manager "SourceConnectionOLEDB__XM_Infor" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80040E4D Description: "Login failed for user 'ABC_User'.". End Error Error: 2013-07-23 09:01:57.61 Code: 0xC020801C Source: Data Flow Task Details OLE DB Source Detail Lines [1744] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "SourceConnectionOLEDB__ABC" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error Error: 2013-07-23 09:01:57.61 Code: 0xC0047017 Source: Data Flow Task Details SSIS.Pipeline Description: component "OLE DB Source Detail Lines" (1744) failed validation and returned error code 0xC020801C. End Error Error: 2013-07-23 09:01:57.61 Code: 0xC004700C Source: Data Flow Task Details SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2013-07-23 09:01:57.61 Code: 0xC0024107 Source: Data Flow Task Details Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 09:01:57 Finished: 09:01:57 Elapsed: 0.468 seconds. The package execution failed. The step failed.
July 23, 2013 at 2:13 am
What is the protection level of the package? (check the package properties)
Failed to decrypt protected XML node "DTS:Password"
This means your package is encrypted. If the protection level is EncryptSensitiveWithUserKey (the default), you need to change it to EncryptSensitiveWithPassword. You'll need to supply the password in the SQL Server Agent job.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 23, 2013 at 2:24 am
Koen,
Many thanks for your response. How and where can I view this property?
Once again, thanks.
Sahoong
July 23, 2013 at 2:34 am
As I said, in the package properties.
Click on the control flow, hit F4.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 23, 2013 at 2:48 am
Thanks. I found it already and it worked. The problem now is that it is asking for password each time you want to open the package and it also did when I deployed it. My concern is that if there was a password before I wouldn't have been able to open it. Am I not creating a complexity? Is there a way that I can get this working without having to specify a password. I just want to leave the package the way it was before I edited it.
Thanks.
July 23, 2013 at 3:38 am
What kind of authentication do you use in the connection managers? Windows authentication or SQL Server authentication?
If the first, you can safely set the protection level to DontSaveSensitive.
If the second, you can also set it to DontSaveSensitive, but you'll need to store the username and password in a configuration.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 23, 2013 at 3:53 am
We use SQL Server authentication and I checked saved password in the configuration manager but yet, the package run successfully in bids then but not in SQL Server. Are you referring to a different configuration because I think I did what you indicated here already?
Thanks.
July 23, 2013 at 4:02 am
Hi
I'm afraid his problem is not related to the protection method of package.
Please change to Windows authentication mode in connection manager (if mixed mode is on).
Br.
Mike
July 23, 2013 at 4:34 am
michal.lisinski (7/23/2013)
HiI'm afraid his problem is not related to the protection method of package.
Please change to Windows authentication mode in connection manager (if mixed mode is on).
Br.
Mike
How is this not related? The protection level of the package specifies what happens with the passwords of the connection managers.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 23, 2013 at 4:35 am
sahoong (7/23/2013)
We use SQL Server authentication and I checked saved password in the configuration manager but yet, the package run successfully in bids then but not in SQL Server. Are you referring to a different configuration because I think I did what you indicated here already?Thanks.
Saving the passwords in the connection manager works only during design time (in BIDS).
Once you deploy the package to the server, you have to specify the password.
This is done by a configuration.
Understanding Integration Services Package Configurations
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 23, 2013 at 4:50 am
Hi
Sorry, I didn't express clearly. Of course you are right, It's related but Imho there main problem that it's unable to save the password in the package and deploy it.
Br.
Mike
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply