Always Encrypted is a feature of the SQL Server 2016/Azure SQL which allows you to take full control over the encryption process of the sensitive data stored in your SQL databases. Thanks to this mechanism the encryption key is stored only on the client side and is never revealed to the SQL Server. In consequence, data traveling from the server to the client is also encrypted (although I would not rely too much on this fact and always use encrypted connections to the SQL Server). That is a very different approach to Transparent Data Encryption or Cell-level Encryption, in which it is the server role to encrypt/decrypt data received/sent to the client. Server-side encryption is completely transparent to the client and does not impact the way the client builds SQL queries. In Always Encrypted model, any query against an encrypted column will perform comparisons on byte arrays of cipher text. As you can imagine this raises some challenges when building a data model. In this post, I am going to cover some details of how the Always Encrypted feature is implemented and, hopefully, help you use it effectively.

Creating Encryption Keys

Before we start encrypting data we need to have an encryption key. We need two keys:

• Column Master Key – an asymmetric key known only to the application or to the key vault
• Column Encryption Key – a symmetric key used for encrypting/decrypting data in the SQL tables

The Column Encryption Key is stored on the SQL Server in an encrypted form and we need the Column Master Key to decrypt it. We can peek at the key metadata using the sys.column_encryption_keys and sys.column_encryption_key_values system views:

SQL Server stores also some information about the Column Master Key – you may view them by querying the sys.column_master_keys system view:

SQL Server does not user those values but sends them to the client so it can find the correct key in the key store. The providers implemented in the ADO.NET driver are: MSSQL_CERTIFICATE_STORE, MSSQL_CSP_PROVIDER, and MSSQL_CNG_STORE. You may use a custom provider, but it will be your responsibility to write the key encryption/decryption logic (you need to override the System.Data.SqlClient.SqlColumnEncryptionKeyStoreProvider class). You can learn how to do that by examining the Microsoft.SqlServer.Management.AlwaysEncrypted.AzureKeyVaultProvider Nuget package, which implements the Azure_Key_Vault provider.

I am going to use the MSSQL_CERTIFICATE_STORE, which is probably the best choice for a local machine with no hardware security module (HSM) attached. First, we need to have a certificate with a private key. We may generate it either by using the SQL Server Management Studio or in PowerShell. We will do the latter as the cmdlet also automatically adds the certificate to the Windows Certificate Store:

PS C:> New-SelfSignedCertificate -Subject "AlwaysEncryptedCert" -CertStoreLocation Cert:CurrentUser\My `
    -Type DocumentEncryptionCert -KeyUsage KeyEncipherment -KeySpec KeyExchange -KeyLength 2048

   PSParentPath: Microsoft.PowerShell.Security\Certificate::CurrentUser\My

Thumbprint                                Subject
----------                                -------
723606bf652946fc2ba872d19158c34d94359eff CN=AlwaysEncryptedCert

In the next step we will use the newly created certificate to encrypt the value of the Column Encryption Key – this time in C#:

var columnEncryptionCertProvider = new SqlColumnEncryptionCertificateStoreProvider();

return columnEncryptionCertProvider.EncryptColumnEncryptionKey(
    "CurrentUser/My/723606bf652946fc2ba872d19158c34d94359eff",
    "RSA_OAEP", Encoding.ASCII.GetBytes("hard to guess key 32-byte length"));

The returned byte array is of the following form:

After we created the keys, it is time to inform SQL Server about them (notice we will not reveal the real keys values in any of those commands):

create column master key CMK1
  with (
    key_store_provider_name = N'MSSQL_CERTIFICATE_STORE',
    key_path=N'CurrentUser/My/723606bf652946fc2ba872d19158c34d94359eff'
)

create column encryption key c1
with values
(
    column_master_key = CMK1,
    algorithm = 'RSA_OAEP',
    encrypted_value = 0x016E00000161...{bytes from the encrypted key}...631248
)

Creating the data model

Our sample database will have only one table storing user data. We will mark user’s social number and birth data as sensitive data:

CREATE TABLE Users (
    UserId int IDENTITY(1,1),
    SocialNumber char(11) COLLATE Latin1_General_BIN2
        ENCRYPTED WITH (ENCRYPTION_TYPE = DETERMINISTIC,
                        ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256',
                        COLUMN_ENCRYPTION_KEY = C1) NOT NULL,
    [UserName] [nvarchar](50) NULL,
    BirthDate [date]
        ENCRYPTED WITH (ENCRYPTION_TYPE = RANDOMIZED,
                        ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256',
                        COLUMN_ENCRYPTION_KEY = C1) NOT NULL
    PRIMARY KEY CLUSTERED (UserId ASC)
)

Notice that the Social Number is encrypted deterministically. It means that the same plain text value will always generate the same cipher text. When using AES, it means that the Initialization Vector is based on the plain text value. Code from the Reference Source portal:

byte[] iv = new byte[_BlockSizeInBytes];

// Prepare IV
// Should be 1 single block (16 bytes)
if (_isDeterministic) {
    SqlSecurityUtility.GetHMACWithSHA256(plainText, _columnEncryptionKey.IVKey, iv);
}
else {
    SqlSecurityUtility.GenerateRandomBytes(iv);
}

With the deterministic encryption in place, we may create an index on a given column and filter rows based on its values. For example the LINQ query:

Users.Where(u => u.SocialNumber == "73e8cd1c33b").Select(u => u.UserName)

would not return any results if the column was not deterministically encrypted. Interestingly, ADO.NET does not use the exact value of the Column Encryption Key but creates three derivative keys. Code copied from the Reference Source:

byte[] buff1 = new byte[keySizeInBytes];
SqlSecurityUtility.GetHMACWithSHA256(Encoding.Unicode.GetBytes(encryptionKeySalt), RootKey, buff1);
_encryptionKey = new SqlClientSymmetricKey(buff1);

// Derive mac key
string macKeySalt = string.Format(_macKeySaltFormat, _algorithmName, KeySize);
byte[] buff2 = new byte[keySizeInBytes];
SqlSecurityUtility.GetHMACWithSHA256(Encoding.Unicode.GetBytes(macKeySalt),RootKey,buff2);
_macKey = new SqlClientSymmetricKey(buff2);

// Derive iv key
string ivKeySalt = string.Format(_ivKeySaltFormat, _algorithmName, KeySize);
byte[] buff3 = new byte[keySizeInBytes];
SqlSecurityUtility.GetHMACWithSHA256(Encoding.Unicode.GetBytes(ivKeySalt),RootKey,buff3);
_ivKey = new SqlClientSymmetricKey(buff3);

Writing the application code

When you use the ADO.NET driver, writing code manipulating tables with encrypted columns is surprisingly simple. For instance, look at the code below which inserts a new row into our Users table:

using (var connection = new SqlConnection(
    "Data Source=(localdb)\\ProjectsV13;Database=AlwaysEncrypted;Integrated Security=True;Column Encryption Setting=enabled"))
{
    await connection.OpenAsync();

    var cmd = connection.CreateCommand();

    var socialNumber = Guid.NewGuid().ToString("n").Substring(0, 11);

    cmd.CommandText = "insert into Users (SocialNumber, UserName, BirthDate) values (@SocialNumber, @UserName, @BirthDate)";
    cmd.Parameters.Add(new SqlParameter("@SocialNumber", SqlDbType.Char) { Value = socialNumber });
    cmd.Parameters.Add(new SqlParameter("@UserName", SqlDbType.NVarChar) { Value = "John the Data" });
    cmd.Parameters.Add(new SqlParameter("@BirthDate", SqlDbType.Date) { Value = new DateTime(1970, 12, 9) });

    await cmd.ExecuteNonQueryAsync();
}

The code looks the same as for the non-encrypted table. The only difference is an additional parameter added to the connection string: Column Encryption Setting=enabled. When working with Always Encrypted, you need to remember about adding it.

Examining the application memory

Finally, let’s have a look at the Column Encryption Keys in the process memory. It is enough to search for instances of the SqlAeadAes256CbcHmac256EncryptionKey class:

0:011> .loadby sos clr

0:011> !Name2EE System.Data.dll System.Data.SqlClient.SqlAeadAes256CbcHmac256EncryptionKey
Module:      00007fff2bef1000
Assembly:    System.Data.dll
Token:       000000000200018e
MethodTable: 00007fff2c0befa8
EEClass:     00007fff2bf4f9e8
Name:        System.Data.SqlClient.SqlAeadAes256CbcHmac256EncryptionKey

0:011> !dumpheap -mt 00007fff2c0befa8
         Address               MT     Size
00000154647f8b88 00007fff2c0befa8       56
00000154647f94d8 00007fff2c0befa8       56
0000015464820ca0 00007fff2c0befa8       56
0000015464821060 00007fff2c0befa8       56

Statistics:
              MT    Count    TotalSize Class Name
00007fff2c0befa8        4          224 System.Data.SqlClient.SqlAeadAes256CbcHmac256EncryptionKey
Total 4 objects

0:011> !DumpObj /d 00000154647f8b88
Name:        System.Data.SqlClient.SqlAeadAes256CbcHmac256EncryptionKey
MethodTable: 00007fff2c0befa8
EEClass:     00007fff2bf4f9e8
Size:        56(0x38) bytes
File:        C:\WINDOWS\Microsoft.Net\assembly\GAC_64\System.Data\v4.0_4.0.0.0__b77a5c561934e089\System.Data.dll
Fields:
              MT    Field   Offset                 Type VT     Attr            Value Name
00007fff329cc978  4000e4a        8        System.Byte[]  0 instance 00000154647f6f88 _rootKey
00007fff2c0b0a98  4000e40       10 ...lientSymmetricKey  0 instance 00000154647f9148 _encryptionKey
00007fff2c0b0a98  4000e41       18 ...lientSymmetricKey  0 instance 00000154647f9198 _macKey
00007fff2c0b0a98  4000e42       20 ...lientSymmetricKey  0 instance 00000154647f91e8 _ivKey
00007fff329c6938  4000e43       28        System.String  0 instance 00000154647f70d8 _algorithmName

While checking roots of each of those objects, we will eventually find a cache of the used symmetric keys. it keeps each key in the memory for 2h (we can change this time by modifying the ColumnEncryptionKeyCacheTtl property of the SqlConnection instance). As an exercise, you may decompose the encrypted column value and use the found encryption key to decrypt the ciphertext.