SSIS Package security

#11

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.

""Charles Wang [MSFT]"" wrote:

Quote:

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: msdnmg (AT) microsoft (DOT) com.
================================================== =========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.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/subscripti...t/default.aspx.
================================================== ==========
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======

#12

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.

""Charles Wang [MSFT]"" wrote:

Quote:

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: msdnmg (AT) microsoft (DOT) com.
================================================== =========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.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/subscripti...t/default.aspx.
================================================== ==========
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======

#13

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.

""Charles Wang [MSFT]"" wrote:

Quote:

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: msdnmg (AT) microsoft (DOT) com.
================================================== =========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.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/subscripti...t/default.aspx.
================================================== ==========
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======

#14

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.

""Charles Wang [MSFT]"" wrote:

Quote:

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: msdnmg (AT) microsoft (DOT) com.
================================================== =========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.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/subscripti...t/default.aspx.
================================================== ==========
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======

#15

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.

""Charles Wang [MSFT]"" wrote:

Quote:

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: msdnmg (AT) microsoft (DOT) com.
================================================== =========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.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/subscripti...t/default.aspx.
================================================== ==========
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======

#16

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.

""Charles Wang [MSFT]"" wrote:

Quote:

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: msdnmg (AT) microsoft (DOT) com.
================================================== =========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.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/subscripti...t/default.aspx.
================================================== ==========
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======

#17

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.

""Charles Wang [MSFT]"" wrote:

Quote:

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: msdnmg (AT) microsoft (DOT) com.
================================================== =========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.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/subscripti...t/default.aspx.
================================================== ==========
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======

#18

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/l...7(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_leonar...design-pattern
-read-a-dataset-from-variable-in-a-script-task.aspx
Sourcing XML Data in SSIS 2005
http://blogs.msdn.com/rdoherty/archi...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: msdnmg (AT) microsoft (DOT) com.
================================================== =========

#19

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/l...7(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_leonar...design-pattern
-read-a-dataset-from-variable-in-a-script-task.aspx
Sourcing XML Data in SSIS 2005
http://blogs.msdn.com/rdoherty/archi...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: msdnmg (AT) microsoft (DOT) com.
================================================== =========

#20

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/l...7(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_leonar...design-pattern
-read-a-dataset-from-variable-in-a-script-task.aspx
Sourcing XML Data in SSIS 2005
http://blogs.msdn.com/rdoherty/archi...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: msdnmg (AT) microsoft (DOT) com.
================================================== =========