SQL Server Integration Services 2016 Lookups With Always Encrypted Data

By:   |   Comments   |   Related: > Encryption


I have a table with data encrypted by the Always Encrypted feature of SQL Server 2016. I need to use this table as a lookup table in Integration Services (SSIS), but I can't make this work. How can I solve this issue?


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. Also, this tip uses the same test set-up as the tip Reading Always Encrypted Data with Integration Services 2016 and the tip Writing Always Encrypted Data with Integration Services 2016. I recommend you read those tips first, as they outline the various steps needed to interact with data encrypted by Always Encrypted. To summarize, you need:

  • a connection to be made with the additional connection property column encryption setting=enabled
  • the column master key, which was created in the database, to be imported into the local certificate store

We will use the same table, Sales.CustomerPII, of the AdventureWorks2016CTP3 sample database, which can be downloaded on Codeplex. However, this time we will read a sample line from this table, perform a lookup on the same table to retrieve sensitive data and then write the results to another table, which is also encrypted by Always Encrypted. Don't forget to download the sample scripts as well, as they contain the private key file needed to interact with the Always Encrypted data.

You can use the following script to create a copy of the Sales.CustomerPII table, albeit with less columns.

USE [AdventureWorks2016CTP3]

CREATE TABLE [Sales].[CustomerPIICopy_Encrypted](
	[CustomerID] [int] NOT NULL,
	[SSN] [nvarchar](11) COLLATE Latin1_General_BIN2
					,ENCRYPTION_TYPE = Deterministic
	[CreditCardNumber] [nvarchar](25) COLLATE Latin1_General_BIN2
					,ENCRYPTION_TYPE = Deterministic

The binary collations for the encrypted columns are a prerequisite of Always Encrypted. The following statement will be used to extract one single CustomerID from the original Sales.CustomerPII table.

SELECT TOP 1 [CustomerID]
FROM [Sales].[CustomerPII];

Now we will try to use the Sales.CustomerPII table as a lookup table to retrieve the SSN and CreditCardNumber columns. Then the results will be written to the Sales.CustomerPIICopy_Encrypted table.

First attempt: using the Lookup with Full Cache

Let's try to use a Lookup component configured with a full cache. The following query is used to retrieve the lookup data:

FROM [Sales].[CustomerPII];


The Lookup columns are configured as follows:

Lookup Config

As explained in the tip Writing Always Encrypted Data with Integration Services 2016, an ADO.NET destination needs to be used to write data to the encrypted table. The resulting data flow looks like this:

Data flow

But, when we run the package, we get the following error:


The reason for this is that the Lookup component doesn't support ADO.NET connections, so we were forced to use an OLE DB connection. But OLE DB connections don't support Always Encrypted. The Lookup component runs successfully, but it returns encrypted data (using a byte array) instead of the actual textual data. When we try to write this data to the destination, a conversion error is thrown.

Bytes instead of strings

Fortunately, the Lookup component doesn't only support OLE DB connections, but also cache connection managers.

Second attempt: using a cache connection manager

The concept of cache connection managers is explained in the tip Lookup and Cache Transforms in SQL Server Integration Services. First we need to retrieve the lookup data and write it to a cache transform. The data flow looks like this:

Lookup data flow

An ADO.NET source needs to be used to retrieve the decrypted data from SQL Server. This means that the data is vulnerable inside the SSIS data flow though. The cache connection manager has the following configuration:

Cache connection manager

The cache connection manager doesn't use a file cache. The cache transform has the following column mappings:

Cache transform

In the lookup component, a cache connection is now chosen:

Lookup with cache connection

In the connection pane, you simple need to choose the connection manager created earlier. The column mappings stay the same. When we now run the data flow, the cache is first populated with decrypted data. Then the data is read from the source and matched against this cache. The lookup columns are now fetched in their decrypted state and written to the destination by the ADO.NET destination component. While writing to the destination table, the columns are encrypted again.


We can verify in the destination table that the data arrived correctly.

Test Success


Using a Lookup transformation in SSIS like you normally would is not possible when working with Always Encrypted data. This is because the Lookup component doesn't support ADO.NET connections, which are the only type of connections supporting Always Encrypted. However, using a work around with cache connections, we can still use lookups with encrypted data.

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

get free sql tips
agree to terms