BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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:
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.