Encryption Hierarchy

SQL Server offers the ability to encrypt data through a hierarchy of keys and certificates. Each layer within the hierarchy encrypts the layer below it.

SQL Server Encryption Concepts

SQL Server’s cryptography functionality relies on a hierarchy of keys and certificates, with the root level being the Service Master Key. The following sections describe the use of master keys, as well as SQL Server’s encryption hierarchy.

Master Keys

The root level of the SQL Server encryption hierarchy is the Service Master Key. The Service Master Key is created automatically when the instance is built, and it is used to encrypt database master keys, credentials, and linked servers’ passwords using the DPAPI. The Service Master Key is stored in the Master database and there is always precisely one per instance. The Service Master Key is a symmetric key that is generated using the AES 256 algorithm. This is in contrast to older versions of SQL Server, which use the Triple DES algorithm.

If you ever need to regenerate the Service Master Key, it involves decrypting and then reencrypting every key and certificate that sits below it in the hierarchy. This is a very resource-intensive process and should only be attempted during a maintenance window.

You can regenerate the Service Master Key using the command in Listing 12-1. You should be aware, however, that if the process fails to decrypt and reencrypt any key that is below it in the hierarchy, then the whole regeneration process fails. You can change this behavior by using the FORCE keyword. The FORCE keyword forces the process to continue, after errors. Be warned that this will leave any data that cannot be decrypted and reencrypted unusable. You will have no way to regain access to this data.

ALTER SERVICE MASTER KEY REGENERATE

Listing 12-1

Regenerating the Service Master Key

Because the Service Master Key is so vital, you must take a backup of it after it has been created or regenerated and store it in a secure, off-site location for the purpose of disaster recovery. You can also restore the backup of this key if you are migrating an instance to a different server to avoid issues with the encryption hierarchy. The script in Listing 12-2 demonstrates how to back up and restore the Service Master Key. If the master key you restore is identical, then SQL Server lets you know and data does not need to be reencrypted.

BACKUP SERVICE MASTER KEY

    TO FILE = 'c:keysservice_master_key'

    ENCRYPTION BY PASSWORD = 'Pa$$w0rd'

RESTORE SERVICE MASTER KEY

    FROM FILE = 'c:keysservice_master_key'

    DECRYPTION BY PASSWORD = 'Pa$$w0rd'

Listing 12-2

Backing Up and Restoring the Service Master Key

Tip

service_master_key is the name of the key file as opposed to a folder. By convention, it does not have an extension.

As when you are regenerating a Service Master Key, when you restore it, you can also use the FORCE keyword with the same consequences.

A Database Master Key is a symmetric key, encrypted using the AES 256 algorithm that is used to encrypt the private keys and certificates that are stored within a database. It is encrypted using a password, but a copy is created that is encrypted using the Service Master Key. This allows the Database Master Key to be opened automatically when it is needed. If this copy does not exist, then you need to open it manually. This means that the key needs to be explicitly opened in order for you to use a key that has been encrypted by it. A copy of the Database Master Key is stored within the database and another copy is stored within the Master database. You can create a Database Master Key using the command in Listing 12-3.

CREATE DATABASE Chapter12MasterKeyExample ;

GO

USE Chapter12MasterKeyExample

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pa$$w0rd'

Listing 12-3

Creating a Database Master Key

As with the Service Master Key, Database Master Keys should be backed up and stored in a secure off-site location. You can back up and restore a Database Master Key by using the commands in Listing 12-4.

BACKUP MASTER KEY

    TO FILE = 'c:keysChapter12_master_key'

    ENCRYPTION BY PASSWORD = 'Pa$$w0rd';

RESTORE MASTER KEY

    FROM FILE = 'c:keysChapter12_master_key'

    DECRYPTION BY PASSWORD = 'Pa$$w0rd' --The password in the backup file

    ENCRYPTION BY PASSWORD = 'Pa$$w0rd'; --The password it will be encrypted within the database

Listing 12-4

Backing Up and Restoring a Database Master Key

As with the Service Master Key, if the restore is unable to decrypt and reencrypt any of the keys below it in the hierarchy, the restore fails. You are able to use the FORCE keyword to force the restore to succeed, but when you do so, you permanently lose access to the data encrypted using the key(s) that could not be decrypted and reencrypted.

SQL Server 2022 introduces support for backing up the Database Master key to Azure Blob storage. To use this option, you will need to create a SQL Server Credential that uses the URL of the Blob storage as its name, the shared access signature as the identity, and the shared access signature key as the secret. Credentials are discussed in Chapter 10 and creating credentials for Azure is demonstrated in Chapter 21.

Once you have a Credential in place, you can simply replace the TO FILE clause of the BACKUP MASTER KEY and RESTORE MASTER KEY statements with TO URL and FROM URL clauses, where the URL is the same as the URL used by the Credential.

Hierarchy

The SQL Server encryption hierarchy is illustrated in Figure 12-1.

The diagram shows that the Service Master Key and a copy of the Database Master Key are stored at the instance level, with the Database Master Key also being stored within the database. The certificates, symmetric keys, and asymmetric keys that are encrypted using the Database Master Key are also stored within the database.

To the right of the diagram, you see a section called the EKM Module. An Extensible Key Management (EKM) module allows you to generate and manage keys and certificates used to secure SQL Server data in third-party hardware security modules, which interface with SQL Server using the Microsoft Cryptographic API (MSCAPI). This is more secure because the key is not being stored with the data, but it also means that you can benefit from advanced features that may be offered by the third-party vendor, such as key rotation and secure key disposal.

Before you can use a third-party EKM module, you need to enable EKM at the instance level using sp_configure, and you must register the EKM by importing the .dll into SQL Server. Many EKM providers are available, but the sample script in Listing 12-5 demonstrates how you might import the Thales EKM module after you install the database security pack.

--Enable EKM

sp_configure 'show advanced', 1

GO

RECONFIGURE

GO

sp_configure 'EKM provider enabled', 1

GO

RECONFIGURE

GO

--Register provider

CREATE CRYPTOGRAPHIC PROVIDER nCipher_Provider FROM FILE =

 'C:Program Files Cipher fastin csqlekm64.dll'

Listing 12-5

Enabling EKM and Importing the EKM Module

Note

A full discussion of EKM is beyond the scope of this book, but you can obtain further information from your cryptographic provider.

Transparent Data Encryption

When implementing a security strategy for your sensitive data, one important aspect to consider is the risk of data being stolen. Imagine a situation in which a privileged user with malicious intent uses detach/attach to move a database to a new instance in order to gain access to data they are not authorized to view. Alternatively, if a malicious user gains access to the database backups, they can restore the backups to a new server in order to gain access to the data.

Transparent data encryption (TDE) protects against these scenarios by encrypting the data pages and log file of a database and by storing the key, known as a Database Encryption Key, in the boot record of the database. Once you enable TDE on a database, pages are encrypted before they are written to disk and they are decrypted when they are read into memory.

TDE also provides several advantages over cell-level encryption, which will be discussed later in this chapter. First, it does not cause bloat. A database encrypted with TDE is the same size as it was before it was encrypted. Also, although there is a performance overhead, this is significantly less than the performance overhead associated with cell-level encryption. Another significant advantage is that the encryption is transparent to applications, meaning that developers do not need to modify their code to access the data.

When planning the implementation of TDE, be mindful of how it interacts with other technologies. For example, you are able to encrypt a database that uses In-Memory OLTP, but the data within the In-Memory filegroup is not encrypted because the data resides in memory, and TDE only encrypts data at rest, meaning when it is on disk. Even though the memory-optimized data is not encrypted, log records associated with in-memory transactions are encrypted.

It is also possible to encrypt databases that use FILESTREAM, but again, data within a FILESTREAM filegroup is not encrypted. If you use full-text indexes (FTE), then new full-text indexes are encrypted. Existing full-text indexes are only encrypted after they are imported during an upgrade. It is regarded as bad practice to use full-text indexing with TDE, however. This is because data is written to disk in plain text during the full-text indexing scan operation, which leaves a window of opportunity for attackers to access sensitive data.

High availability and disaster recovery technologies such as database mirroring, AlwaysOn Availability Groups, and log shipping are supported with databases that have TDE enabled. Data on the replica database is also encrypted, and the data within the log is encrypted, meaning that it cannot be intercepted as it is being sent between the servers. Replication is also supported with TDE, but the data in the subscriber is not automatically encrypted. You must enable TDE manually on subscribers and the distributor.

It is also important to note that enabling TDE for any database within an instance causes TDE to be enabled on TempDB. The reason for this is that TempDB is used to store user data for intermediate results sets, during sort operations, spool operations, and so on. TempDB also stores user data when you are using Temp Tables, or row versioning operations occur. This can have the undesirable effect of decreasing the performance of other user databases that have not had TDE enabled.

It is also important to note, from the viewpoint of the performance of database maintenance, that TDE is incompatible with instant file initialization. Instant file initialization speeds up operations that create or expand files, as the files do not need to be zeroed out. If your instance is configured to use instant file initialization, then it no longer works for the files associated with any databases that you encrypt. It is a hard technical requirement that files are zeroed out when TDE is enabled.

Implementing TDE

To implement transparent data encryption, you must first create a Database Master Key. Once this key is in place, you can create a certificate. You must use the Database Master Key to encrypt the certificate. If you attempt to encrypt the certificate using a password only, then it will be rejected when you attempt to use it to encrypt the Database Encryption Key. The Database Encryption Key is the next object that you need to create, and as implied earlier, you encrypt this using the certificate. Finally, you can alter the database to turn encryption on.

Note

It is possible to encrypt the Database Encryption Key using an asymmetric key as opposed to a server certificate, but only if the asymmetric key is protected using an EKM module.

When you enable TDE for a database, a background process moves through each page in every data file and encrypts it. This does not stop the database from being accessible, but it does take out locks, which stop maintenance operations from taking place. While the encryption scan is in progress, the following operations cannot be performed:

• Dropping a file

• Dropping a filegroup

• Dropping the database

• Detaching the database

• Taking the database offline

• Setting the database as read_only

Luckily, a feature in recent versions of SQL Server gives DBAs more control over this process, with the ability to pause and restart the encryption scan, using an ALTER DATABASE statement, with either SET ENCRYPTION SUSPEND or SET ENCRYPTION RESTART options specified.

It is also important to note that the operation to enable TDE will fail if any of the filegroups within a database are marked as read_only. This is because all pages within all files need to be encrypted when TDE is enabled, and this involves changing the data within the pages to obfuscate them.

The script in Listing 12-6 creates a database called Chapter12Encrypted and then creates a table that is populated with data. Finally, it creates a Database Master Key and a server certificate.

--Create the Database

CREATE DATABASE Chapter12Encrypted ;

GO

USE Chapter12Encrypted

GO

--Create the table

CREATE TABLE dbo.SensitiveData

(

ID                INT                PRIMARY KEY        IDENTITY,

FirstName        NVARCHAR(30),

LastName        NVARCHAR(30),

CreditCardNumber        VARBINARY(8000)

) ;

GO

--Populate the table

DECLARE @Numbers TABLE

(

        Number        INT

)

;WITH CTE(Number)

AS

(

         SELECT 1 Number

         UNION ALL

         SELECT Number + 1

         FROM CTE

         WHERE Number < 100

)

INSERT INTO @Numbers

SELECT Number FROM CTE ;

DECLARE @Names TABLE

(

         FirstName        VARCHAR(30),

         LastName        VARCHAR(30)

) ;

INSERT INTO @Names

VALUES('Peter', 'Carter'),

                 ('Michael', 'Smith'),

                 ('Danielle', 'Mead'),

                 ('Reuben', 'Roberts'),

                 ('Iris', 'Jones'),

                 ('Sylvia', 'Davies'),

                 ('Finola', 'Wright'),

                 ('Edward', 'James'),

                 ('Marie', 'Andrews'),

                 ('Jennifer', 'Abraham'),

                 ('Margaret', 'Jones') ;

INSERT INTO dbo.SensitiveData(Firstname, LastName, CreditCardNumber)

SELECT  FirstName, LastName, CreditCardNumber FROM

         (SELECT

                 (SELECT TOP 1 FirstName FROM @Names ORDER BY NEWID()) FirstName

                ,(SELECT TOP 1 LastName FROM @Names ORDER BY NEWID()) LastName

                ,(SELECT CONVERT(VARBINARY(8000)

                ,(SELECT TOP 1 CAST(Number * 100 AS CHAR(4))

                  FROM @Numbers

                  WHERE Number BETWEEN 10 AND 99 ORDER BY NEWID()) + '-' +

                         (SELECT TOP 1 CAST(Number * 100 AS CHAR(4))

                           FROM @Numbers

                           WHERE Number BETWEEN 10 AND 99 ORDER BY NEWID()) + '-' +

                         (SELECT TOP 1 CAST(Number * 100 AS CHAR(4))

                           FROM @Numbers

                           WHERE Number BETWEEN 10 AND 99 ORDER BY NEWID()) + '-' +

                         (SELECT TOP 1 CAST(Number * 100 AS CHAR(4))

                           FROM @Numbers

                           WHERE Number BETWEEN 10 AND 99 ORDER BY NEWID()))) CreditCardNumber

FROM @Numbers a

CROSS JOIN @Numbers b

CROSS JOIN @Numbers c

) d ;

USE Master

GO

--Create the Database Master Key

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pa$$w0rd';

GO

--Create the Server Certificate

CREATE CERTIFICATE TDECert WITH SUBJECT = 'Certificate For TDE';

GO

Listing 12-6

Creating the Chapter12Encrypted Database

Now that we have created our database, along with the Database Master Key and certificate, we can now encrypt our database. To do this through SQL Server Management Studio, we can select Manage Database Encryption, from under Tasks, in the context menu of our database. This invokes the Manage Database Encryption wizard illustrated in Figure 12-2.

You can see that we have selected our server certificate from the drop-down box and have chosen to enable database encryption. In the Encryption Algorithm drop-down box, we have selected AES 128, which is the default option.

Note

Choosing an algorithm is essentially a trade-off between security and performance. Longer keys consume more CPU resources but are more difficult to crack.

Transparent data encryption can also be configured through T-SQL. We can achieve the same results via T-SQL by executing the script in Listing 12-7.

USE CHAPTER12Encrypted

GO

--Create the Database Encryption Key

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_128

ENCRYPTION BY SERVER CERTIFICATE TDECert ;

GO

--Enable TDE on the database

ALTER DATABASE CHAPTER12Encrypted SET ENCRYPTION ON ;

GO

Listing 12-7

Enabling Transparent Data Encryption

Tip

In SQL Server 2022, certificates can also be created and backed up as in PFX format. This allows the server certificate, intermediate certificates, and the private key to all be stored in the same file, using a binary format. This can be used to avoid using the weaker RC4 algorithm, to allow conversion to public key pairs.

Migrating an Encrypted Database

By the very nature of TDE, if we attempt to move our Chapter12Encrypted database to a new instance, the operation fails, unless we take our cryptographic artifacts into account. Figure 12-3 illustrates the message we receive if we take a backup of the Chapter12Encrypted database and try to restore it on a new instance. You can find a full discussion of backups and restores in Chapter 13.

We would receive the same error if we detached the database and attempted to attach it to the new instance. Instead, we must first create a Database Master Key with the same password and then restore the server certificate and private key to the new instance. We can restore the server certificate that we created earlier using the script in Listing 12-9.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pa$$w0rd' ;

GO

CREATE CERTIFICATE TDECert

FROM FILE = 'C:CertificatesTDECert'

WITH PRIVATE KEY

(

    FILE = 'C:CertificatesTDECertKey',

    DECRYPTION BY PASSWORD = 'Pa$$w0rd'

) ;

Listing 12-9

Restoring the Server Certificate

TIP

Make sure that the SQL Server service account has permissions to the certificate and key files in the operating system. Otherwise, you will receive an error stating that the certificate is not valid, does not exist, or that you do not have permissions to it. This means that you should check the restore immediately and periodically repeat the test.

Managing Cell-Level Encryption

Cell-level encryption allows you to encrypt a single column, or even specific cells from a column, using a symmetric key, an asymmetric key, a certificate, or a password. Although this can offer an extra layer of security for your data, it can also cause a significant performance impact and a large amount of bloat. Bloat means that the size of the data is much larger after the data has been encrypted than it was before. Additionally, implementing cell-level encryption is a manual process that requires you to make code changes to applications. Therefore, encrypting data should not be your default position, and you should only do it when you have a regulatory requirement or clear business justification.

When we created the SensitiveData table in our database, you may have noticed that we used the VARBINARY(8000) data type for the CreditCardNumber column when the obvious choice would have been a CHAR(19). This is because encrypted data must be stored as one of the binary data types. We have set the length to 8000 bytes, because this is the maximum length of the data that is returned from the function used to encrypt it.

You will notice that we back up the symmetric key before using it. This is a new feature of SQL Server 2022. In previous versions, only Service Master Keys, Database Master Keys, and Certificates could be backed up. The feature allows for the backup of symmetric keys to either file (as demonstrated) or to Azure Blob storage. To back up to Azure Blob storage, use a Credential to authenticate and replace the TO FILE clause with a TO URL clause, as discussed for Database Master Keys, earlier in this chapter. Keys can then be restored using a RESTORE SYMMETRIC KEY statement. Once again, this statement accepts FROM FILE or FROM URL clauses.

Also notice that before we use the key to encrypt the data, we issue a statement to open the key. The key must always be opened before it is used for either encrypting or decrypting data. To do this, the user must have permissions to open the key.

When you encrypt a column of data using the method shown in Listing 12-10, you still have a security risk caused by the deterministic nature of the algorithm used for encryption, which means when you encrypt the same value, you get the same hash. Imagine a scenario in which a user has access to the SensitiveData table but is not authorized to view the credit card numbers. If that user is also a customer with a record in that table, they could update their own credit card number with the same hashed value as that of another customer in the table. They have then successfully stolen another customer’s credit card number, without having to decrypt the data in the CreditCardNumber column. This is known as a whole-value substitution attack.

To protect against this scenario, you can add an authenticator column, which is also known as a salt value. This can be any column but is usually the primary key column of the table. When the data is encrypted, the authenticator column is encrypted along with the data. At the point of decryption, the authenticator value is then checked, and if it does not match, then the decryption fails.

The script in Listing 12-11 shows how we can use an authenticator column to encrypt the CreditCardNumber column using the primary key of the table as an Authenticator column. Here, we use the HASHBYTES() function to create a hash value of the Authenticator column, and then we use the hash representation to encrypt the data. If you have already encrypted the column, the values are updated to include the salt.

At the end of the script, we close the key. If we do not close it explicitly, then it remains open for the rest of the session. This can be useful if we are going to perform multiple activities using the same key, but it is good practice to explicitly close it immediately following its final usage within a session.

Even though it is possible to encrypt data using symmetric keys, asymmetric keys, or certificates for performance reasons, you will usually choose to use a symmetric key and then encrypt that key using either an asymmetric key or a certificate.

Accessing Encrypted Data

In order to read the data in the column encrypted using ENCRYPTBYKEY(), we need to decrypt it using the DECRYPTBYKEY() function. Table 12-3 describes the parameters for this function.

Table 12-3

DecryptByKey Parameters

Parameter	Description
Cyphertext	The encrypted data that you want to decrypt
AddAuthenticator	A BIT value specifying if an authenticator column is required
Authenticator	The column to be used as an authenticator

The script in Listing 12-12 demonstrates how to read the encrypted data in the CreditCardNumber column using the DECRYPTBYKEY() function after it has been encrypted without an authenticator.

--Open Key

OPEN SYMMETRIC KEY CreditCardKey

    DECRYPTION BY CERTIFICATE CreditCardCert;

--Read the Data using DECRYPTBYKEY()

SELECT

         FirstName

        ,LastName

        ,CreditCardNumber AS [Credit Card Number Encrypted]

        ,CONVERT(VARCHAR(30), DECRYPTBYKEY(CreditCardNumber)) AS [Credit Card Number Decrypted]

        ,CONVERT(VARCHAR(30), CreditCardNumber)

                                    AS [Credit Card Number Converted Without Decryption]

FROM dbo.SensitiveData ;

--Close the Key

CLOSE SYMMETRIC KEY CreditCardKey ;

Listing 12-12

Reading an Encrypted Column

The sample of the results from the final query in this script is shown in Figure 12-4. You can see that querying the encrypted column directly returns the encrypted binary value. Querying the encrypted column with a straight conversion to the VARCHAR data type succeeds, but no data is returned. Querying the encrypted column using the DECRYPTBYKEY() function, however, returns the correct result when the value is converted to the VARCAH data type.

Always Encrypted

Always Encrypted is a technology which protects data against privileged users, such as members of the sysadmin role. Because DBAs cannot view the encrypted data, Always Encrypted provides true segregation of duties. This can help with compliance issues for sensitive data when your platform support is outsourced to a third-party vendor. This is especially true if you have a regulatory requirement not to make your data available outside of your country’s jurisdiction and the third-party vendor is using offshore teams.

Always Encrypted uses two separate types of key: a column encryption key and a column master key. The column encryption key is used to encrypt the data within a column and the column master key is used to encrypt the column encryption keys.

Having the second layer of key means that SQL Server needs only to store an encrypted value of the column encryption key, instead of storing it in plain text. The column master key is not stored in the database engine at all. Instead, it is stored in an external key store. The key store used could be an HSM (hardware security module), Windows Certificate Store, or an EKM provider, such as Azure Key Vault or Thales. SQL Server then stores the location of the column master key, within the database metadata.

Instead of SQL Server being responsible for the encryption and decryption of data, this responsibility is handled by the client driver. Of course, this means that the application must be using a supported driver, and the following link contains details of working with supported drivers: https://msdn.microsoft.com/en-gb/library/mt147923.aspx.

When an application issues a request, which will require data to either be encrypted or decrypted, the client driver liaises with the database engine to determine the location of the column master key. The database engine also provides the encrypted column encryption key and the algorithm used to encrypt it.

The client driver can now contact the external key store and retrieve the column master key, which it uses to decrypt the column encryption key. The plaintext version of the column encryption key can then be used to encrypt or decrypt the data, as required.

The entire process is transparent to the application, meaning that changes are not required to the application’s code in order to use Always Encrypted. The only change that may be required is to use a later supported driver.

Always Encrypted has some significant limitations, including an inability to perform nonequality comparisons (even equality comparisons are only available with deterministic encryption). SQL Server 2019 introduced Secure Enclaves to address some of these issues. With secure enclaves, operators such as <, >, and even LIKE are supported, providing that randomized encryption is used. Secure Enclaves also support in-place encryption.

Secure Enclaves work, by using a protected area of memory, inside the SQL Server process, as a trusted execution environment. Within this memory region, data is decrypted and computations are performed. It is not possible for the rest of the SQL Server process, or any other process on the server, to access the secure memory, meaning that the decrypted data cannot be leaked, even when using debugging tools.

If SQL Server determines that a secure enclave is required to satisfy a query, then the client driver uses a secure channel, to send the encryption keys to the secure enclave. The client driver then submits the query, and encrypted query parameters, for execution. Because the data (even the encrypted parameters) are only ever decrypted inside the enclave, the data, parameters, and encryption keys are never exposed, in plain text.

Because the decrypted data and keys are available inside of the enclave, the client driver needs to verify that the enclave is genuine. To do this, it requires an external arbiter, known as an attestation service, such as the Windows Server Host Guardian Service. Before sending any data to the enclave, the client driver will contact the attestation service to determine the enclave’s validity.

When using Always Encrypted, there are many limitations. For example, advanced data types are not supported. A full list of limitations can be found at docs.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine?view=sql-server-2022#feature-details.

Administering Keys

As you would expect, metadata about keys is exposed through system tables and dynamic management views. Details regarding Column Master Keys can be found in the sys.column_master_keys table. The columns returned by this table are detailed in Table 12-6.

Table 12-6

sys.column_master_keys Columns

Column	Description
Name	The name of the column master key.
Column_master_key_id	The internal identifier of the column master key.
Create_date	The date and time that the key was created.
Modify_date	The date and time that the key was last modified.
Key_store_provider_name	The type of key store provider, where the key is stored.
Key_path	The path to the key, within the key store.
Allow_enclave_computations	Specifies if the key is enclave enabled.
Signature	A digital signature, combining key_path and allow_enclave_computations. This stops malicious administrators from changing the key’s enclave enabled setting.

The details of Column Encryption Keys can be found in the sys.column_encryption_keys system table. This table returns the columns detailed in Table 12-7.

Table 12-7

Columns Returned by sys.column_encryption_keys

Name	Description
Name	The name of the column encryption key
Column_encryption_key_id	The internal ID of the column encryption key
Create_date	The date and time that the key was created
Modify_date	The date and time that the key was last modified

An additional system table, called sys.column_encryption_key_vales, provides a join between the sys.column_master_keys and sys.column_encryption_keys system tables while at the same time providing the encrypted value of the column encryption key, when encrypted by the column master key. Table 12-8 details the columns returned by this system table.

Table 12-8

sys.column_encryption_key_values Columns

Name	Description
Column_encryption_key_id	The internal ID of the column encryption key
Column_master_key_id	The internal ID of the column master key
Encrypted_value	The encrypted value of the column encryption key
Encrypted_algorithm_name	The algorithm used to encrypt the column encryption key

Therefore, we could use the query in Listing 12-21 to find all columns in a database that have been encrypted with enclave enabled keys.

Tip

Remove the WHERE clause to return all columns that are secure with Always Encrypted, and determine which columns do and do not support secure enclaves.

SELECT

        c.name AS ColumnName

      , OBJECT_NAME(c.object_id) AS TableName

      , cek.name AS ColumnEncryptionKey

      , cmk.name AS ColumnMasterKey

      , CASE

            WHEN cmk.allow_enclave_computations = 1

                  THEN 'Yes'

            ELSE 'No'

        END AS SecureEnclaves

FROM sys.columns c

INNER JOIN sys.column_encryption_keys cek

      ON c.column_encryption_key_id = cek.column_encryption_key_id

INNER JOIN sys.column_encryption_key_values cekv

      ON cekv.column_encryption_key_id = cek.column_encryption_key_id

INNER JOIN sys.column_master_keys cmk

      ON cmk.column_master_key_id = cekv.column_master_key_id

WHERE allow_enclave_computations = 1

Listing 12-21

Return Details of Columns That Use Secure Enclaves

It is not possible for an Administrator to toggle a key between enclave enabled and not enclave enabled. This is a conscious design decision by Microsoft to protect against malicious Administrators. It is possible to rotate keys, however, and when rotating keys, you can rotate out a key that is not enclave enabled, and replace it with one that is (or vice versa).

Tip

The following demonstration assumes that an additional column master key exists within the Chapter12AlwaysEncrypted database.

The simplest way to rotate a key is by using SQL Server Management Studio. Drill through Databases ➤ Chapter12AlwaysEncrypted ➤ Security ➤ Always Encrypted Keys ➤ Column Master Keys and then select Rotate from the context menu of the key which you wish to rotate out. This will cause the Column Master Key Rotation dialog box to be displayed, as illustrated in Figure 12-5. Here, you can select the new key, which should be used to encrypt the underlying column encryption keys.

Now that the encryption keys have been reencrypted using the new keys, the old key values need to be cleaned up. This can be achieved by selecting Cleanup from the context menu of the old column master key, causing the Column Master Key Cleanup dialog box to be invoked. This is illustrated in Figure 12-6.

Summary

The SQL Server encryption hierarchy begins with the Service Master Key, which is encrypted using the Data Protection API (DPAPI) in the Windows operating system. You can then use this key to encrypt the Database Master Key. In turn, you can use this key to encrypt keys and certificates stored within the database. SQL Server also supports third-party Extensible Key Management (EKM) providers to allow for advanced key management of keys used to secure data.

Transparent data encryption (TDE) gives administrators the ability to encrypt an entire database with no bloat and an acceptable performance overhead. This offers protection against the theft of data by malicious users attaching a database to a new instance or stealing the backup media. TDE gives developers the advantage of not needing to modify their code in order to access the data.

Cell-level encryption is a technique used to encrypt data at the column level, or even the specific rows within a column, using a symmetric key, an asymmetric key, or a certificate. Although this functionality is very flexible, it is also very manual and causes a large amount of bloat and a large performance overhead. For this reason, I recommended that you only use cell-level encryption to secure the minimum amount of data you need in order to fulfill a regulatory requirement or that you have clear business justification for using it.

In order to mitigate the impact of bloat and performance degradation when using cell-level encryption, it is recommended that you encrypt data using a symmetric key. You can then encrypt the symmetric key using an asymmetric key or certificate.