?
Solved

package encryption in SSIS

Posted on 2008-10-16
12
Medium Priority
?
652 Views
Last Modified: 2013-11-10
Failed to decrypt protected XML node "PackagePassword" with error 0x8009000B "Key not valid for use in specified state.".

 When I run my SSIS package.  It gets data into sql from another application using sql password
The package works fine when  I execute it in DIBS and MSDB.  I get this message when I schedule it as a job.  Any inputs?
0
Comment
Question by:ohemaa
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 3
  • +1
12 Comments
 
LVL 9

Expert Comment

by:Sander Stad
ID: 22738711
Try to change the protection level to "Encrypt sensitive with User Key" in the design area of your SSIS package.
Redeploy the package after saving
0
 

Author Comment

by:ohemaa
ID: 22738759
Encrypt sensitive with User Key is how it is now
0
 
LVL 9

Accepted Solution

by:
Sander Stad earned 1600 total points
ID: 22738848
This behavior occurs if the value of the ProtectionLevel property in the SSIS package is set to provide the maximum amount of protection for the Password property in the SSIS package.
If this package is run on the same computer the SSIS package decrypts en there are no error. But if you use a different user account or a different computer to run the SSIS package the Password property is stays encrypted what causes the error.
Try to use a different account that has the right privileges to run.
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 9

Expert Comment

by:Sander Stad
ID: 22738861
Here are some solutions provided by Microsoft that coudl probably help you too:
http://support.microsoft.com/kb/918760/ 
0
 

Author Comment

by:ohemaa
ID: 22741347
it executes ok in msdb but fails when I schdule it as a job
0
 
LVL 9

Expert Comment

by:Sander Stad
ID: 22742273
What user excutes the MS SQL server agent service? The username and password of that user have to be used to allow the execution to succeed.
0
 
LVL 17

Assisted Solution

by:HoggZilla
HoggZilla earned 400 total points
ID: 22742583
When you save it to the server (MSDB) sent the Protection Level to "Rely on server storage and roles for access control".
If the package runs successfully from MSDB, does that mean you are running the job manually in SQL Server Agent or are you calling it to run from BIDS, T-SQL sp?
Do you have other jobs running under SQL Server Agent? Are they hitting the same databases and file paths? Are you using a proxy? I have experienced many issues in all of these areas and worked through them.
0
 

Author Comment

by:ohemaa
ID: 22745466
how do I do this--Rely on server storage and roles for access control".
are you calling it to run from MSDb under SQL agent --integration --then specify the job
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22745587
When you save the package from BIDS, you select File - Save Copy of ...
Select SQL Server as the Package location.
Select Protection level: Rely on server storage and roles for access control.
Once the package is saved on the SQL Server, right click on SQL Server Agen and create a New Job. Under steps select New and the Type will be SQL Server Integration Services Package. Select the Server name and open the Package selection box where you can select the saved package.
If you are Admin role it should work from here. Let me know if you have issues.

save-copy-as.bmp
protection-level.bmp
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22745630
SQL Server Agent scheduling
New Job Step
Package Selection

new-job-step.bmp
select-package.bmp
0
 

Author Comment

by:ohemaa
ID: 22745632
I had to recreate the whole package but I am sure this would have resolved it.  I did not see this earlier ..would have given you more points.  I will try this next time I run into this issue...re-doing the package was a waste of time..thanks anyways
0
 
LVL 2

Expert Comment

by:batman_k
ID: 35397385
Thanks All.....solution works for me also
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

777 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