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<br>    {<br><br>        string masterKeyThatWillNotBeHardcodedInYourApp = "someMasterKey";<br>        byte[] saltThatWillNotBeHardcodedInYourApp = UTF8Encoding.UTF8.GetBytes("someSalt");<br><br>        //This will constantly get used<br>        public override byte[] DecryptColumnEncryptionKey(string masterKeyPath, string encryptionAlgorithm, byte[] encryptedColumnEncryptionKey)<br>        {<br>            using (MemoryStream ms = new MemoryStream())<br>            {<br>                using (RijndaelManaged AES = new RijndaelManaged())<br>                {<br>                    AES.KeySize = 256;<br>                    AES.BlockSize = 128;<br><br>                    Rfc2898DeriveBytes keyBytes = new Rfc2898DeriveBytes(<br>                            masterKeyThatWillNotBeHardcodedInYourApp, <br>                            saltThatWillNotBeHardcodedInYourApp, <br>                            1000<br>                       );<br>                    AES.Key = keyBytes.GetBytes(AES.KeySize / 8);<br>                    AES.IV = keyBytes.GetBytes(AES.BlockSize / 8);<br><br>                    AES.Mode = CipherMode.CBC;<br><br>                    using (CryptoStream cs = new CryptoStream(ms, AES.CreateDecryptor(), CryptoStreamMode.Write))<br>                    {<br>                        cs.Write(encryptedColumnEncryptionKey, 0, encryptedColumnEncryptionKey.Length);<br>                        cs.Close();<br>                    }<br>                    encryptedColumnEncryptionKey = ms.ToArray();<br>                }<br>            }<br><br>            return encryptedColumnEncryptionKey;<br>        }<br><br>        //This will never get used by the app, I've used it just to encrypt the column key<br>        public override byte[] EncryptColumnEncryptionKey(string masterKeyPath, string encryptionAlgorithm, byte[] columnEncryptionKey)<br>        {<br>            byte[] encryptedBytes = null;<br>            using (MemoryStream ms = new MemoryStream())<br>            {<br>                using (RijndaelManaged AES = new RijndaelManaged())<br>                {<br>                    AES.KeySize = 256;<br>                    AES.BlockSize = 128;<br><br>                    Rfc2898DeriveBytes keyBytes = new Rfc2898DeriveBytes(<br>                            masterKeyThatWillNotBeHardcodedInYourApp,<br>                            saltThatWillNotBeHardcodedInYourApp,<br>                            1000<br>                       );<br><br>                    AES.Key = keyBytes.GetBytes(AES.KeySize / 8);<br>                    AES.IV = keyBytes.GetBytes(AES.BlockSize / 8);<br><br>                    AES.Mode = CipherMode.CBC;<br><br>                    using (CryptoStream cs = new CryptoStream(ms, AES.CreateEncryptor(), CryptoStreamMode.Write))<br>                    {<br>                        cs.Write(columnEncryptionKey, 0, columnEncryptionKey.Length);<br>                        cs.Close();<br>                    }<br>                    encryptedBytes = ms.ToArray();<br>                }<br>            }<br><br>            return encryptedBytes;<br>        }<br>    }<br>

Step 3:

Register your provider with the SqlConnection:

//Register your encryption key strategies <br>            Dictionary<string, SqlColumnEncryptionKeyStoreProvider> providerStrategies =<br>                new Dictionary<string, SqlColumnEncryptionKeyStoreProvider>();<br><br>            providerStrategies.Add("MY_OWN_CUSTOM_KEY_STORE_PROVIDER", new MyOwnCustomKeyStoreProvider());<br><br>            SqlConnection.RegisterColumnEncryptionKeyStoreProviders(providerStrategies);<br>

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

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<br> WITH ( KEY_STORE_PROVIDER_NAME = 'MY_OWN_CUSTOM_KEY_STORE_PROVIDER', <br> KEY_PATH = 'MyKeyStoreWillNotUseThis')<br>

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<br> WITH VALUES<br> (<br>  COLUMN_MASTER_KEY = [MO_CMKSP],<br>  ALGORITHM = 'RSA_OAEP',<br>  ENCRYPTED_VALUE = 0x29128e12266a71dd098bc3223b3bbf293a275b2ec8c13f97515f54dd7d2a54af46f37071e0e16e777d73f4a743ddb991<br> )<br>

Step 3:

Encrypt columns by specifying the column encryption key:

CREATE TABLE [dbo].[Employee](<br>  [Id] [int] IDENTITY(1,1) NOT NULL,<br>  [SSN] [nvarchar](11) COLLATE Latin1_General_BIN2<br>  ENCRYPTED WITH (<br>   COLUMN_ENCRYPTION_KEY = [MO_CEK1],<br>   ENCRYPTION_TYPE = Deterministic,<br>   ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'<br>  ) NOT NULL,<br>  [Salary][int] <br>  ENCRYPTED WITH (<br>   COLUMN_ENCRYPTION_KEY = [MO_CEK1],<br>   ENCRYPTION_TYPE = RANDOMIZED,<br>   ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'<br>  ) NOT NULL,<br>  PRIMARY KEY CLUSTERED<br>  (<br>  [Id] ASC<br>  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]<br> ) ON [PRIMARY]<br><br> CREATE TABLE [dbo].[EmployeeExtraInformation](<br>  [Id] [int] IDENTITY(1,1) NOT NULL,<br>  [EyeColor] [nvarchar](11) NOT NULL,<br>  [SSN] [nvarchar](11) COLLATE Latin1_General_BIN2<br>  ENCRYPTED WITH (<br>   COLUMN_ENCRYPTION_KEY = [MO_CEK1],<br>   ENCRYPTION_TYPE = Deterministic,<br>   ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'<br>  ) NOT NULL,<br>  PRIMARY KEY CLUSTERED<br>  (<br>  [Id] ASC<br>  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]<br> ) ON [PRIMARY]<br>

PoC Code

Program.cs

using System;<br>using System.Collections.Generic;<br>using System.Data.SqlClient;<br><br>namespace CustomKeyStoreProvider<br>{<br>    class Program<br>    {<br>        static void Main(string[] args)<br>        {<br>            //Register your encryption key strategies <br>            Dictionary<string, SqlColumnEncryptionKeyStoreProvider> providerStrategies =<br>                new Dictionary<string, SqlColumnEncryptionKeyStoreProvider>();<br><br>            providerStrategies.Add("MY_OWN_CUSTOM_KEY_STORE_PROVIDER", new MyOwnCustomKeyStoreProvider());<br><br><br>            //Apparently this works transparently with the Hibernate and the Entity Framework!<br>            SqlConnection.RegisterColumnEncryptionKeyStoreProviders(providerStrategies);<br><br>            using (SqlConnection connection = new SqlConnection({Your connection string};Column Encryption Setting=Enabled))<br>            {<br>                connection.Open();<br>                string ssn;<br>                using (SqlCommand command = connection.CreateCommand())<br>                {<br>                    command.CommandText = "INSERT INTO [dbo].[Employee] VALUES (@ssn, @salary)";<br>                    Random rand = new Random();<br>                    ssn = string.Format(@"{0:d3}-{1:d2}-{2:d4}", rand.Next(0, 1000), rand.Next(0, 100), rand.Next(0, 10000));<br>                    command.Parameters.AddWithValue("@ssn", ssn);<br>                    command.Parameters.AddWithValue("@salary", 18000);<br>                    command.ExecuteNonQuery();<br>                }<br><br>                using (SqlCommand command = connection.CreateCommand())<br>                {<br>                    command.CommandText = "INSERT INTO [dbo].[EmployeeExtraInformation] (eyecolor, ssn) VALUES (@eyecolor, @ssn)";<br>                    command.Parameters.AddWithValue("@eyecolor", "blue");<br>                    command.Parameters.AddWithValue("@ssn", ssn);<br>                    command.ExecuteNonQuery();<br>                }<br><br>                //Show stored data unencrypted <br>                using (SqlCommand command = connection.CreateCommand())<br>                {<br>                    command.CommandText = "SELECT [id], [ssn], [salary] FROM [dbo].[Employee]";<br>                    using (SqlDataReader reader = command.ExecuteReader())<br>                    {<br>                        if(reader.HasRows)<br>                        {<br>                            Console.WriteLine("-- Showing all rows:");<br>                            while (reader.Read())<br>                            {<br>                                Console.WriteLine($"id : {reader["id"]}, ssn : {reader["ssn"]}, salary : {reader["salary"]}");<br>                            }<br>                        }<br>                    }<br>                }<br><br>                //Equals search, this actually works<br>                using(SqlCommand command = connection.CreateCommand())<br>                {<br>                    command.CommandText = "SELECT [id], [ssn], [salary] FROM [dbo].[Employee] WHERE [ssn] = @ssn";<br>                    command.Parameters.AddWithValue("@ssn", ssn);<br><br>                    using (SqlDataReader reader = command.ExecuteReader())<br>                    {<br>                        if (reader.HasRows)<br>                        {<br>                            Console.WriteLine($"-- Showing found record for ssn {ssn}:");<br>                            while (reader.Read())<br>                            {<br>                                Console.WriteLine($"id : {reader["id"]}, ssn : {reader["ssn"]}, salary : {reader["salary"]}");<br>                            }<br>                        }<br>                    }<br>                }<br><br>                //Inner Join, this works as well<br>                using (SqlCommand command = connection.CreateCommand())<br>                {<br>                    command.CommandText = @"SELECT [dbo].[Employee].[salary], [dbo].[Employee].[ssn], [dbo].[EmployeeExtraInformation].[eyecolor] FROM [dbo].[Employee] <br>                                                    INNER JOIN [dbo].[EmployeeExtraInformation] ON [dbo].[Employee].[ssn] = [dbo].[EmployeeExtraInformation].[ssn]";<br><br>                    using (SqlDataReader reader = command.ExecuteReader())<br>                    {<br>                        if (reader.HasRows)<br>                        {<br>                            Console.WriteLine($"-- Showing all records inner joined:");<br>                            while (reader.Read())<br>                            {<br>                                Console.WriteLine($"eyecolor : {reader["eyecolor"]}, ssn : {reader["ssn"]}, salary : {reader["salary"]}");<br>                            }<br>                        }<br>                    }<br>                }<br><br>                try<br>                {<br>                    using (SqlCommand command = connection.CreateCommand())<br>                    {<br>                        command.CommandText = "SELECT [id], [ssn], [salary] FROM [dbo].[Employee] WHERE [ssn] like @ssn";<br>                        command.Parameters.AddWithValue("@ssn", ssn);<br><br>                        command.ExecuteReader();<br>                    }<br>                }<br>                catch (Exception ex)<br>                {<br>                    Console.WriteLine("-- As expected, can't search on ssn using like:");<br>                    Console.WriteLine(ex.Message);<br>                }<br><br>                try<br>                {<br>                    using (SqlCommand command = connection.CreateCommand())<br>                    {<br>                        command.CommandText = "SELECT [id], [ssn], [salary] FROM [dbo].[Employee] WHERE [salary] = @salary";<br>                        command.Parameters.AddWithValue("@salary", 18000);<br><br>                        command.ExecuteReader();<br>                    }<br>                }<br>                catch(Exception ex)<br>                {<br>                    Console.WriteLine("-- As expected, can't search on salary, it is a randomized field:");<br>                    Console.WriteLine(ex.Message);<br>                }<br><br>                connection.Close();<br>            }<br><br>            Console.ReadLine(); <br>        }<br>    }<br>}<br>

MyOwnCustomKeyStoreProvider.cs

using System.Data.SqlClient;<br>using System.IO;<br>using System.Security.Cryptography;<br>using System.Text;<br><br>namespace CustomKeyStoreProvider<br>{<br>    public class MyOwnCustomKeyStoreProvider : SqlColumnEncryptionKeyStoreProvider<br>    {<br><br>        string masterKeyThatWillNotBeHardcodedInYourApp = "someMasterKey";<br>        byte[] saltThatWillNotBeHardcodedInYourApp = UTF8Encoding.UTF8.GetBytes("someSalt");<br><br>        //This will constantly get used<br>        public override byte[] DecryptColumnEncryptionKey(string masterKeyPath, string encryptionAlgorithm, byte[] encryptedColumnEncryptionKey)<br>        {<br>            using (MemoryStream ms = new MemoryStream())<br>            {<br>                using (RijndaelManaged AES = new RijndaelManaged())<br>                {<br>                    AES.KeySize = 256;<br>                    AES.BlockSize = 128;<br><br>                    Rfc2898DeriveBytes keyBytes = new Rfc2898DeriveBytes(<br>                            masterKeyThatWillNotBeHardcodedInYourApp, <br>                            saltThatWillNotBeHardcodedInYourApp, <br>                            1000<br>                       );<br>                    AES.Key = keyBytes.GetBytes(AES.KeySize / 8);<br>                    AES.IV = keyBytes.GetBytes(AES.BlockSize / 8);<br><br>                    AES.Mode = CipherMode.CBC;<br><br>                    using (CryptoStream cs = new CryptoStream(ms, AES.CreateDecryptor(), CryptoStreamMode.Write))<br>                    {<br>                        cs.Write(encryptedColumnEncryptionKey, 0, encryptedColumnEncryptionKey.Length);<br>                        cs.Close();<br>                    }<br>                    encryptedColumnEncryptionKey = ms.ToArray();<br>                }<br>            }<br><br>            return encryptedColumnEncryptionKey;<br>        }<br><br>        //This will never get used by the app, I've used it just to encrypt the column key<br>        public override byte[] EncryptColumnEncryptionKey(string masterKeyPath, string encryptionAlgorithm, byte[] columnEncryptionKey)<br>        {<br>            byte[] encryptedBytes = null;<br>            using (MemoryStream ms = new MemoryStream())<br>            {<br>                using (RijndaelManaged AES = new RijndaelManaged())<br>                {<br>                    AES.KeySize = 256;<br>                    AES.BlockSize = 128;<br><br>                    Rfc2898DeriveBytes keyBytes = new Rfc2898DeriveBytes(<br>                            masterKeyThatWillNotBeHardcodedInYourApp,<br>                            saltThatWillNotBeHardcodedInYourApp,<br>                            1000<br>                       );<br><br>                    AES.Key = keyBytes.GetBytes(AES.KeySize / 8);<br>                    AES.IV = keyBytes.GetBytes(AES.BlockSize / 8);<br><br>                    AES.Mode = CipherMode.CBC;<br><br>                    using (CryptoStream cs = new CryptoStream(ms, AES.CreateEncryptor(), CryptoStreamMode.Write))<br>                    {<br>                        cs.Write(columnEncryptionKey, 0, columnEncryptionKey.Length);<br>                        cs.Close();<br>                    }<br>                    encryptedBytes = ms.ToArray();<br>                }<br>            }<br><br>            return encryptedBytes;<br>        }<br>    }<br>}<br><br>

Setup.sql

CREATE COLUMN MASTER KEY [MO_CMKSP] --Stands for My Own Custom Key Store Provider<br> WITH ( KEY_STORE_PROVIDER_NAME = 'MY_OWN_CUSTOM_KEY_STORE_PROVIDER', <br> KEY_PATH = 'MyKeyStoreWillNotUseThis')<br><br>GO <br><br>CREATE COLUMN ENCRYPTION KEY [MO_CEK1] -- Stands for My Own Column Encryption Key 1<br> WITH VALUES<br> (<br>  COLUMN_MASTER_KEY = [MO_CMKSP],<br>  ALGORITHM = 'RSA_OAEP',<br>  ENCRYPTED_VALUE = 0x29128e12266a71dd098bc3223b3bbf293a275b2ec8c13f97515f54dd7d2a54af46f37071e0e16e777d73f4a743ddb991<br> )<br>GO<br><br>CREATE TABLE [dbo].[Employee](<br>  [Id] [int] IDENTITY(1,1) NOT NULL,<br>  [SSN] [nvarchar](11) COLLATE Latin1_General_BIN2<br>  ENCRYPTED WITH (<br>   COLUMN_ENCRYPTION_KEY = [MO_CEK1],<br>   ENCRYPTION_TYPE = Deterministic,<br>   ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'<br>  ) NOT NULL,<br>  [Salary][int] <br>  ENCRYPTED WITH (<br>   COLUMN_ENCRYPTION_KEY = [MO_CEK1],<br>   ENCRYPTION_TYPE = RANDOMIZED,<br>   ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'<br>  ) NOT NULL,<br>  PRIMARY KEY CLUSTERED<br>  (<br>  [Id] ASC<br>  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]<br> ) ON [PRIMARY]<br><br> CREATE TABLE [dbo].[EmployeeExtraInformation](<br>  [Id] [int] IDENTITY(1,1) NOT NULL,<br>  [EyeColor] [nvarchar](11) NOT NULL,<br>  [SSN] [nvarchar](11) COLLATE Latin1_General_BIN2<br>  ENCRYPTED WITH (<br>   COLUMN_ENCRYPTION_KEY = [MO_CEK1],<br>   ENCRYPTION_TYPE = Deterministic,<br>   ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'<br>  ) NOT NULL,<br>  PRIMARY KEY CLUSTERED<br>  (<br>  [Id] ASC<br>  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]<br> ) ON [PRIMARY]<br>

Useful links:
CodeProject