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

 

Writing Always Encrypted Data with Integration Services 2016


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

Attend these FREE MSSQLTips webcasts >> click to register


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


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