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.