ohemaa
asked on
package encryption in SSIS
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?
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?
ASKER
Encrypt sensitive with User Key is how it is now
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Here are some solutions provided by Microsoft that coudl probably help you too:
http://support.microsoft.com/kb/918760/
http://support.microsoft.com/kb/918760/
ASKER
it executes ok in msdb but fails when I schdule it as a job
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
are you calling it to run from MSDb under SQL agent --integration --then specify the job
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
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
ASKER
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
Thanks All.....solution works for me also
Redeploy the package after saving