Ideas for encrypting data in transit in SSIS
How can you encrypt data flowing within an Integration Services package? We don't think you can, but here are a couple of ideas.

Posted by Andy Brown on 08 February 2015

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Encrypting data and security options in Integration Services

Another course question (thanks, Khiem): how can you encrypt data passing between (say) sources and destinations in SSIS?  The short answer is that I don't think you can (and neither does Shawn Melton at StackOverflow), but here are two ideas.

Encrypting the package

You can set the ProtectionLevel property to protect all of the text in a package incliuding passwords in connection strings.  To do this, first right-click on the background of a package and choose to show its properties:

Showing package properties

As this diagram shows, you can also press ALTENTER.

 

You can now change the ProtectionLevel property:

The ProtectionLevel property

The property has 5 possible values.

You can see a summary of what each option means here, and even (from SQL Server 2012 onwards) set a project-level protection level:

Project-level protection

Change the project's properties to set this option globally for all packages in a project.

However, while this encrypts things like connection strings it won't encrypt the data passing within a package.

Encrypting the data in SQL

To genuinely encrypt data while it flows within a package, you'll need one of the following SQL commands:

Command What it does
EncryptByKey Encrypts data using a symmetric key.
EncryptByPassPhrase Encrypts data with a passphrase using the TRIPLE DES algorithm with a 128 key bit length.

So you could run an Execute SQL package within SSIS, to call a stored procedure to encrypt your data - some thoughts on this are given in this useful blog.

This blog has 0 threads Add post