Enable Always Encrypted for Your MSSQL Databases

Procedure

Create a Test Database

--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

-- 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

-- 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

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

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

Export the Server Certificate

Application-side Testing

Import the Certificate on Client

Test the Application

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;
}
}
}
}
}

Test SSMS

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

Analyze the Test Results

Source Code of the Test Application

Summary

Original Source

--

--

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