This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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

In this blog

Encrypting the package
Encrypting the data in SQL

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:

As this diagram shows, you can also press ALT + ENTER.

You can now change 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:

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.

Some other pages relevant to the above blog include:

This blog has 0 threads

Add a new post