Getting Started With Always Encrypted
Published Mar 23 2019 01:38 PM 1,691 Views
Microsoft

First published on MSDN on Jun 04, 2015

Updates:



    1. The syntax for column master keys have been updated. Please refer to http://blogs.msdn.com/b/sqlsecurity/archive/2015/10/28/new-enhancements-in-always-encrypted.aspx for details on what is new in Always Encrypted.

 

    1. We have updated the schema for our sample table to follow best practices. Big thanks to Denny Cherry for his feedback and help on greatly improving our sample schema.



 

 

The recently released SQL Server 2016 Community Technology Preview 2 introduced Always Encrypted , a new security feature that ensures sensitive data is never seen in plaintext in a SQL Server instance. Always Encrypted 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.

 

We will begin a series of articles on Always Encrypted with a simple example of the technology that can help everyone to get started. We will show how to develop a simple console application that uses Always Encrypted to protect patient information stored in a database.

 

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



    1. Database Engine from CTP2 of SQL Server 2016 (on a SQL Server machine).

 

    1. SQL Server Management Studio from CTP2 of SQL Server 2016 (on your development machine). Update: This article has been updated to reflect the syntax changes introduced in CTP3

 

    1. Visual Studio, preferably 2015 RC (on your development machine). Update: Visual Studio 2015 is now available.



Create a Database Schema using Always Encrypted

 

For this simple example, we will perform the following steps using SSMS (SQL Server Management Studio) on the development machine:



    1. Create a local, self-signed certificate on the development machine, which will act as a column master key (CMK). The CMK will be used to protect column encryption keys (CEK), which encrypts the sensitive data. We will then create a column master key definition object in the database, which will store the information about the location of the CMK. Please notice that the certificate will never be copied to the database or to the SQL Server machine.

 

    1. Create a column encryption key on the development machine, encrypt it using the CMK and then create a column encryption key object in the database uploading the encrypted value of the key.

 

    1. Create a simple table with encrypted columns.



Step 1 - Configure a Column Master Key

 

a) Create a new database named Clinic .

 

b) Using Object Explorer , locate and open the Always Encrypted Keys folder under Security for your database. Right-click on Column Master Key and select New Column Master Key …. This will open a dialog which you will use to define a column master key for your database. The easiest option for developing new apps using Always Encrypted is to use a certificate, stored in your personal Certificate Store, as a column master key.

 

 

c) Simply, enter CMK1 as a name of your column master key, click Generate Self-Signed Certificate , and click OK . This will generate a self-signed certificate, put it in your personal store ( Certificate Store: Current User ), and create a definition of the column master key in the database.

 

 

Step 2 - Configure a Column Encryption Key

 

To generate a column encryption key that will be used to encrypt sensitive data in the database, right-click on the Column Encryption Keys folder, select New Column Encryption Key , enter CEK1 as a key name and select CMK1 as an encrypting column master key for your new column encryption key. Once you click OK , a new column encryption key gets created, encrypted with the certificate you configured in step 1, and the encrypted value is uploaded to the database.

 

 

Step 3 – Create a Table using Always Encrypted:

 

Using a New Query window in SSMS, issue the following statement:


CREATE TABLE [dbo].[Patients](
[PatientId] [int] IDENTITY(1,1),
[SSN] [char](11) COLLATE Latin1_General_BIN2
ENCRYPTED WITH (ENCRYPTION_TYPE = DETERMINISTIC,
ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256',
COLUMN_ENCRYPTION_KEY = CEK1) 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] [date]
ENCRYPTED WITH (ENCRYPTION_TYPE = RANDOMIZED,
ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256',
COLUMN_ENCRYPTION_KEY = CEK1) NOT NULL
PRIMARY KEY CLUSTERED ([PatientId] ASC) ON [PRIMARY] )
GO

[Updated] Please notice that the schema for our table has changed to reflect best practices. The following is a summary of the changes:

 

SSN changed to char(11) as this is not a variable-length field. No need for this field to be nchar either as SSN will not contain any Unicode characters.

 

ZIP code changed to char(5) as there are certain ZIP codes that start with 0s, and therefore an integer will not represent them correctly.

 

State has changed to 2 char characters also notice that Unicode is not needed.

 

Changed Birthdate from datetime2 to date as the time and precision are not suitable for this field.

 

Once again, thanks a lot to Denny Cherry for his feedback on how to improve our schema.

 


 

The above T-SQL creates the Patients table with two encrypted columns: SSN and BirthDate . SSN is configured to be encrypted using deterministic encryption, which supports equality lookups, joins and group by. BirthDate is encrypted using randomized encryption, which does not support any operations, but that is ok, as the app is not going to perform any computations on the BirthDate column.

 

 

 

Create an Application using Always Encrypted

 

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

 

In Visual Studio, we will create a new console application using C#. Since the SqlClient enhancements to support Always Encrypted were introduced in .Net Framework 4.6, we need to ensure the application is using the right version of the framework. Right click on the project, select Properties , then go to the Application tab, and make sure that Target Framework option is set to “.Net Framework 4.6”.

 

 

Next we will add very simple code that connects to the database, inserts and selects data using SqlClient . You will notice that the only change required to use Always Encrypted is including “Column Encryption Setting=Enabled;” in the connection string. The complete code is included as a file attachment.

 

For this exmple, we are enabling the Column Encryption Setting in the connection string using a SqlConnectionStringBuilder object and setting SqlConnectionStringBuilder.ColumnEncryptionSetting to Enabled … and that’s pretty much it.


strbldr.ColumnEncryptionSetting = SqlConnectionColumnEncryptionSetting.Enabled;

Something to remark is that in order to send values that will correspond to encrypted columns, you need to use SqlParameter class. It is not possible to use literals to pass such values.


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

SqlParameter paramSSN = cmd.CreateParameter();
paramSSN.ParameterName = @"@SSN";
paramSSN.DbType = DbType.AnsiStringFixedLength;
paramSSN.Direction = ParameterDirection.Input;
paramSSN.Value = ssn;
paramSSN.Size = 11;
cmd.Parameters.Add(paramSSN);

SqlParameter paramBirthdate = cmd.CreateParameter();
paramBirthdate.ParameterName = @"@BirthDate";
paramBirthdate.SqlDbType = SqlDbType.Date;
paramBirthdate.Direction = ParameterDirection.Input;
paramBirthdate.Value = birthdate;
cmd.Parameters.Add(paramBirthdate);

cmd.ExecuteNonQuery();

cmd.CommandText = @"SELECT [SSN], [FirstName], [LastName], [BirthDate] FROM [dbo].[Patients] WHERE [SSN] = @SSN;";

SqlParameter paramSSN = cmd.CreateParameter();
paramSSN.ParameterName = @"@SSN";
paramSSN.DbType = DbType.AnsiStringFixedLength;
paramSSN.Direction = ParameterDirection.Input;
paramSSN.Value = ssn;
paramSSN.Size = 11;
cmd.Parameters.Add(paramSSN);

SqlDataReader reader = cmd.ExecuteReader();

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 SSMS query for that. If we simply select our table, you will notice that the SSN & BirthDate columns seem to be displaying binary data.

 

 

Conclusion

 

As we have seen, Always Encrypted transparently encrypts/decrypts sensitive data in the application as long as the application has access to the certificate acting as a CMK. Users and applications without access to the CMK, including SQL Server itself, will not be able to decrypt the sensitive data.

 

If you connect SQL Profiler to the database while running this application, you will notice that SQL Server will receive data corresponding to the encrypted values only as ciphertext, never as plaintext.

 

 

Special thanks the Always Encrypted team for their help writing this article.

 

AlwaysEncryptedDemo.cs

Version history
Last update:
‎Mar 29 2019 08:58 AM
Updated by: