Enable Always Encrypted for Your MSSQL Databases

By Feng Yi.

Nowadays, with the ubiquity of cloud computing, having a wide application across several major industries. It’s important that we are able to find a way to ensure that the data stored in the cloud is continually and constantly encrypted. Moreover, it’s also important that data is kept private, and even the cloud service providers cannot see the plaintext data stored in the database, thus guarantying the absolute security and privacy of customer data in the cloud database. Well, one way to go about achieving both of these things is through the Always Encrypted feature introduced in Microsoft’s SQL Server 2016.

Always Encrypted in SQL Server functions to keep data constantly and continually encrypted. Only applications that call SQL Server can read/write and/or operate encrypted data. Always Encrypted can prevent your database or OS administrators from accessing the sensitive data of customer applications. Always Encrypted in SQL Server 2016 controls client applications by verifying encryption keys, which are not transmitted over the network to a remote SQL Server. Therefore, Always Encrypted provides the maximum level of customer data security for cloud databases. With Always Encrypted on and running, even your cloud service providers cannot obtain the plaintext of customer data.

Procedure

You can follow the steps below to enable the Always Encrypted feature.

Create a Test Database

For this tutorial, to keep things simple, let’s create a test database called AlwaysEncrypted first for demonstration purposes. You can do so with the following code script:

--Step 1 - Create MSSQL sample database
USE master
GO
IF DB_ID('AlwaysEncrypted') IS NULL
CREATE DATABASE [AlwaysEncrypted];
GO
-- Not 100% require, but option adviced.
ALTER DATABASE [AlwaysEncrypted] COLLATE Latin1_General_BIN2;

Create a Column Master Key

Next, you’ll want to create a column master key (CMK) in the AlwaysEncrypted database.

-- Step 2 - Create a column master key
USE [AlwaysEncrypted]
GO
CREATE COLUMN MASTER KEY [AE_ColumnMasterKey]
WITH
(
KEY_STORE_PROVIDER_NAME = N'MSSQL_CERTIFICATE_STORE',
KEY_PATH = N'CurrentUser/My/C3C1AFCDA7F2486A9BBB16232A052A6A1431ACB0'
)
GO

Create a Column Encryption Key

Next, create a column encryption key (CEK).

-- Step 3 - Create a column encryption key
USE [AlwaysEncrypted]
GO
CREATE COLUMN ENCRYPTION KEY [AE_ColumnEncryptionKey]
WITH VALUES
(
COLUMN_MASTER_KEY = [AE_ColumnMasterKey],
ALGORITHM = 'RSA_OAEP',
ENCRYPTED_VALUE = 0x016E000001630075007200720065006E00740075007300650072002F006D0079002F006300330063003100610066006300640061003700660032003400380036006100390062006200620031003600320033003200610030003500320061003600610031003400330031006100630062003000956D4610BE7DAEFC2E1B08D557BFF9E33FF23896BD76BB33A84560F5E4BE174D8798D86CC963BA57867404945B166D756CE87AFC9EB29EEB9E26B08115724C1724DCD449D0D14D4D5C4601A631899C733C7646EB845A816A17DB1D400B7C341C2EF5838731583B1C51A457E14692532FD7059B7F0AFF3D89BDF86FB3BB18880F6B49CD2EA6F346BA5EE130FCFCA69A71523722F824CD14B3CE2C29C9E46074F2FE36265450A0424F390C2BC32B724FAB674E2B58DB16347B842597AFEBE983C7F4F51BCC088292219BD6F6E1F092BD77C5AD80331770E0B0B8BF6428D2719560AF56780ECE8805F7B425818F31CF54C84FF11114DB693B6CB7D499B1490B8E155749329C9A7AF4417E2A17D0EACA92CBB59A4EE314C54BCD83F80E8D6363F9CF66D8608772DCEB5D3FF4C8A131E21984C2370AB0788E38CB330C1D6190A7513BE1179432705C0C38B9430FC7A8D10BBDBDBA4AC7A7E24D2E257A0B8B79AC2B6D7E0C2F2056F58579E96009C488F2C1C691B3DC9E2F5D538D2E96BB4E8DB280F3C0461B18ADE30A3A5C5279C6861E3109C8EEFE4BC8192338137BBF7D5BFD64A689689B40B5E1FB7A157D06F6674C807515255C0F124ED866D9C0E5294759FECFF37AEEA672EF5C3A7649CAA8B55288526DF6EF8EB2D7485601E9A72CFA53D046E200320BAAD32AD559C644018964058BBE9BE5A2BAFB28E2FF7B37C85B49680F
)
GO

Check the CMK and CEK

Check the column master key (CMK) and column encryption key (CEK) that we have just created:

-- Step 4 - CMK & CEK Checking
select * from sys.column_master_keys
select * from sys.column_encryption_keys
select * from sys.column_encryption_key_values

As shown in the following screenshot, everything is normal.

Image for post

Of course, you can also use the SSMS IDE to check the CMK and CEK.

To do that, expand the database to be checked > Security > Always Encrypted Keys > Column Master Keys and Column Encryption Keys. See the following figure for reference.

Image for post

Create an Always Encrypted Test Table

Follow the provided code to create an Always Encrypted test table:

-- Step 5 -  Create a table with an encrypted columnUSE [AlwaysEncrypted]
GO
IF OBJECT_ID('dbo.CustomerInfo', 'U') IS NOT NULL
DROP TABLE dbo.CustomerInfo
GO
CREATE TABLE dbo.CustomerInfo
(
CustomerId INT IDENTITY(10000,1) NOT NULL PRIMARY KEY,
CustomerName NVARCHAR(100) COLLATE Latin1_General_BIN2
ENCRYPTED WITH (
ENCRYPTION_TYPE = DETERMINISTIC,
ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256',
COLUMN_ENCRYPTION_KEY = AE_ColumnEncryptionKey
) NOT NULL,
CustomerPhone NVARCHAR(11) COLLATE Latin1_General_BIN2
ENCRYPTED WITH (
ENCRYPTION_TYPE = RANDOMIZED,
ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256',
COLUMN_ENCRYPTION_KEY = AE_ColumnEncryptionKey
) NOT NULL
)
;
GO

When creating the Always Encrypted test table, specify the following parameters for the encryption field:

  • Encryption type: DETERMINISTIC and RANDOMIZED
  • Algorithm: AEAD_AES_256_CBC_HMAC_SHA_256 (a special algorithm for Always Encrypted)
  • Encryption key: The name of the created encryption key

Export the Server Certificate

Export the server-side certificate as a file:

Control Panel > Internet Options > Content > Certificates > Export¡­. See the following figure for reference.

Image for post

Enter a password to protect your private key in the Certificate Export Wizard.

Image for post

Select a storage path.

Image for post

The certificate is finally exported.

Application-side Testing

After configuring the SQL Server on the server side, import the certificate for the application to be tested and start to test the application.

Import the Certificate on Client

  • The steps to import the certificate on client are similar to exporting the server-side certificate are: Control Panel > Internet Options > Content > Certificates -> Import¡­. See the following figure.
Image for post

Then, enter the encryption password for the private key file. The file should be successfully exported now.

Test the Application

We can use Visual Studio to create a C# Console Application as the test Application and use the NuGet Package feature to install Dapper as the tool for performing SQL Server database operations.

Note: The SQL Server driver for Always Encrypted is supported only in .NET Framework 4.6 or later, so you’ll need to make sure that the Target framework of your project is at least .NET Framework 4.6 by clicking your project > Properties > Application. If the Target framework shows a .NET Framework version older than for 4.6, change it to .NET Framework 4.6 or later.

Image for post

Next, in this tutorial, for the sake of simplicity and convenience, we will test the application directly on the server side of the SQL Server. Therefore, the connection string that you will see will connect to the local SQL Server service. If you want to test a remote SQL Server, modify the connection string. The complete test application code is as follows:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using System.Data;
using System.Data.SqlClient;
namespace AlwaysEncryptedExample
{
public class AlwaysEncrypted
{
public static readonly string CONN_STRING = "Column Encryption Setting = Enabled;Server=.,1433;Initial Catalog=AlwaysEncrypted;Trusted_Connection=Yes;MultipleActiveResultSets=True;";
public static void Main(string[] args)
{
List<Customer> Customers = QueryCustomerList<Customer>(@"SELECT TOP 3 * FROM dbo.CustomerInfo WITH(NOLOCK)");
// there is no record
if(Customers.Count == 0)
{
Console.WriteLine("************There is no record.************");
string execSql = @"INSERT INTO dbo.CustomerInfo VALUES (@customerName, @cellPhone);";
Console.WriteLine("************Insert some records.************"); DynamicParameters dp = new DynamicParameters();
dp.Add("@customerName", "CustomerA", dbType: DbType.String, direction: ParameterDirection.Input, size: 100);
dp.Add("@cellPhone", "13402871524", dbType: DbType.String, direction: ParameterDirection.Input, size: 11);
DoExecuteSql(execSql, dp); Console.WriteLine("************re-generate records.************");
Customers = QueryCustomerList<Customer>(@"SELECT TOP 3 * FROM dbo.CustomerInfo WITH(NOLOCK)");
}
else
{
Console.WriteLine("************There are a couple of records.************");
}
foreach(Customer cus in Customers)
{
Console.WriteLine(string.Format("Customer name is {0} and cell phone is {1}.", cus.CustomerName, cus.CustomerPhone));
}
Console.ReadKey();
}
public static List<T> QueryCustomerList<T>(string queryText)
{
// input variable checking
if (queryText == null || queryText == "")
{
return new List<T>();
}
try
{
using (IDbConnection dbConn = new SqlConnection(CONN_STRING))
{
// if connection is closed, open it
if (dbConn.State == ConnectionState.Closed)
{
dbConn.Open();
}
// return the query result data set to list.
return dbConn.Query<T>(queryText, commandTimeout: 120).ToList();
}
}
catch (Exception ex)
{
Console.WriteLine("Failed to execute {0} with error message : {1}, StackTrace: {2}.", queryText, ex.Message, ex.StackTrace);
// return empty list
return new List<T>();
}
}
public static bool DoExecuteSql(String execSql, object parms)
{
bool rt = false;
// input parameters checking
if (string.IsNullOrEmpty(execSql))
{
return rt;
}
if (!string.IsNullOrEmpty(CONN_STRING))
{
// try to add event file target
try
{
using (IDbConnection dbConn = new SqlConnection(CONN_STRING))
{
// if connection is closed, open it
if (dbConn.State == ConnectionState.Closed)
{
dbConn.Open();
}
var affectedRows = dbConn.Execute(execSql, parms); rt = (affectedRows > 0);
}
}
catch (Exception ex)
{
Console.WriteLine("Failed to execute {0} with error message : {1}, StackTrace: {2}.", execSql, ex.Message, ex.StackTrace);
}
}
return rt;
}
public class Customer
{
private int customerId;
private string customerName;
private string customerPhone;
public Customer(int customerId, string customerName, string customerPhone)
{
this.customerId = customerId;
this.customerName = customerName;
this.customerPhone = customerPhone;
}
public int CustomerId
{
get
{
return customerId;
}
set
{
customerId = value;
}
}
public string CustomerName
{
get
{
return customerName;
}
set
{
customerName = value;
}
}
public string CustomerPhone
{
get
{
return customerPhone;
}
set
{
customerPhone = value;
}
}
}
}
}

Next, to support the Always Encrypted feature in SQL Server 2016, simply add the property configuration Column Encryption Setting = Enabled; to the application code. To make this clear, I put this property configuration at the beginning of the connection string:

Image for post

Then, after doing this, run the test application, and the following result will be returned:

Image for post

The application test result shows that the Always Encrypted test table can be read and written normally and that the test application works well. But the question remains: What if we use another method to read/write data from/to the test table instead of using the application?

Test SSMS

Suppose that we use Microsoft’s SQL Server Management Studio (SSMS) as the test tool, for example. Then, first, look over the data in the Always Encrypted test table:

-- try to read Always Encrypted table and it'll show us encrypted data instead of the plaintext.
USE [AlwaysEncrypted]
GO
SELECT * FROM dbo.CustomerInfo WITH(NOLOCK)

The result would be as follows:

Image for post

Then, you can use SSMS to insert data directly into the test table:

-- try to insert records to encrypted table, will be fail.
USE [AlwaysEncrypted]
GO
INSERT INTO dbo.CustomerInfo
VALUES ('CustomerA','13402872514'),('CustomerB','13880674722')
GO

The following error is reported:

Msg 206, Level 16, State 2, Line 74
Operand type clash: varchar is incompatible with varchar(8000) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'AE_ColumnEncryptionKey', column_encryption_key_database_name = 'AlwaysEncrypted') collation_name = 'Chinese_PRC_CI_AS'

Now consider the following screenshot.

Image for post

This indicates that we cannot use any method other than a test application to read and operate the plaintext data in an Always Encrypted table.

Analyze the Test Results

After comparing the test results of using the application and using SSMS for direct access to the Always Encrypted test table, we know that the former method allows normal table writes/reads. The latter cannot read the plaintext data in the test table and can only be used to view the ciphertext data after the test table is encrypted. When the latter is used, a write operation fails with an error.

Source Code of the Test Application

To obtain the source code of the test application used in this article, click here to download.

Summary

In this tutorial, you have seen the principle behind and implementation of the new feature in SQL Server 2016 — Always Encrypted. This feature ensures that the data stored in databases on the cloud is always encrypted and that even cloud service providers cannot see the plaintext data in databases. Therefore, Always Encrypted ensures absolute security of customer data in cloud databases and alleviates suspicion towards the credibility of cloud service providers, which is a critical issue in cloud database scenarios.

Original Source

Written by

Follow me to keep abreast with the latest technology news, industry insights, and developer trends.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store