Reading Always Encrypted Data with SQL Server Integration Services 2016

By:   |   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.

Read encrypted data

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".

Connection property

When you now try to read the data, an error is returned instead of data.

always encrypted

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

Importing certificate

Click Next at the second screen:

private key

The next screen asks for the password of the private key. It's AlwaysEncrypted.

certificate store

Leave the default for the certificate store:

end the wizard

Click Finish to end the wizard and import the certificate.

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

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:

Reading encrypted data with SSIS, without property

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.

Running SSIS, without property

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:

OLEDB error

You can configure an ADO.NET connection manager by clicking on the All tab and setting the property Column Encryption Setting to Enabled.

Configure ADO.NET

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.

Preview 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:

Success!

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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Koen Verbeeck 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



Comments For This Article




Friday, December 6, 2019 - 2:23:28 AM - Koen Verbeeck Back To Top (83302)

Hi Leo,

this SO post seems to have a solution:

https://stackoverflow.com/questions/57496804/ssis-data-flow-into-always-encrypted-column-with-azure-key-vault


Wednesday, December 4, 2019 - 12:33:02 PM - Leo Back To Top (83283)

Hi Koen,

I found this post useful and thank you for posting this article!

Instead of the local Windows Certificated, I use Azure Key Vault instead for storing the CMK. 

Is there a specific way to retrieve the CMK stored in Azure Key Vault from within SSIS and use it to decrypt the desired column?


Tuesday, January 30, 2018 - 5:07:45 AM - Koen Verbeeck Back To Top (75060)

 

Hi Hardy,

I believe that should be possible yes. You do have to specify additional connection properties though:

https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/configure-always-encrypted-using-sql-server-management-studio


Friday, January 26, 2018 - 3:26:51 PM - Hardy Back To Top (75048)

 Does this mean that once the certificate is deployeed to a server that runs the package, people can also use ssms installed in that server to view the data? 















get free sql tips
agree to terms