The whole process of encryption of SSIS packages in SSMS relies on the Import Package and Export Package options; specifically, the combination of performing both of mentioned options in particular order, while choosing and setting the appropriate ProtectionLevel in the process.
In short, to encrypt a particular package, it must be exported with new credentials specified, and then imported back into specified folder.
Import Package and Export Package options
To start the package encryption process, after right clicking on desired package, choose the option ‘Export Package…’
In the dialog below, there are several options to choose, in order to export the selected package. The package location could be located in the SSIS Package Store (which requires input of a server name) or File System.
The package path can be defined by the user as a custom destination path for File System or the destination folder in the Package Store.
To choose the protection level, click the ellipse as shown below,
and the following dialog will appear:
The Protection level option regards sensitive data, in one case, or all the data included in particular package in other. Data that is considered sensitive is set by default in Integration Services: variables previously marked as delicate, non-changeable XML tags, which are controlled by the SSIS service, and password, which can be considered sensitive if the ‘Encrypt all data with password’ is chosen.
Package protection levels:
Do not save sensitive data: if sensitive data exists, it will not be included after the exporting of the new package, remaining unavailable;
Encrypt sensitive data with user key: sensitive data will be encrypted with current user credentials, and package still can be used on local server. Which data will be considered as sensitive, depends on the creator/owner of the package;
Encrypt sensitive data with password: with this level, a password must be provided – this kind of encryption is desirable, if user want to keep only sensitive data private.
Encrypt all data with user key: same as the encryption of sensitive data, it can be used on local server, but it regards all the data within the package;
Encrypt all data with password: this level encrypts all data within the package, password is required, and it provides a 100% privacy;
Exporting packages
The examples provided will be encrypted with passwords
There is a determined Package path within File System (in this case, on local D:\ disk, for testing purposes, and to provide visibility of exported package path), and the Protection level will be set to Encrypt sensitive data with password:
The same process is applied on exported packages when setting the Encrypt all data with password Protection level.
Importing back the encrypted package
To confirm the successful encryption (or to show the example of preventing the unauthorized usage), import back the new package.
The following window will appear:
While importing the encrypted package, the Protection level must be selected again. The primary option is to keep the protection level of the original package. In that state, the next step is providing valid credentials:
If valid credentials are not provided, the dialog will reappear with following addition in message:
Here are the other options within Protection level, while importing:
They are similar to Protection levels while exporting, with the edition of Rely on server storage and roles for access control.
With providing valid credentials, the new package is added to the Integration Services:
References
- Access Control for Sensitive Data in Packages
- Set or Change the Protection Level of Packages
- dtutil Utility
Daniel is currently working for ApexSQL LLC as Software sales engineer where he specializes for the BI environment.
- How to monitor the SQL Server tempdb database - July 6, 2017
- How to monitor total SQL Server indexes size - June 12, 2017
- How to set and use encrypted SQL Server connections - May 8, 2017