Friday 12 August 2016

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.

Custom Key Store Provider for SQL Always Encrypted


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 provider with the SqlConnection:

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

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

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

    ReplyDelete
    Replies
    1. Hello Rohit,

      Thank you for the question!

      Can you please refine your question, I am not sure what you are asking.

      Delete
    2. Hi Zan,

      the above POC works fine for creating new table and inserting new rows in it but
      in my case I want to encrypt the exiting table data.
      In this case the moment I encrypt the column with the encryption key then SQL Server management studio (SSMS) throws below error

      "TaskUpdates: Message:Task: 'Performing encryption operations' -- Status: 'Failed' -- Details: 'Task failed due to following error: Failed to decrypt a column encryption key. Invalid key store provider name: 'Vormetric Key Storage Provider'. A key store provider name must denote either a system key store provider or a registered custom key store provider. Valid system key store provider names are: 'MSSQL_CERTIFICATE_STORE', 'MSSQL_CNG_STORE', 'MSSQL_CSP_PROVIDER'. Valid (currently registered) custom key store provider names are: 'AZURE_KEY_VAULT'. Please verify key store provider information in column master key definitions in the database, and verify all custom key store providers used in your application are registered properly.'"

      I guess this is because still my custom provider is not physically registered with SSMS yet.
      Do you have any idea how to register and integrate the custom provider in SSMS under CNG tab? so that I can encrypt the existing table's data

      Delete
    3. Hello,

      I don't think this is possible when you use custom key store option. Remember that only your application can actually decrypt the master key. If you are trying to do this in SSMS it will fail as it does not have the master key. I think that your only option here is to create a new encrypted column and migrate data over into it.

      Let me know how it goes.

      Delete
  2. 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?

    ReplyDelete
  3. 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

    ReplyDelete