Recently, we had to implement custom key store provider for always encrypted. We wanted it to access our own key store to retrieve the master key and to decrypt the column key. It was not very clear how this can be achieved. So I've decided to produce a PoC and write an article about it.
Setup Your C# Application
Step 1
Make sure your project is set to .NET Framework 4.6.
Step 2
Implement your own custom store provider by extending the SqlColumnEncryptionKeyStoreProvider
and overriding the two methods:
public class MyOwnCustomKeyStoreProvider : SqlColumnEncryptionKeyStoreProvider
{
string masterKeyThatWillNotBeHardcodedInYourApp = "someMasterKey";
byte[] saltThatWillNotBeHardcodedInYourApp = UTF8Encoding.UTF8.GetBytes("someSalt");
public override byte[] DecryptColumnEncryptionKey
(string masterKeyPath, string encryptionAlgorithm, byte[] encryptedColumnEncryptionKey)
{
using (MemoryStream ms = new MemoryStream())
{
using (RijndaelManaged AES = new RijndaelManaged())
{
AES.KeySize = 256;
AES.BlockSize = 128;
Rfc2898DeriveBytes keyBytes = new Rfc2898DeriveBytes(
masterKeyThatWillNotBeHardcodedInYourApp,
saltThatWillNotBeHardcodedInYourApp,
1000
);
AES.Key = keyBytes.GetBytes(AES.KeySize / 8);
AES.IV = keyBytes.GetBytes(AES.BlockSize / 8);
AES.Mode = CipherMode.CBC;
using (CryptoStream cs = new CryptoStream
(ms, AES.CreateDecryptor(), CryptoStreamMode.Write))
{
cs.Write(encryptedColumnEncryptionKey, 0, encryptedColumnEncryptionKey.Length);
cs.Close();
}
encryptedColumnEncryptionKey = ms.ToArray();
}
}
return encryptedColumnEncryptionKey;
}
public override byte[] EncryptColumnEncryptionKey
(string masterKeyPath, string encryptionAlgorithm, byte[] columnEncryptionKey)
{
byte[] encryptedBytes = null;
using (MemoryStream ms = new MemoryStream())
{
using (RijndaelManaged AES = new RijndaelManaged())
{
AES.KeySize = 256;
AES.BlockSize = 128;
Rfc2898DeriveBytes keyBytes = new Rfc2898DeriveBytes(
masterKeyThatWillNotBeHardcodedInYourApp,
saltThatWillNotBeHardcodedInYourApp,
1000
);
AES.Key = keyBytes.GetBytes(AES.KeySize / 8);
AES.IV = keyBytes.GetBytes(AES.BlockSize / 8);
AES.Mode = CipherMode.CBC;
using (CryptoStream cs = new CryptoStream
(ms, AES.CreateEncryptor(), CryptoStreamMode.Write))
{
cs.Write(columnEncryptionKey, 0, columnEncryptionKey.Length);
cs.Close();
}
encryptedBytes = ms.ToArray();
}
}
return encryptedBytes;
}
}
Step 3
Register your provider with the SqlConnection
:
Dictionary<string, SqlColumnEncryptionKeyStoreProvider> providerStrategies =
new Dictionary<string, SqlColumnEncryptionKeyStoreProvider>();
providerStrategies.Add("MY_OWN_CUSTOM_KEY_STORE_PROVIDER", new MyOwnCustomKeyStoreProvider());
SqlConnection.RegisterColumnEncryptionKeyStoreProviders(providerStrategies);
Step 4
Now, pay attention. Make sure that your connection is configured correctly, I've spent several hours trying to figure out why my setup was not working. It was all because I did not include "Column Encryption Setting=Enabled
" in the connection string:
new SqlConnection("Server=tcp:some.database.windows.net,1433;
Database=testing;User ID=testing@testing;Password=Password;Trusted_Connection=False;
Encrypt=True;Connection Timeout=30;Column Encryption Setting=Enabled")
If you don't include Column Encryption Setting=Enabled, you will get an unhelpful exception like this:
An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll
Additional information: Operand type clash: nvarchar is incompatible with nvarchar(11) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'MO_CEK1', column_encryption_key_database_name = 'sometest')
Incorrect parameter encryption metadata was received from the client. The error occurred during the invocation of the batch and therefore the client can refresh the parameter encryption metadata by calling sp_describe_parameter_encryption and retry.
Setup Your Database
Step 1
Define your custom key store provider:
CREATE COLUMN MASTER KEY [MO_CMKSP]
WITH ( KEY_STORE_PROVIDER_NAME = 'MY_OWN_CUSTOM_KEY_STORE_PROVIDER',
KEY_PATH = 'MyKeyStoreWillNotUseThis')
Step 2
Define the column encryption key that will get unwrapped by your own custom key store provider. Encrypted value needs to be some random value that gets encrypted by your master key and stored here as a hexadecimal:
CREATE COLUMN ENCRYPTION KEY [MO_CEK1]
WITH VALUES
(
COLUMN_MASTER_KEY = [MO_CMKSP],
ALGORITHM = 'RSA_OAEP',
ENCRYPTED_VALUE = _
0x29128e12266a71dd098bc3223b3bbf293a275b2ec8c13f97515f54dd7d2a54af46f37071e0e16e777d73f4a743ddb991
)
Step 3
Encrypt columns by specifying the column encryption key:
CREATE TABLE [dbo].[Employee](
[Id] [int] IDENTITY(1,1) NOT NULL,
[SSN] [nvarchar](11) COLLATE Latin1_General_BIN2
ENCRYPTED WITH (
COLUMN_ENCRYPTION_KEY = [MO_CEK1],
ENCRYPTION_TYPE = Deterministic,
ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
) NOT NULL,
[Salary][int]
ENCRYPTED WITH (
COLUMN_ENCRYPTION_KEY = [MO_CEK1],
ENCRYPTION_TYPE = RANDOMIZED,
ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
) NOT NULL,
PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, _
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[EmployeeExtraInformation](
[Id] [int] IDENTITY(1,1) NOT NULL,
[EyeColor] [nvarchar](11) NOT NULL,
[SSN] [nvarchar](11) COLLATE Latin1_General_BIN2
ENCRYPTED WITH (
COLUMN_ENCRYPTION_KEY = [MO_CEK1],
ENCRYPTION_TYPE = Deterministic,
ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
) NOT NULL,
PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, _
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
PoC Code
Program.cs
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
namespace CustomKeyStoreProvider
{
class Program
{
static void Main(string[] args)
{
Dictionary<string, SqlColumnEncryptionKeyStoreProvider> providerStrategies =
new Dictionary<string, SqlColumnEncryptionKeyStoreProvider>();
providerStrategies.Add("MY_OWN_CUSTOM_KEY_STORE_PROVIDER",
new MyOwnCustomKeyStoreProvider());
SqlConnection.RegisterColumnEncryptionKeyStoreProviders(providerStrategies);
using (SqlConnection connection = new SqlConnection({Your connection string};
Column Encryption Setting=Enabled))
{
connection.Open();
string ssn;
using (SqlCommand command = connection.CreateCommand())
{
command.CommandText = _
"INSERT INTO [dbo].[Employee] VALUES (@ssn, @salary)";
Random rand = new Random();
ssn = string.Format(@"{0:d3}-{1:d2}-{2:d4}", rand.Next(0, 1000),
rand.Next(0, 100), rand.Next(0, 10000));
command.Parameters.AddWithValue("@ssn", ssn);
command.Parameters.AddWithValue("@salary", 18000);
command.ExecuteNonQuery();
}
using (SqlCommand command = connection.CreateCommand())
{
command.CommandText = "INSERT INTO [dbo].[EmployeeExtraInformation]
(eyecolor, ssn) VALUES (@eyecolor, @ssn)";
command.Parameters.AddWithValue("@eyecolor", "blue");
command.Parameters.AddWithValue("@ssn", ssn);
command.ExecuteNonQuery();
}
using (SqlCommand command = connection.CreateCommand())
{
command.CommandText = "SELECT [id], [ssn],
[salary] FROM [dbo].[Employee]";
using (SqlDataReader reader = command.ExecuteReader())
{
if(reader.HasRows)
{
Console.WriteLine("-- Showing all rows:");
while (reader.Read())
{
Console.WriteLine($"id : {reader["id"]},
ssn : {reader["ssn"]},
salary : {reader["salary"]}");
}
}
}
}
using(SqlCommand command = connection.CreateCommand())
{
command.CommandText = "SELECT [id], [ssn], [salary]
FROM [dbo].[Employee] WHERE [ssn] = @ssn";
command.Parameters.AddWithValue("@ssn", ssn);
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
Console.WriteLine($"-- Showing found record for ssn {ssn}:");
while (reader.Read())
{
Console.WriteLine($"id : {reader["id"]},
ssn : {reader["ssn"]},
_salary : {reader["salary"]}");
}
}
}
}
using (SqlCommand command = connection.CreateCommand())
{
command.CommandText = @"SELECT [dbo].[Employee].[salary],
[dbo].[Employee].[ssn], [dbo].[EmployeeExtraInformation].[eyecolor] _
FROM [dbo].[Employee]
INNER JOIN [dbo].[EmployeeExtraInformation] _
ON [dbo].[Employee].[ssn] = [dbo].[EmployeeExtraInformation].[ssn]";
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
Console.WriteLine($"-- Showing all records inner joined:");
while (reader.Read())
{
Console.WriteLine($"eyecolor :
{reader["eyecolor"]},
ssn : {reader["ssn"]},
salary : {reader["salary"]}");
}
}
}
}
try
{
using (SqlCommand command = connection.CreateCommand())
{
command.CommandText = "SELECT [id], [ssn],
[salary] FROM [dbo].[Employee] WHERE [ssn] like @ssn";
command.Parameters.AddWithValue("@ssn", ssn);
command.ExecuteReader();
}
}
catch (Exception ex)
{
Console.WriteLine("-- As expected, can't search on ssn using like:");
Console.WriteLine(ex.Message);
}
try
{
using (SqlCommand command = connection.CreateCommand())
{
command.CommandText = "SELECT [id],
[ssn], [salary] FROM [dbo].[Employee] WHERE [salary] = @salary";
command.Parameters.AddWithValue("@salary", 18000);
command.ExecuteReader();
}
}
catch(Exception ex)
{
Console.WriteLine("-- As expected, _
can't search on salary, it is a randomized field:");
Console.WriteLine(ex.Message);
}
connection.Close();
}
Console.ReadLine();
}
}
}
MyOwnCustomKeyStoreProvider.cs
using System.Data.SqlClient;
using System.IO;
using System.Security.Cryptography;
using System.Text;
namespace CustomKeyStoreProvider
{
public class MyOwnCustomKeyStoreProvider : SqlColumnEncryptionKeyStoreProvider
{
string masterKeyThatWillNotBeHardcodedInYourApp = "someMasterKey";
byte[] saltThatWillNotBeHardcodedInYourApp =
UTF8Encoding.UTF8.GetBytes("someSalt");
public override byte[] DecryptColumnEncryptionKey
(string masterKeyPath,
string encryptionAlgorithm, byte[] encryptedColumnEncryptionKey)
{
using (MemoryStream ms = new MemoryStream())
{
using (RijndaelManaged AES = new RijndaelManaged())
{
AES.KeySize = 256;
AES.BlockSize = 128;
Rfc2898DeriveBytes keyBytes = new Rfc2898DeriveBytes(
masterKeyThatWillNotBeHardcodedInYourApp,
saltThatWillNotBeHardcodedInYourApp,
1000
);
AES.Key = keyBytes.GetBytes(AES.KeySize / 8);
AES.IV = keyBytes.GetBytes(AES.BlockSize / 8);
AES.Mode = CipherMode.CBC;
using (CryptoStream cs =
new CryptoStream(ms, AES.CreateDecryptor(), CryptoStreamMode.Write))
{
cs.Write(encryptedColumnEncryptionKey, 0, encryptedColumnEncryptionKey.Length);
cs.Close();
}
encryptedColumnEncryptionKey = ms.ToArray();
}
}
return encryptedColumnEncryptionKey;
}
public override byte[] EncryptColumnEncryptionKey
(string masterKeyPath, string encryptionAlgorithm, byte[] columnEncryptionKey)
{
byte[] encryptedBytes = null;
using (MemoryStream ms = new MemoryStream())
{
using (RijndaelManaged AES = new RijndaelManaged())
{
AES.KeySize = 256;
AES.BlockSize = 128;
Rfc2898DeriveBytes keyBytes = new Rfc2898DeriveBytes(
masterKeyThatWillNotBeHardcodedInYourApp,
saltThatWillNotBeHardcodedInYourApp,
1000
);
AES.Key = keyBytes.GetBytes(AES.KeySize / 8);
AES.IV = keyBytes.GetBytes(AES.BlockSize / 8);
AES.Mode = CipherMode.CBC;
using (CryptoStream cs = _
new CryptoStream(ms, AES.CreateEncryptor(), CryptoStreamMode.Write))
{
cs.Write(columnEncryptionKey, 0, columnEncryptionKey.Length);
cs.Close();
}
encryptedBytes = ms.ToArray();
}
}
return encryptedBytes;
}
}
}
Setup.sql
CREATE COLUMN MASTER KEY [MO_CMKSP]
WITH ( KEY_STORE_PROVIDER_NAME = 'MY_OWN_CUSTOM_KEY_STORE_PROVIDER',
KEY_PATH = 'MyKeyStoreWillNotUseThis')
GO
CREATE COLUMN ENCRYPTION KEY [MO_CEK1]
WITH VALUES
(
COLUMN_MASTER_KEY = [MO_CMKSP],
ALGORITHM = 'RSA_OAEP',
ENCRYPTED_VALUE = _
0x29128e12266a71dd098bc3223b3bbf293a275b2ec8c13f97515f54dd7d2a54af46f37071e0e16e777d73f4a743ddb991
)
GO
CREATE TABLE [dbo].[Employee](
[Id] [int] IDENTITY(1,1) NOT NULL,
[SSN] [nvarchar](11) COLLATE Latin1_General_BIN2
ENCRYPTED WITH (
COLUMN_ENCRYPTION_KEY = [MO_CEK1],
ENCRYPTION_TYPE = Deterministic,
ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
) NOT NULL,
[Salary][int]
ENCRYPTED WITH (
COLUMN_ENCRYPTION_KEY = [MO_CEK1],
ENCRYPTION_TYPE = RANDOMIZED,
ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
) NOT NULL,
PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, _
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[EmployeeExtraInformation](
[Id] [int] IDENTITY(1,1) NOT NULL,
[EyeColor] [nvarchar](11) NOT NULL,
[SSN] [nvarchar](11) COLLATE Latin1_General_BIN2
ENCRYPTED WITH (
COLUMN_ENCRYPTION_KEY = [MO_CEK1],
ENCRYPTION_TYPE = Deterministic,
ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
) NOT NULL,
PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, _
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Useful Links
*Note: Code in this article is not production ready and is used for prototyping purposes only. If you have suggestions or feedback, please do comment.
CodeProject