OLEDB provider for Oracle

Question

OLEDB provider for Oracle

Gangadhara MS

Hall of Fame

Points: 3377
More actions
September 8, 2011 at 2:30 am

#257019

Dear All,
I am using Connection manager of Native OLEDB/Microsoft OLEDB provider for Oracle where i am saving credentials user name and password with save password check box checked.
But when i running the job from the Job agent its failing with error says
"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."
Could u please help me to save password.
Thanks,
Gangadhara

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Ashok Teotia SSCrazy Points: 2407 More actions · Answer 1

Use Deployment method while running package in job,if the user/account is different from who has created the package and the user/account under which SQL Agent is running..

I hope this will help you.

Regards

Ashok

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 2

The problem is related to the package ProtectionLevel set to EncryptSensitiveWithUserKey

Try the following links.

http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/749bb98a-5317-43bd-a548-e0cdef5a12d9

http://msdn.microsoft.com/en-us/library/ms138023.aspx

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Gangadhara MS Hall of Fame Points: 3377 More actions · Answer 3

Can you please somebody assist me.The above link resolution does 't helped me out.

kramaswamy SSCoach Points: 18181 More actions · Answer 4

Welsh's solution should work, assuming you read through it all.

Long story short, change the ProtectionLevel on your package from EncryptSensitiveWithUserKey, to EncryptSensitiveWithPassword, or EncryptAllWithPassword. Create a password. When you deploy your script in SQL Server Agent, make sure the password is specified, you should see it in the Command Line tab.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 5

kramaswamy (9/12/2011)
Welsh's solution should work, assuming you read through it all.

Thank you, I know it will work. You just have to try it.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

kramaswamy SSCoach Points: 18181 More actions · Answer 6

I wonder if in SSIS 2008 or Denali, they were smart enough to change the default setting on an SSIS package from EncryptSensitiveWithUserKey to EncryptSensitiveWithPassword. The ease of having no password is totally eclipsed by the fact that you're always going to be getting errors when you copy the solution to another machine or deploy it on a server.