Role of Protection Levels

At the time of creation of packages in the Business Intelligence Development Studio (BIDS) or the wizards, protection levels are set. Modification or unauthorized execution of packages are prevented by the protection levels. When packages are imported into SSIS package store, they can be updated with protection levels.

The sensitive information referred by protection levels has got defined. Like a connection string’s password part is sensitive information. XML nodes generated by the tasks and marked as sensitive are also sensitive information. In this category also fall the variables marked as sensitive.

Protection Level Options

Three options, viz. “DontSaveSensitive”, “SensitiveWithPassword” and “EncryptAllWithUserKey” are discussed here. Sensitive information in the package at the time of saving is suppressed by the “DontSaveSensitive” option. Instead of encrypting, this protection level prevents saving of properties in the package marked as sensitive thereby making unavailable the sensitive data to other users. Whenever the package is opened by a different user, blank is shown instead of sensitive information till the user provides the sensitive information. The entire package is encrypted by use of password by the “SensitiveWithPassword” option. By the use of a password supplied by the user when the package is created or exported, package encryption is done. The user thus has to provide package password for opening the package in the SSIS Designer or for running the package with the use of dtexec command prompt utility. The user must have the password to access or run the package. By use of a key based on the user profile, a whole package can be encrypted by the “EncryptAllWithUserKey” option. Package loading can only be done by the same user who uses the same profile. Encryption of the package is done by a key based on the user by whom the package was exported or created. Here again the package can be opened in the SSIS Designer or run by use of the dtexec command prompt utility only by the user creating or exporting the package.