You design a Business Intelligence (BI) solution by using SQL Server 2008. The SQL Server 2008 Integration Services (SSIS) developers use a SQL Server 2008 instance as the primary development environment.
All the SSIS packages contain data connection managers that use SQL Server authentication to extract data. The packages are saved by using the EncryptAllWithUserKey package protection level.You plan a package migration strategy from the development environment to a production environment. Migration will be performed by using an automated script.
You need to ensure that the packages execute without error in the production environment.
What should you do?
A.
Create a package configuration for every package that uses a SQL Server table.
B.
Create a package configuration for every package that uses an XML configuration file.
C.
Export each package and change the package protection level to DontSaveSensitive.
D.
Export each package and change the package protection level to EncryptSensitiveWithPassword.
Explanation:
Tip: "EncryptAllWithUserKey" = "EncryptSensitiveWithPassword"http://msdn.microsoft.com/en-us/library/ms141747.aspx
Setting the Protection Level of Packages
To protect the data in an Integration Services package, you can set a protection level that helps protect just sensitive data or all the data in the package. Furthermore, you can encrypt this data with a password or a user key, or rely on the database to encrypt the data. Also, the protection level that you use for a package is not necessarily static, but changes throughout the life cycle of the package. You often set one protection level during development and another as soon as you deploy the package.
Do not save sensitive (DontSaveSensitive) – Suppresses the values of sensitive properties in the package when the package is saved. This protection level does not encrypt, but instead it prevents properties that are marked sensitive from being saved with the package and therefore makes the sensitive data unavailable to other users. If a different user opens the package, the sensitive information is replaced with blanks and the user must provide the sensitive information.
Encrypt all with password (EncryptAllWithPassword) – Uses a password to encrypt the whole package. The package is encrypted by using a password that the user supplies when the package is created or exported. To open the package in SSIS Designer or run the package by using the dtexec command prompt utility, the user must provide the package password. Without the password the user cannot access or run the package.
Encrypt all with user key (EncryptAllWithUserKey) – Uses a key that is based on the current user profile to encrypt the whole package. Only the user who created or exported the package can open the package in SSIS Designer or run the package by using the dtexec command prompt utility.
Encrypt sensitive with password (EncryptSensitiveWithPassword) – Uses a password to encrypt only the values of sensitive properties in the package. DPAPI is used for this encryption. Sensitive data is saved as a part of the package, but that data is encrypted by using a password that the current user supplies when the package is created or exported. To open the package in SSIS Designer, the user must provide the package password. If the password is not provided, the package opens without the sensitive data and the current user must provide new values for sensitive data. If the user tries to execute the package without providing the password, package execution fails.
Encrypt sensitive with user key (EncryptSensitiveWithUserKey) – Uses a key that is based on the current user profile to encrypt only the values of sensitive properties in the package. Only the same user who uses the same profile can load the package. If a different user opens the package, the sensitive information is replaced with blanks and the current user must provide new values for the sensitive data. If the user attempts to execute the package, package execution fails. DPAPI is used for this encryption.
Rely on server storage for encryption (ServerStorage) – Protects the whole package using SQL Server database roles. This option is supported only when a package is saved to the SQL Server msdb database. It is not supported when a package is saved to the file system from Business Intelligence Development Studio.