Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

package encryption in SSIS

Posted on 2008-10-16
12
Medium Priority
?
660 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
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
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

564 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