By: Koen Verbeeck | Comments (4) | Related: > Encryption
Problem
I have a table with a couple of columns encrypted using the Always Encrypted feature. When I try to read the data with Integration Services (SSIS), I get an error. How can I solve this issue?
Solution
SQL Server 2016 Preview
At the time of writing, SQL Server 2016 is still in preview (currently CTP 3.2 has been released). This means functionality or features of the Always Encrypted or Integration Services might change, disappear or be added in the final release.
Test Set-up
For an introduction to Always Encrypted, check out the tip SQL Server 2016 Always Encrypted by Aaron Bertrand. This tip explains the feature and also details how to create a column master key and a column encryption key, which will be needed later on.
The AdventureWorks2016CTP3 sample database, which can be downloaded on Codeplex, contains the table Sales.CustomerPII. This table has two encrypted columns: SSN (social security number) and CreditCardNumber. When you try to read the data from the table with a regular connection, you can see that both columns are encrypted because binary data is returned instead of text.
You can only read the encrypted data if the following conditions are met:
- a connection has been made with the additional connection property column encryption setting=enabled
- the column master key created in the database is imported into the local certificate store (more detail on this later on)
To add the connection property, you need to click on Options when creating a new connection in SQL Server Management Studio (SSMS). In the tab Additional Connection Parameters, you can write "column encryption setting=enabled".
When you now try to read the data, an error is returned instead of data.
Importing the certificate
When you download the sample database, you can also download an archive with sample scripts. In this archive, there's a directory for Always Encrypted that contains a certificate called AlwaysEncryptedCMK.pfx. When you double click on the certificate, the Certificate Import Wizard is started. At the first screen, you can leave the default
Click Next at the second screen:
The next screen asks for the password of the private key. It's AlwaysEncrypted.
Leave the default for the certificate store:
Click Finish to end the wizard and import the certificate.
You can read more about importing and exporting private keys in the Import or export certificates and private keys how-to. Once the certificate has been imported, you can read data from the encrypted columns.
Reading encrypted data with SSIS
In an SSIS package, reading data from a table with encrypted columns follows the same process as outlined in the previous section. If you don't have the connection property set-up, you can read rows from the table in the preview:
As you can see in the preview window, instead of strings byte arrays are returned. You can preview the data, but you can't run the package. The byte arrays are much longer than the length of the string columns so they cause a truncation error if you try to run the package.
However, for the moment, only ADO.NET connection managers support the additional connection property. If you try to add the property column encryption setting=enabled to an OLE DB connection string, you get the following error:
You can configure an ADO.NET connection manager by clicking on the All tab and setting the property Column Encryption Setting to Enabled.
When you run the package now, you get an error that the encrypted data cannot be read just like in SSMS. Even the preview window will show an error.
Once the certificate is imported into the certificate store - as explained in the previous section - you can run the package and view all of the data:
Conclusion
With a few simple steps - setting a connection property and importing a certificate - we can read data from a table encrypted with Always Encrypted. The only downside is that OLE DB connection managers do not support this additional connection property. In a following tip, we'll find out how to write data to an encrypted table with SSIS.
Next Steps
- For more information on Always Encrypted, check out the tip SQL Server 2016 Always Encrypted.
- You can also find extra information in the official documentation.
- Don't forget to download the sample databases and the sample scripts from Microsoft to get you started.
- For more SQL Server 2016 tips, you can use this overview.
About the author
Koen Verbeeck is a seasoned business intelligence consultant at AE. He has over a decade of experience with the Microsoft Data Platform in numerous industries. He holds several certifications and is a prolific writer contributing content about SSIS, ADF, SSAS, SSRS, MDS, Power BI, Snowflake and Azure services. He has spoken at PASS, SQLBits, dataMinds Connect and delivers webinars on MSSQLTips.com. Koen has been awarded the Microsoft MVP data platform award for many years.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
