Always Encrypted Key Metadata

  • Article

Note: this article was modified on Nov 1st, 2015 to reflect syntax changes in T-SQL DDL and metadata views, introduced in SQL Server 2016 CTP3. Please, refer to SQL Server 2016 Release Notes for details.

In the previous articles on Always Encrypted, we used two sample applications (a console app and an ASP .NET web app) to demonstrate how to use Always Encrypted to protect sensitive information stored in a database. We showed a process for setting up Always Encrypted, including configuring the keys – a column master key and a column encryption key, in the database and on the client machine. Following up on your questions for the previous posts, today we will explore the role the metadata for the keys plays when an application queries encrypted columns.

Column Encryption Key Metadata

In the example from our Getting Started with Always Encrypted blog article, we used the SSMS UI to provision a pair of keys: a column master key and a column encryption key. We created metadata objects for both keys in the database: a column master key object, named CMK1, and a column encryption key object, named CEK1. Now, let us use SSMS to script the CREATE statements for these objects to see information they contain.

Scripting CEK1 produces the following T-SQL statement:

 CREATE COLUMN ENCRYPTION KEY [CEK1]
 WITH VALUES
 (
 COLUMN_MASTER_KEY = [CMK1],
 ALGORITHM = 'RSA_OAEP',
 ENCRYPTED_VALUE = 0x0170...
 )
 GO

The above statement specifies the name of the column encryption key and a tuple (enclosed in parentheses and following the WITH VALUES clause) that includes the following information:

  • Name of the column master key protecting the value of the column encryption key.
  • Algorithm used to generate the encrypted value of the column encryption key (RSA_OAEP).
  • Encrypted value of the column encryption key. The encrypted value is assumed to be produced by encrypting the plaintext of the column encryption key using the specified column master key and the specified algorithm.

Note that a column encryption key can have up to two encrypted values (the above key has only one encrypted value), each of which must be encrypted using a different column master key. In other words, the CREATE COLUMN ENCRYPTION KEY statement can contain up to two tuples following the WITH VALUES clause, each for a different encrypted value of the same column encryption key. For most of the time, a column encryption key needs to have only one encrypted value. However, for the purpose of a column master key rotation, you may add the second encrypted value (using the ALTER COLUMN ENCRYPTION KEY statement) with a different column master key. We will cover the topic of a column master key rotation in a later blog article.

Column Master Key Metadata

In our first blog example, we also created a column master key, named CMK1. Scripting that column master key results in the following T-SQL:

 CREATE COLUMN MASTER KEY [CMK1] 
 WITH (
 KEY_STORE_PROVIDER_NAME = N'MSSQL_CERTIFICATE_STORE'
 , KEY_PATH = N'CurrentUser/My/6ABA1BF8288746CE4A617055588097A2242FED2A' 
 )

The two attributes of a column master key stored in the database are:

  • Name of a provider for the key store that holds the column master key.
  • Key path specifying the location of the column master key in the key store.

The key store provider name is simply a lookup value, SqlClient in ADO.NET uses it to find an implementation of the provider that encapsulates the key store. SqlClient comes with a few built-in providers. One of them is the provider, named MSSQL_CERTIFICATE_STORE for Windows Certificate Store. You can, however, register your own key store providers for other key stores. (We will discuss implementing and setting up custom key store providers in a future post.)

A key path has a format that is specific to the key store provider. For the MSSQL_CERTIFICATE_STORE key store provider, a key path consists of the Certificate Store name (CurrentUser or LocalMachine), a store location (My) and a certificate thumbprint.

Querying Always Encrypted Metadata

Column encryption key metadata is stored in two catalog views: sys.column_encryption_keys and sys.column_encryption_key_values. Column master key metadata is stored in the sys.column_master_keys catalog view.

Here is an example of a query that joins the above views to list information about column encryption keys, their values and the corresponding column master keys.

 SELECT 
 ek.name AS column_encryption_key_name
 , ev.encrypted_value
 , ev.encryption_algorithm_name
 , mk.name as column_master_key_name
 , mk.key_store_provider_name AS column_master_key_store_provider_name
 , mk.key_path AS column_master_key_path 
 FROM sys.column_encryption_keys ek JOIN sys.column_encryption_key_values ev 
 ON (ek.column_encryption_key_id = ev.column_encryption_key_id) 
 JOIN sys.column_master_keys mk ON (ev.column_master_key_id = mk.column_master_key_id

The mapping of the column encryption keys to columns is captured in the sys.columns catalog view. Thus, to find out which columns in your database are encrypted and with which column encryption keys, you can run the following query:

 SELECT 
 t.name AS table_name
 , c.name AS column_name 
 , c.encryption_type_desc 
 , c.encryption_algorithm_name 
 , k.name AS column_encryption_key_name 
 FROM sys.columns c JOIN sys.column_encryption_keys k 
 ON(c.column_encryption_key_id = k.column_encryption_key_id) 
 JOIN sys.tables t ON (c.object_id = t.object_id)  

Putting it All Together

Now, let us explore how all the metadata, described above, is used when an application queries encrypted columns. Let us assume an application issues the following query against our Clinic database from our Getting Started With Always Encrypted blog article.

 SELECT * FROM Patients

Assuming that the application has connected to the database using a connection string containing Column Encryption Setting=Enabled, here is the flow that will be executed by SqlClient and SQL Server:

  1. SqlClient sends the query to SQL Server.
  2. When executing the query, SQL Server retrieves all rows from the Patients table, including encrypted values from the SSN and BirthDate columns. Along with the result set, SQL Server sends the following encryption-related metadata to the SqlClient:
    • Encryption metadata for each encrypted column, included in the result set:

      Column Name

      Encryption Type

      Algorithm Name

      Column Encryption Key Name/Id

      Patients.SSN

      DETERMINISTIC

      AEAD_AES_256_CBC_HMAC_SHA_256

      CEK1

      Patients.BirthDate

      RANDOMIZED

      AEAD_AES_256_CBC_HMAC_SHA_256

      CEK1
    • The encrypted value of the column encryption key, the algorithm used to produce the encrypted value, and the information (key store provider name, key path) about the corresponding column master key:

      Column Encryption Key Name/Id

      Encrypted Value

      Encryption Algorithm

      Column Master Key Store Provider Name

      Column Master Key Path

      CEK1

      0x…

      RSA_OAEP

      MSSQL_CERTIFICATE_STORE

      CurrentUser/My/6ABA...
  3. After receiving the result set and the encryption metadata, SqlClient looks up the name of the key store provider (in our case, MSSQL_CERTIFICATE_STORE) to locate the provider implementation in the registry of key store providers. Note that, MSSQL_CERTIFICATE_STORE is the default key store provider, which is pre-registered in SqlClient, so no configuration or initialization is required to use it in the application.
  4. SqlClient calls the key store provider holding the column master key. SqlClient passes the encrypted value of the column encryption key, the name of the algorithm used to encrypt the value and the key path of the column master key.
  5. Assuming the key path is valid and the user has access to the column master key, the key store provider decrypts the encrypted value and returns the plaintext value of the column encryption key to SqlClient.
  6. SqlClient uses the plaintext value of the column encryption key (along with the information about how particular columns are encrypted) to decrypt the values in the result set.
  7. SqlClient returns the result set containing plaintext values to the application.

For the above workflow to succeed, SqlClient must be able to decrypt the column encryption key protecting the data in the result set. For that:

  • The name of the provider for the key store holding the column master key must be valid (the provider must be registered in SqlClient).      
  • The key path, specified in the column master key object in the database must be valid in the environment hosting the client application. If the column master key is a certificate stored in Windows Certificate Store, the certificate must be deployed to the store and the location, specified in the key path, and its thumbprint must match the thumbprint specified in the key path.

The above implies that if you move the application from one machine to another, you need to make sure the column master key is accessible on the new machine. For this example, the column master key is a certificate stored in the CurrentUser store, you need to deploy the certificate on the new machine to the CurrentUser store for the user, who will run the application on the new machine.

A couple of other facts about the above workflow:

  • SqlClient caches plaintext values of column encryption keys, so on subsequent queries that use a column encryption key that was used in a previous query, SqlClient will use the cached plaintext value of the column encryption key (step 3-5 will be skipped). 
  • A database can have multiple column encryption keys configured. If a result set contains values from columns using different column encryption keys, steps 3-5 need to be performed for each column encryption key.

So far, we have looked at the decryption workflow. Now, let us examine the flow for encrypting the values of query parameters that correspond in encrypted columns in the database. Let us assume, a .NET application issues a parameterized INSERT statement to insert a row into the Patients table:

 INSERT INTO [dbo].[Patients] ([SSN], [FirstName], [LastName],
 [BirthDate]) VALUES (@SSN, @FirstName, @LastName, @BirthDate);

(Note: The Getting Started With Always Encrypted article contains a complete code sample for inserting a row to the Patients table.)

As before, we assume, the application has connected to the database using a connection string containing Column Encryption Setting=Enabled.

Here is the flow that will be executed by SqlClient and SQL Server:

  1. Since the connection to the database was established using Column Encryption Setting=Enabled and the query contains parameters, SqlClient first calls the sys.describe_parameter_encryption stored procedure, passing the query statement, but no parameter values, to SQL Server.
  2. SQL Server analyzes the query and determines that the @SSN and @BirthDate parameters correspond to the SSN and BirthDate columns, which are encrypted.
  3. SQL Server sends a response to SqlClient, which includes:

    • Information about encryption of columns, referenced in query parameters:

      Column Name

      Encryption Type

      Algorithm Name

      Column Encryption Key Name/Id

      Patients.SSN

      DETERMINISTIC

      AEAD_AES_256_CBC_HMAC_SHA_256

      CEK1

      Patients.BirthDate

      RANDOMIZED

      AEAD_AES_256_CBC_HMAC_SHA_256

      CEK1

    •  The encrypted value of the column encryption key, the algorithm used to produce the encrypted value, and the information (key store provider name, key path) about the corresponding column master key:

      Column Encryption Key Name/Id

      Encrypted Value

      Encryption Algorithm

      Column Master Key Store Provider Name

      Column Master Key Path

      CEK1

      0x…

      RSA_OAEP

      MSSQL_CERTIFICATE_STORE

      CurrentUser/My/6ABA...

       

  4. SqlClient decrypts the column encryption in the same way as in the encryption workflow (following steps 3-5 in the encryption workflow), or, if the column encryption key has been used before (is available in the cache), SqlClient simply retrieves the plaintext value of the column encryption key from the cache.
  5. SqlClient encrypts the values of parameters corresponding to encrypted columns ( @SSN, @BirthDate).
  6. SqlClient sends the original query along with the ciphertext of parameters corresponding to encrypted columns to SQL Server.
  7. SQL Server executes the query.

Conclusion

With Always Encrypted, SQL Server is the central store for encryption-related metadata, which includes encrypted (but not plaintext) values of column encryption keys and the information about the location of column master keys (but no actual column master keys). The key metadata plays a critical role in the query processing workflow and is the key to ensure the transparency of encryption to client applications: an application (and the application developer) does not have to worry about identifying the keys needed to encrypt query parameters or decrypt query results, as this information is automatically located by SQL Server at runtime and fetched by SqlClient. However, the application developer or/and administrator must ensure column master keys, configured in the database and associated with columns referenced in application’s queries, are accessible to the application.