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

Application-side Testing

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.

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

--

--

--

Follow me to keep abreast with the latest technology news, industry insights, and developer trends. Alibaba Cloud website:https://www.alibabacloud.com

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

{UPDATE} Truco Venezolano Playspace Hack Free Resources Generator

{UPDATE} Xtreme Sport Bike Parking Sim Hack Free Resources Generator

Best cyber security services by providers.

The British Airways Hack: JavaScript Weakness Pin-pointed Through Time-lining

New Function on Huobi Wallet | Claim DeFi Rewards with just One Click!

Analyzing obfuscated Powershell with shellcode

MPC Techniques Series, Part 9: SPDZ

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

Alibaba Cloud

Follow me to keep abreast with the latest technology news, industry insights, and developer trends. Alibaba Cloud website:https://www.alibabacloud.com

More from Medium

Is Blue-Green Deployment Strategy just a Hype?

Interview with Tom Granot — Developer Observability, KoolKits and Reliability

Fundamentals of Liquibase

How to set up multiple INPUT, OUTPUT in Fluent Bit?