August 23, 2011 at 10:32 am
Hi I have deployed the package and when i was running this package from other machine i was getting the following error .Can some one help me in ignoring this failure ... Thanks in advance
Error Message
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: 2011-08-23 12:25:04.79 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: 2011-08-23 12:25:06.43 Code: 0xC0202009 Source: OracleDB_INFO Connection manager "Target" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80040E4D Description: "ORA-01017: invalid username/password; logon denied ". End Error Error: 2011-08-23 12:25:06.43 Code: 0xC020801C Source: DB_INFO OLE DB Source [40] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Target" 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: 2011-08-23 12:25:06.43 Code: 0xC0047017 Source: DB_INFO SSIS.Pipeline Description: component "OLE DB Source" (40) failed validation and returned error code 0xC020801C. End Error Error: 2011-08-23 12:25:06.43 Code: 0xC004700C Source: DB_INFO SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2011-08-23 12:25:06.43 Code: 0xC0024107 Source: DB_INFO Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 12:25:04 PM Finished: 12:25:06 PM Elapsed: 1.828 seconds. The package execution failed. The step failed.
August 23, 2011 at 2:30 pm
I have the Package deployed by following steps
-->Right Click on Project and Deployment utility is set to true
-->Build the Solution
-->Go to bin\Deployment and run the deployment tool and selected file system type.
-->Scheduled the package in SQL Server Jobs .
But i was getting the following error ..My Question is where the packages encrypted?? The package level protection is as "EncryptSensitiveWithUserKey"
Message
Executed as user: XX\ACXXXX. ...Version 10.50.1600.1 for 32-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 4:22:33 PM Error: 2011-08-23 16:22:33.84 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: 2011-08-23 16:22:33.86 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "DTS:Password" with error
August 23, 2011 at 3:07 pm
Now My issue was resolved once i changed the protection level of the package to encrypt with password and i gave the credentials at Set values in SQL Agent job properties and now the job runs fine with out any issues .The issue was raised as i developed on one machine and i was running the package on other machine .
August 23, 2011 at 6:43 pm
The problem is one that I've faced several times, and it's because the default encryption level is EncryptSensitiveWithUserKey.
The problem is that the User Key is something specific to a machine. That means once you copy the package to another machine, it will fail.
Changing it to EncryptSensitiveWithPassword, or EncryptAllWithPassword, allows you to avoid that problem.
October 3, 2012 at 12:12 am
http://msdn.microsoft.com/en-us/library/dd440760.aspx
Play Video on this page, this will resolve all issue regarding running SSIS packages through SQL Server Agent Job.
Thanks
Muhammad Qaiser
October 3, 2012 at 2:57 pm
Been there myself, and on occasion still forget to change the encryption.
July 19, 2013 at 4:23 am
What worked for me:
- create the package with the wizard using "Do not save sensitive data";
- import the package to Visual Studio Management;
- right click on the package and "View Code";
- find the connection string, in my case Oracle:
<DTS:Property DTS:Name="ConnectionString">Data Source=Oracledb;User ID=userDB;Provider=MSDAORA.1;Persist Security Info=True;</DTS:Property></DTS:ConnectionManager></DTS:ObjectData></DTS:ConnectionManager>
- modify the string by adding "Password=userPassword"
- save the package;
In my case this eliminated the null password problem, not to mention that, the password in not visible in the connection string.
Hope it works for you!
July 19, 2013 at 9:39 am
Problem with using that approach is that, if you choose to not save sensitive data, then you will have to specify all parameters that are sensitive each time you run - which is quite inconvenient. Much simpler to go with EncyrptSensitiveWithPassword, which allows you to save connection strings and such, and still keeps your package protected.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply