ZAN KAVTASKIN: Creating Custom Key Store Provider for SQL Always Encrypted (Without Key Vault Example PoC)

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");

        //This will constantly get used
        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;
        }

        //This will never get used by the app, I've used it just to encrypt the column key
        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 encryption key strategies 
            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 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] --Stands for My Own Custom Key Store Provider
 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] -- Stands for My Own Column Encryption Key 1
 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)
        {
            //Register your encryption key strategies 
            Dictionary<string, SqlColumnEncryptionKeyStoreProvider> providerStrategies =
                new Dictionary<string, SqlColumnEncryptionKeyStoreProvider>();

            providerStrategies.Add("MY_OWN_CUSTOM_KEY_STORE_PROVIDER", new MyOwnCustomKeyStoreProvider());


            //Apparently this works transparently with the Hibernate and the Entity Framework!
            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();
                }

                //Show stored data unencrypted 
                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"]}");
                            }
                        }
                    }
                }

                //Equals search, this actually works
                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"]}");
                            }
                        }
                    }
                }

                //Inner Join, this works as well
                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");

        //This will constantly get used
        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;
        }

        //This will never get used by the app, I've used it just to encrypt the column key
        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] --Stands for My Own Custom Key Store Provider
 WITH ( KEY_STORE_PROVIDER_NAME = 'MY_OWN_CUSTOM_KEY_STORE_PROVIDER', 
 KEY_PATH = 'MyKeyStoreWillNotUseThis')

GO 

CREATE COLUMN ENCRYPTION KEY [MO_CEK1] -- Stands for My Own Column Encryption Key 1
 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:

Always Encrypted Database Engine
Creating Custom Azure Key Vault Key Store Provider for Always Encrypted
Always Encrypted works transparently with NHibernate and Entity Framework, however there are limitations

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

6 comments:

Anonymous25 July 2017 at 01:31
Hi,

How to register "MY_OWN_CUSTOM_KEY_STORE_PROVIDER" in SQL Server 2016 ? So that I can create keys from SSME and select "MY_OWN_CUSTOM_KEY_STORE_PROVIDER" as provider.
JohnW15 May 2018 at 07:35
Hi I have been following this article and am stuck on Database Step 2. You state: Encrypted value needs to be some random value that gets encrypted by your master key but how/where do I generate that and how long should it be? Once I have the master key do I pass it through the EncryptColumnEncryptionKey function to get the Encrypted value?
James5 February 2020 at 19:48
Hi,
Thank you for the tutorial, it works fine with the C# environment.
Is it possible to inject the same to use in SSMS and SSRS ?

I could see only possible way for SSMS to work is through Azure key or through certificates.

regards
James

ZAN KAVTASKIN

Pages

Friday 12 August 2016

Creating Custom Key Store Provider for SQL Always Encrypted (Without Key Vault Example PoC)

Setup your C# application

Step 1:

Step 2:

Step 3:

Step 4:

Setup your database

Step 1:

Step 2:

Step 3:

PoC Code

Program.cs

MyOwnCustomKeyStoreProvider.cs

Setup.sql

6 comments: