Encrypt Sensitive With Password

Encryption of only the sensitive information in the package by use of a password is performed by “EncryptSensitiveWithPassword” option. Use of the standard Windows cryptography component called Data Protection Application Programming Interface (DPAPI) is adopted for this kind of encryption. Sensitive data which is saved as part of the package is encrypted during creation or export of the package with the use of a password supplied by the current user. The package password has to be provided by the user for opening the package in the SSIS Designer. In absence of the password, the opening of the package is without the sensitive data, new values for which have then to be provided by the current user. There is failure of package execution if execution of the package is attempted by the user without providing the password. Thus one has to be aware of passwords and command line execution like the dtexec command prompt utility.

Encrypt Sensitive With User Key

Packages have this option based on DPAPI as the default setting. By use of keys based on the current user, encryption of only the sensitive information in the package is performed by this option. Package loading can only be done by the same user with same user profile. Blanks appear in place of sensitive information whenever the package is opened by a different user by whom new values for the sensitive data must be provided. If the user attempts without these, there is failure of the package execution.

Reliance on Server Storage

The entire package that uses SQL Server database roles is protected by the “EncryptSensitiveWithUserKey” option. However only if a package is saved to MSDB database of SQL Server is this option supported. Where a package is saved to the file system from Business Intelligence Development Studio (BIDS), this option is not supported. This option relies on Server Storage for encryption.