How to Get Started with Always Encrypted for Beginners Part 2

In this post we will pick up where we left off in Part 1, if you haven’t read that please go back and do so.

Now that we have encrypted our columns, it’s time to take a look at how we decrypt them inside SQL Server Management Studio or through our applications. You’ll be surprised to see how easy it is.

Verify Your Setup

First, let’s verify that the table is still encrypted, and nothing changed after you ran through the Part 1 examples. To confirm, simply query sys.columns, script out the table, or query the data to check that the Birthdate column is still encrypted.

USE [AdventureWorks2016CTP3]

GO

CREATE TABLE [HumanResources].[Employee_AE](

       [BusinessEntityID] [int] NOT NULL,

       [NationalIDNumber] [nvarchar](15) NOT NULL,

       [LoginID] [nvarchar](256) NOT NULL,

       [OrganizationNode] [hierarchyid] NULL,

       [OrganizationLevel] [smallint] NULL,

       [JobTitle] [nvarchar](50) NOT NULL,

       [BirthDate] [date] ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto7], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL,

       [MaritalStatus] [nchar](1) NOT NULL,

       [Gender] [nchar](1) NOT NULL,

       [HireDate] [date] NOT NULL,

       [SalariedFlag] [dbo].[Flag] NOT NULL,

       [VacationHours] [smallint] NOT NULL,

       [SickLeaveHours] [smallint] NOT NULL,

       [CurrentFlag] [dbo].[Flag] NOT NULL,

       [rowguid] [uniqueidentifier] NOT NULL,

       [ModifiedDate] [datetime] NOT NULL

) ON [PRIMARY]

GO

SELECT *  FROM [AdventureWorks2016CTP3].[HumanResources].[Employee_AE]

Check system tables

SELECT DB_NAME() as [database] , t.name as [table], c.name as [column], encryption_algorithm_name, encryption_type,encryption_type_desc from sys.columns c JOIN sys.tables t on t.object_id= c.object_id WHERE c.Name='Birthdate' and t.name = 'Employee_AE'

Decrypt with SQL Server Management Studio

Viewing decrypted data within SQL Server Management Studio (SSMS) is very easy. SSMS uses .NET 4.6 and the modern SQL Server client, so you can pass in the necessary encryption options. SSMS uses the connection string to access the Master Key and return the data in its decrypted format.

First create a new SQL Connection and Click Options to expand the window.

Then go to the Additional Connections Parameters Tab of the login window and simply type column encryption setting = enabled. Then choose Connect.

Now try SELECT From your columns.

SELECT *  FROM [AdventureWorks2016CTP3].[HumanResources].[Employee_AE]

Now the reason this works is both the Column Key and Master Key are stored in the Windows Certificate Store of this SQL Server. The Master Key was setup in Part 1 in the Windows Certificate Store.

Decrypt with an Application

According to MSDN for the application to decrypt data the account that connects to the database must have the VIEW ANY COLUMN MASTER KEY DEFINITION and VIEW ANY COLUMN ENCRYPTION KEY DEFINITION database permissions. These permissions are required to access the metadata about Always Encrypted keys in the database.

Once those permissions are established all you must do is change your application connection string to include Column Encryption Setting=enabled. Below is an example using SQL Server integrated security.

Example

string connectionString = “Data Source=server63; Initial Catalog=Clinic; Integrated Security=true; Column Encryption Setting=enabled”; SqlConnection connection = new SqlConnection(connectionString);

 Summary

Decrypting the data when you have the Master Key stored on your Database Server makes it easy, but it also gives access to the encrypted data to the DBA. Make sure when you are planning to use Always Encrypted you consider who you want to have access to the data and where you want to store the keys.  There are many more layers of security you can add to this by defining those items. The example I gave in both Part 1 and Part 2 are the least complex and therefore not the most secure, but it gives you a beginner’s overview of how implement it. You need to examine your application to understand if it fits with in the current supported features of always encrypted.