SSIS with Always Encrypted

First published on MSDN on Dec 17, 2015

The released SQL Server 2016, currently available as Community Technology Preview , and Azure SQL Database introduced Always Encrypted , a new security feature that ensures sensitive data is never seen in plaintext in a SQL Server instance or in Azure SQL Database . Always Encrypted ensures sensitive data remains encrypted while in use. It works by transparently encrypting the data in the application, so that SQL Server will only handle the encrypted data and not plaintext values. Even if the SQL instance or the host machine is compromised, all an attacker can get is ciphertext of sensitive data.

In United Family Hospital, they are storing patient information in the SQL Server. The database contains a table called "Patients". The SSN and birthday columns in the Patients table contain sensitive information for patients and we need to encrypt these two columns in SQL Server. In this blog, we will show us how to read and write encrypted table "Patients" in an SSIS package .

For this example, we will need to install the following:

1. Database Engine from SQL Server 2016 Community Technology Preview (on a SQL Server machine). Alternatively, if we want to use Azure SQL Database, we need a subscription in Azure.


2. SQL Server Management Studio from SQL Server 2016 Community Technology Preview (on development machine).


3. Designer from SQL Server Data Tools Preview in Visual Studio 2015 .

Create a Database Schema using Always Encrypted

First, using SSMS create a database and create a plaintext schema of the Patients table using the following statement:

CREATE TABLE [dbo].[Patients](
[PatientId] [int] IDENTITY(1,1),
[SSN] [char](11) COLLATE Latin1_General_BIN2 NOT NULL,
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[MiddleName] [nvarchar](50) NULL,
[StreetAddress] [nvarchar](50) NULL,
[City] [nvarchar](50) NULL,
[ZipCode] [char](5) NULL,
[State] [char](2) NULL,
[BirthDate] [datetime] NOT NULL
PRIMARY KEY CLUSTERED ([PatientId] ASC) ON [PRIMARY] )
GO

Now we can follow SSMS Encryption Wizard - Enabling Always Encrypted in a Few Easy Steps to encrypt SSN column and BirthDate columns.

Create an SQL Server Integration Service package using Always Encrypted

Now that we have the Always Encrypted keys and the schema configured, we can create a package that will be able to insert data into the Patients table & transform it.

Step 1 – Add a new ADO.NET connection manager to the package

The SQL Server 2016 Always-Encrypted feature is only supported by ADO.NET. We need to add a new ADO.NET connection manager to access the encrypted table. In order to handle encrypted columns we need to set the Column Encryption Setting for this connection manager from to “Enabled” (the default value “Disabled”).

Step 2 – Create an “Execute SQL Task” to write data into encrypted table

Add and “Execute SQL Task” and select “ADO.NET” as the connection type and chose the connection manager we created just now in the drop list.

As values inserted, updated or compared to encrypted columns must be passed using parameterized SQL, we need to configure parameters for the T-SQL execution task. The SQL statement  should like this:

INSERT INTO [dbo].[Patients] ([SSN], [FirstName], [LastName], [BirthDate]) VALUES (@SSN,@FirstName, @LastName, @BirthDate);

Now we need to configure the parameters for the SQL Task.

Go back to the “Execute SQL Task” and set the parameter mapping:

Step 3 – Add a data flow to read the encrypted data

Switch to the data flow tab and add an “ADO Net Source”, use the same connection manager as the “Execute SQL Task” and select the “dbo.Patients” table. Add a sort
transformation to sort the source base on last name column, right click the line between source and sort transformation then click “Enable Data Viewer”.

Now, we can click “Start” to run this package in SSDT, the unencrypted information will be shown on the data viewer.

At this point many readers may be doubting we really encrypted anything, after all, the application seems to be simply handling plaintext as
naturally as before; so, how can we verify that the data was properly encrypted?

We can use an SSMS query for that. If we simply select our table, we will notice that the SSN & BirthDate columns seem to be displaying
binary data.

Conclusion

The ADO.NET source/destination are able to transparently encrypt and decrypt data because we have set up “column encryption setting=enabled” and the ADO.NET provider can access the column master key that protects the data in the database.

Note that, since we followed SSMS Encryption Wizard - Enabling Always Encrypted in a Few Easy Steps to encrypt the SSN and BirthDate columns, the column master key is a certificate stored in the Current User certificate store location on the machine with SSMS. Since we executed the SSIS package on the same machine under the same identity, the ADO provider could access the certificate to successfully encrypt and decrypt the columns.

If we try to execute the SSIS package on another machine, we need to make the CMK available on that machine (in the case of the cert, we would need to export/import the cert to the target machine).

A security benefit, the example demonstrates, is the database has been removed from the attack surface area. While, the SSIS package operates on the plaintext values
of SSN and BirthDate, this sensitive information is never visible in plaintext in the database system.

L imitations

The SQL Server 2016 Always-Encrypted feature is only supported by the ADO.NET  provider currently. It is not supported by the OleDB provider and therefore any OleDB-provider-related transformation tasks such as Fuzzy Lookup will not support Always Encrypted feature.

In the “Execute SQL Task”, parameter binding for some encrypted SQL types is not supported, because of data type conversion limitations in Always Encrypted. The
unsupported types are money, smallmoney, smalldatetime, UniqueIndentifier, DatatimeOffset, time and date.