Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
TBennett0217
Starting Member
1 Post |
 Posted - 2008-09-24 : 13:06:34
|
| We've got packages losing passwords when we load them from Visual Studio to our new server. I'm loading it with the rely on server storage protection level. If I load it with the encrypt sensitive data with password protection level, I am able to successfully execute it on the server when entering the password. What I am trying to figure out is what does it look at when using "rely on server storage"? I need to fix my server settings that are causing the passwords to disappear, but I don't know where or how to update this. I do have another server that is also using SQL 2005 and it is setup correctly, because passwords don't disappear when using "rely on server storage", so maybe I can copy the settings there, but I don't know where to look. Please help! |
|
|
saurabhsrivastava
Posting Yak Master
216 Posts |
Posted - 2008-09-26 : 22:45:36
|
| Do not save sensitive - removes sensitive information during the save. When the saved copy is opened, removed items (passwords, etc.) need to be added again in order to restore package to its original state. This also means that its execution (without re-adding missing information) will likely fail. Encrypt all with password - encrypts the entire package with a password specified during the save using the Triple DES cipher algorithm with 192-bit key. Knowledge of the password is required in order to execute the package or to open it for editing in Business Intelligence Development Studio. Encrypt all with user key - similar to the previously described level, encrypts the entire package but instead of using a password, makes the process transparent by applying a user-specific key stored securely in the user's personal profile. This means that executing the package or reopening it in Business Intelligence Development Studio must be performed not only by the same user but also requires access to the personal profile. As a result, this level is not suitable for shared development efforts or transferring packages between systems (protecting them with passwords is a more appropriate option). Encrypt sensitive with password - uses the same encryption mechanism as the "Encrypt all with password" level, but applies it only to sensitive information. This means that in order to successfully execute a package encrypted in this manner you will need to supply the password (otherwise, the execution will fail). On the other hand, you will be able to open such packages in the Business Intelligence Development Studio even without password knowledge, but during this process, the sensitive information will be automatically removed. Encrypt sensitive with user key - uses the same encryption mechanism as the "Encrypt all with user key" level, but applies it only to sensitive information. Execution of such packages without access to the encryption key (i.e. either by another user or by the original user with profile missing) will result in its failure. It is possible to open the package for edits in the same circumstances, but with sensitive information automatically removed. Note that this is the default protection level applied to packages created with Business Intelligence Development Studio (to verify this, right-click on the empty area of the Control Flow tab in the SSIS Designer, select Properties item from the context sensitive menu, and check ProtectionLevel entry in the Properties window). Rely on server storage and roles for access control - does not perform encryption, but instead leaves protection of the package content to the permissions mechanism built into SQL Server (this option is applicable only when storing the package in msdb database). For this purpose you can use fixed database-level roles, such as db_dtsadmin (with administrative rights to SSIS packages stored on SQL Server), db_dtsltduser (with ability to execute individual SSIS packages to which permissions have been granted), and db_dtsoperator(allowed to execute, backup, and restore all SSIS packages). Custom roles (Reader and Writer) can be granted using the Package Roles dialog box (displayed by selecting the Package Roles... item in the context sensitive menu of packages stored in SQL Server from Object Explorer in SQL Server Management Studio). |
 |
|
|
|
|
|
|
|
SSIS Losing Passwords