Solved

package encryption in SSIS

Posted on 2008-10-16
12
641 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
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.

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
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.
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

856 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