Question About ProtectionLevel: ServerStorage

[greenmtnsun]

Hi all,

I have a ton of SSIS packagest that I've created which point to an Oracle
Database; and sometimes SQL Server and Oracle at the same time. Since Oracle
doesn't have windows authentification, I need to save a password some where.
Anyway, we were storing them in the job on SQL Server and set the package to
EncryptSensitiveWithUserKey, in the connection manager, inside the SSIS step,
but with tons of packages, and the likelyhood that we will have to change the
password in the future, we would like to use a security system where I can
store that password in a central place. This will hopefully allow me to
change the password or server information one time in one place for many
packages.

I see that there is a ProtectionLevel: ServerStorage and that theoretically
this can do what I need. Can someone lead me through it with some
instructions?

Here is what I have so far starting from Visual Studio BIDS:

1. File Menu... Save Copy of [Your File Name.dtsx] As...
This action will open "Save Copy of Package" Screen
2. Specify Target Server
3. Specify Authentication Type (which apparently needs to be Windows
Authentification.)
4. Specify Package Path (Target Folder within Integration Services)
5. Protection Level must be set to "Server Storage"

Where do I put the central file?
How do I set it up? I didn't see a place to specify some file like this.
Do I understand this concept right?

Thanks,
Keith

[Todd C]

Hello:
I have developed a methodology for storing Connection String info in one
place, obstensibly for transfering package between Dev, Test, and Production
environments, but it could help you out. At one forum user's suggestion, I
documented it all in a blog to share easily.

http://toddchitt.wordpress.com/2008/06/27/ssis_config

Hope it helps you out. If you have questions, post a response on the blog
site and I will respond personally and privately.

Good Luck.
=====
Todd C

Post by greenmtnsun
Hi all,
I have a ton of SSIS packagest that I've created which point to an Oracle
Database; and sometimes SQL Server and Oracle at the same time. Since Oracle
doesn't have windows authentification, I need to save a password some where.
Anyway, we were storing them in the job on SQL Server and set the package to
EncryptSensitiveWithUserKey, in the connection manager, inside the SSIS step,
but with tons of packages, and the likelyhood that we will have to change the
password in the future, we would like to use a security system where I can
store that password in a central place. This will hopefully allow me to
change the password or server information one time in one place for many
packages.
I see that there is a ProtectionLevel: ServerStorage and that theoretically
this can do what I need. Can someone lead me through it with some
instructions?
1. File Menu... Save Copy of [Your File Name.dtsx] As...
This action will open "Save Copy of Package" Screen
2. Specify Target Server
3. Specify Authentication Type (which apparently needs to be Windows
Authentification.)
4. Specify Package Path (Target Folder within Integration Services)
5. Protection Level must be set to "Server Storage"
Where do I put the central file?
How do I set it up? I didn't see a place to specify some file like this.
Do I understand this concept right?
Thanks,
Keith