Solved

package encryption in SSIS

Posted on 2008-10-16
12
649 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 400 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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 100 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

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 the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.

705 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