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.
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:
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.
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:
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.
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)
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:
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 |
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... |
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 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:
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:
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
|
Encryption Algorithm |
Column Master Key Store Provider Name |
Column Master Key Path |
CEK1 |
0x… |
RSA_OAEP |
MSSQL_CERTIFICATE_STORE |
CurrentUser/My/6ABA... |
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.