Solved

package encryption in SSIS

Posted on 2008-10-16
12
638 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 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
 
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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…
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

930 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now