Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Reading Always Encrypted Data with SQL Server Integration Services 2016


By:   |   Read Comments   |   Related Tips: More > SQL Server 2016

New Whitepaper > 10 Ways We Can Steal Your Data >> download now


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


Last Update:


signup button

next tip button



About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a BI professional, specializing in the Microsoft BI stack with a particular love for SSIS.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools