SSIS Package security

[Gerhard]

I have an SSIS Package I put together on one of our servers using Visual
Studio 2005. When I open the solution and run it under my login, all is
fine. When the administrator of the computer opens in and runs it, he gets
the following error:

Error 2 Error loading Package.dtsx: Failed to decrypt protected XML node
"PackagePassword" with error 0x8009000B "Key not valid for use in specified
state.". You may not be authorized to access this information. This error
occurs when there is a cryptographic error. Verify that the correct key is
available. C:\Documents and Settings\veachb\My Documents\Visual Studio
2005\Projects\SBN Reporting\SBN Reporting\Package.dtsx

I did not knowingly set any password to protect this. Is there a way to
open this up so that other users on the computer can run it successfully?

Thanks.

Bob

[Charles Wang [MSFT]]

Hi Bob,
Thank you for using Microsoft MSDN Managed Newsgroup.

I understand that your administrator could not open your SSIS package on
his computer with the decryption error.
If I have misunderstood, please let me know.

It seems that he was experencing the known issue documented in the KB
article 918760. You may change your package ProtectionLevel to
ServerStorage or EncryptSensitiveWithPassword and then save your SSIS
package and distribute it to your administrator's computer.

From the KB article, we can find that this issue often occurred if the
current account or the execution account differs from the original package
author, and the package's ProtectionLevel property setting does not let the
current user decrypt secrets in the package. You can also try the other
methods in this KB article to see if they are helpful:
An SSIS package does not run when you call the SSIS package from a SQL
Server Agent job step
http://support.microsoft.com/kb/918760

Best regards,
Charles Wang
Microsoft Online Community Support
===========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: ***@microsoft.com.
===========================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for
non-urgent issues where an initial response from the community
or a Microsoft Support Engineer within 1 business day is acceptable.
Please note that each follow up response may take approximately
2 business days as the support professional working with you may
need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
============================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================

[Gerhard]

Thanks. I looked at the link, and looks like the best is to set the SSIS
Package ProtectionLevel property to ServerStorage. Trouble is, the help file
on Visual Studio does not have any reference to ProtectionLevel, and I don't
know how to do this. Can you help me with a link on that also?

Another question, within the package, I run various packages that were
created by the export wizzard. Do I need to change the setting on each of
those?

Also, when a table has a change, these scripts fail, and I have been unable
to find a way to edit them so end up having to rebuild them. Is there a way
to edit/refresh them in this instance?

Thanks for your help.

Post by Charles Wang [MSFT]
Hi Bob,
Thank you for using Microsoft MSDN Managed Newsgroup.
I understand that your administrator could not open your SSIS package on
his computer with the decryption error.
If I have misunderstood, please let me know.
It seems that he was experencing the known issue documented in the KB
article 918760. You may change your package ProtectionLevel to
ServerStorage or EncryptSensitiveWithPassword and then save your SSIS
package and distribute it to your administrator's computer.
From the KB article, we can find that this issue often occurred if the
current account or the execution account differs from the original package
author, and the package's ProtectionLevel property setting does not let the
current user decrypt secrets in the package. You can also try the other
An SSIS package does not run when you call the SSIS package from a SQL
Server Agent job step
http://support.microsoft.com/kb/918760
Best regards,
Charles Wang
Microsoft Online Community Support
===========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
===========================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for
non-urgent issues where an initial response from the community
or a Microsoft Support Engineer within 1 business day is acceptable.
Please note that each follow up response may take approximately
2 business days as the support professional working with you may
need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
============================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================

[Charles Wang [MSFT]]

Hi Gerhard,
For the information of setting ProtectionLevel, you can refer to this
article:
Setting the Protection Level of Packages
http://technet.microsoft.com/en-us/library/ms141747(SQL.90).aspx

For your first question setting ProtectionLevel to ServerStorage, it
requires the prerequisite that the packages must be stored in MSDB
database. You could not edit the package protectionlevel in the BIDS,
however you can refer to the following steps to export your package to MSDB
database:
1. In BIDS, choose File --> Save Copy of your-pkg.dtsx As...
2. Fill out Save Copy of Package dialog and make sure the protection level
set to "Rely on server storage and roles for access control".


For your second question, "within the package, I run various packages that
were created by the export wizard. Do I need to change the setting on each
of those? Also, when a table has a change, these scripts fail, and I have
been unable to find a way to edit them so end up having to rebuild them.
Is there a way to edit/refresh them in this instance?"

If you need to change the setting on each of those, you may save all the
packages to your MSDB database. For the table change and your script fails,
I think that you may consider save all your SQL scripts into a file (XML)
or a table, read it first to a variable and then execute the statement
extracted from the variable value. By this way, you just need to edit your
T-SQL script outside your SSIS package. Regarding variable usage, you may
refer to the following article:
Variables How-to Topics (Integration Services)
http://msdn.microsoft.com/en-us/library/ms141105.aspx
SSIS Design Pattern - Read a DataSet From Variable In a Script Task
http://sqlblog.com/blogs/andy_leonard/archive/2007/10/14/ssis-design-pattern
-read-a-dataset-from-variable-in-a-script-task.aspx
Sourcing XML Data in SSIS 2005
http://blogs.msdn.com/rdoherty/archive/2005/03/16/396956.aspx

Hope this helps.

Best regards,
Charles Wang
Microsoft Online Community Support
===========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: ***@microsoft.com.
===========================================================