Solved

SSIS Package Failed to decrypt protected XML node

Posted on 2009-04-01
12
3,823 Views
Last Modified: 2013-11-10
Hi experts,
I am a newbie. I have a situation where I need to open up a package built by my Colleague and  when I open the package I get an error
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.
I know that Its because the package properties are to ENCRYPTSENSITIVEWITHUSERKEY ... we need to change the property so that the package developed can be open by others also in order to make any modifications... I have tried changing it to DontsaveSensitivedata.If I do that i need to enter the credentials for the ODBC connections manually eachtime.. is there any workaround

Thanks
0
Comment
Question by:danny1620
  • 7
  • 5
12 Comments
 
LVL 22

Expert Comment

by:PedroCGD
ID: 24040976
Store those credentials in a XML file or in a SQL database table using package configurations funtionality
For futher details check the attached doc.
Helped?

regards,
Pedro
www.pedrocgd.blogspot.com
POTS-EX02-EN.doc
0
 

Author Comment

by:danny1620
ID: 24042515
Hi pedro,
Thanks.But,that did not help... I see that you have answered my other related question also
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/Q_24285379.html#a24041522
 Am i doing something wrong .. I have attached the XML file. I have modified the Usernames and passwords as the XML file when created did not have any values .. I am using an ODBC connection Setup In such a case what would my server name be ..... Help me out
Thanks

Package-Config-XML.txt
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 24044299
"passwords as the XML file when created did not have any values" is normal and intentional for security reasons.
Tomorrow I will take a look on your questions.
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:danny1620
ID: 24044359
Oh Is it!!... OK Thank You.. Will wait for your suggestions
Thanks again
0
 

Author Comment

by:danny1620
ID: 24046325
The solution you gave works fine with ODBC for oracle and even with native SQL client but i have packages which use Oracle Provider for Oracle and the connection string stores just the username, password and dsn it does not have any provider info ... How do i go about..

thanks
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 24048831
You can store using package configurations, just username, database... or all the connection string...
If you have problems using XML file, store the sensitive data inside a SQL database table with specific permissions... with all the connection string or some part of it.
Dont forget to set DelayValidation=True in all the connections you use Package COnfigurations.

Also test outside SSIS if your connection to oracle is ok.
regards,
pedro
0
 

Author Comment

by:danny1620
ID: 24050815
I have tried to use both SQL Server Package config and XML Config ... I have attached XLS with my SQL Server Config table.. Just check the data in I have added the password for both TEST Filter and TEST Filter1.. TEST Filter uses SQL Server as Dest and Orcale Provider for Oracle as Source and when i run the package deployed on MSDB I get the error.... While the TEST filter1 uses ODBC For Oracle and it gave me the same error message but it ran fine when i update the password values..... Is It different setup when dealing with Oracle Provider for Oracle ...DO I need to update any values
Below is the info used to connect to the Oracle DB using Oracle Provider for Oracle
Database:  Device List
Description: [Oracle 8i]
IP Address: 192.23.66.10
Host Name: ************
IP Address:
DB login with read permissions:
User: *****
Password: *******
Oracle Service Name: ********
 Thanks

SSIS-Config.xls
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 24058461
Your machine is a 32 or 64 bits?
0
 

Author Comment

by:danny1620
ID: 24060428
Its 32 bits..
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 24096169
Try it on another machine
0
 

Author Comment

by:danny1620
ID: 24119940
I am getting the same error Message as below
===================================
Package Validation Error (Package Validation Error)
===================================
Error at Import  router data from ODBC database to  Device [ (Oracle) [1]]: System.Data.Odbc.OdbcException: ERROR [28000] [Oracle][ODBC][Ora]ORA-01005: null password given; logon denied
ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed
ERROR [28000] [Oracle][ODBC][Ora]ORA-01005: null password given; logon denied
   at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
   at System.Data.Odbc.OdbcConnectionHandle..ctor(OdbcConnection connection, OdbcConnectionString constr, OdbcEnvironmentHandle environmentHandle)
   at System.Data.Odbc.OdbcConnectionOpen..ctor(OdbcConnection outerConnection, OdbcConnectionString connectionOptions)
   at System.Data.Odbc.OdbcConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
   at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
   at System.Data.Odbc.OdbcConnection.Open()
   at Microsoft.SqlServer.Dts.Runtime.ManagedHelper.GetManagedConnection(String assemblyQualifiedName, String connStr, Object transaction)
   at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager100.AcquireConnection(Object pTransaction)
   at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.AcquireConnections(Object transaction)
   at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper100 wrapper, Object transaction)
Error at Import  router data from  ODBC database to  Device [SSIS.Pipeline]: component "ODBC(Oracle)" (1) failed validation and returned error code 0x80131937.
Error at Import  router data from  ODBC database to  Device [SSIS.Pipeline]: One or more component failed validation.
Error at Import router data from ODBC database to  Device: There were errors during task validation.
 (Microsoft.DataTransformationServices.VsIntegration)
 
0
 

Accepted Solution

by:
danny1620 earned 0 total points
ID: 24281123
I got it working I Dont know why it was not taking the bUilt in ODBC Connection But the workaround was to use the Whole Connection String by using the Build Connection String Option ...
Thanks


0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Access SQL Server instance by NONE-admin Windows user 12 27
CDC and AOG on MS SQL 2012 13 23
Query for timesheet application 3 17
Parse this column 6 25
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question