How To Get Started With Always Encrypted For Beginners - Part Two

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.

• How To Get Started With Always Encrypted For Beginners - Part One

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,
      NOT NULL,
    [OrganizationNode] [hierarchyid] NULL,
    [OrganizationLevel] [smallint] NULL,
      NOT NULL,
    [BirthDate] [date] ENCRYPTED WITH (
        COLUMN_ENCRYPTION_KEY = [CEK_Auto7],
        ENCRYPTION_TYPE = Deterministic,
        ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
    ) NOT NULL,
      NOT NULL,
      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

You can also just SELECT and look at the data. Here you see the encrypted values for the data in the birthdate column.

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]  

If you did it correctly you will see the decrypted BirthDate column.

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 examples 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 to implement it. You need to examine your application to understand if it fits within the currently supported features of always encrypted.