Daniel Tikvicki

Setting SSIS package encryption manually in SSMS

June 24, 2016 by

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:

See more

For SSIS package documentation, consider ApexSQL Doc, a tool that enables documenting SSIS packages from file system, SSIS package store, SQL Server, SSISDB catalog and SSIS project file in different output formats, and with various included details.

To compare SSIS packages with each other, consider ApexSQL Diff.

References


Daniel Tikvicki

Daniel Tikvicki

Daniel is a librarian who ran into a vortex of IT world, where he is levitating and learning constantly. He likes books, merely all forms of artistic expression (being a musician/poet himself), and he is underground publisher (fanzines and experimental music). Also, the points of interest include theology, mythology and pseudoscience.

Daniel is currently working for ApexSQL LLC as Software sales engineer where he specializes for the BI environment.
Daniel Tikvicki
SSIS packages

About Daniel Tikvicki

Daniel is a librarian who ran into a vortex of IT world, where he is levitating and learning constantly. He likes books, merely all forms of artistic expression (being a musician/poet himself), and he is underground publisher (fanzines and experimental music). Also, the points of interest include theology, mythology and pseudoscience. Daniel is currently working for ApexSQL LLC as Software sales engineer where he specializes for the BI environment.

449 Views