Writing Always Encrypted Data with Integration Services 2016

By:   |   Comments (4)   |   Related: > Encryption


Problem

I'd like to write data to a table using SQL Server Integration Services where some of the columns are encrypted with the new Always Encrypted feature of SQL Server 2016. However, I constantly get errors. 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. Also, this tip uses the same test set-up as the tip Reading Always Encrypted Data with Integration Services 2016. I recommend you read that tip first, as it outlines 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. This time we will of course try to insert data into that table. The sample scripts used in this tip also use the AdventureworksDW2016CTP3 database. Also, 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.

Trying to insert data using SQL Server Management Studio (SSMS) without the additional connection property will give the following error:

Insert into SSMS

Even using correctly typed parameters will lead to an error, but a different one in this case:

Insert into SSMS

The error explains the data passed in the variables is in plain text, but SQL Server expects the data to be already encrypted. Luckily for us, Integration Services will handle that part for us.

Writing data to an encrypted table with SSIS

Using a Data Flow

As mentioned in the tip Reading Always Encrypted Data with Integration Services 2016, only ADO.NET connection managers support the additional connection property. When it isn't specified, writing data to an encrypted table will lead to an error:

Writing encrypted data with SSIS, without property

If you set the connection property Column Encryption Setting to enabled, but haven't installed the certificate, you'll get the following error:

Writing with SSIS, without certificate

If everything is configured correctly, we can start writing data to the table. We will use the following query to fetch the source data:

SELECT
	 [CustomerID]		= CONVERT(INT,REPLACE([CustomerAlternateKey],'AW',''))
	,[FirstName]
	,[LastName]
	,[SSN]			= N'DummySSN'
	,[CreditCardNumber]	= N'DummyCreditCard'
	,[EmailAddress]
	,[PhoneNumber]		= [Phone]
	,[TerritoryID]		= -1
FROM [AdventureworksDW2016CTP3].[dbo].[DimCustomer];

Writing data to the table is now just the same as in any other data flow. You just have to remember to use ADO.NET components instead of OLE DB.

Success


Success

Let's verify if the rows actually made it correctly into the table. You cannot filter on the encrypted columns themselves.

Query Always Encrypted

When we filter on another column, we can see the rows made it to the destination table.

Query results

To clean-up the destination table, you can use the following SQL statement:

DELETE FROM [AdventureWorks2016CTP3].[Sales].[CustomerPII]
WHERE TerritoryID = -1;

Using an Execute SQL Task

In this section we will write data to the Always Encrypted table using an Execute SQL Task. However, we cannot use a regular SQL statement, as this will fail as demonstrated at the start of this tip. We will need a SQL statement where the values are passed in as parameters. The statement looks like this:

Query results

Since we're using an ADO.NET connection, we can use named parameters in the SQL script instead of question marks like in OLE DB. First we need to create variables that will hold the different values.

Variables

Next, map those variables against the parameters in the parameter mapping pane of the Execute SQL Task:

Parameter pane

Let's take a look in Profiler to find out what SSIS is sending to SQL Server when the package is running. Two statements are sent to SQL Server. The first one uses an extended system stored procedure to find out if parameters are encrypted.

Profiler

The second one sends the actual insert statement to SQL Server. Although the encrypted columns are small Unicode text columns, the parameters for the encrypted columns are passed as varbinary. This means the values are encrypted before they even reach SQL Server.

Profiler

In SSIS, we can see that the Execute SQL Task has executed successfully. However, we can only insert one row at a time. For performance reasons, the data flow will be a better choice to insert multiple rows into a table with encrypted columns.

Success

Conclusion

Writing data to a table with encrypted columns are straight forward: you have to import the certificate and you need to specify a connection property in the ADO.NET connection manager, just like when you want to read data. Insert data through an Execute SQL Task is a bit more complicated, as parameters must be used. This means you can only insert one record at a time.

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




Wednesday, April 18, 2018 - 3:07:43 AM - Koen Verbeeck Back To Top (75726)

 

What if you use a proxy with your user credentials?


Tuesday, April 17, 2018 - 3:35:16 PM - Juan Torres Back To Top (75723)

 Yes, it is the same machine and there are no proxies.

 

 


Tuesday, April 17, 2018 - 9:24:36 AM - Koen Verbeeck Back To Top (75719)

 

The SQL Server Agent runs on the same machine where the SSISDB is located and where the certificates are installed? Are you using a proxy in the job?


Monday, April 16, 2018 - 10:35:03 AM - Juan Torres Back To Top (75713)

 I have an implentation of always encrypted, the only part not working is executing SSIS packages from SQL server agent. Any ideas?

I have check user is the same, certificates applied and ssis pack runs fine from the code, not working from agent with no error just does not do anything for encrypted tables.

 

 















get free sql tips
agree to terms