--- PDF PAGE 400 ---
386
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.
Although it is common practice to encrypt data using a symmetric key, it is also 
possible to encrypt data using an asymmetric key, a certificate, or even a passphrase. If 
you encrypt data using a passphrase, then the TRIPLE DES algorithm is used to encrypt 
the data. Table 11-1 lists the cryptographic functions that you can use to encrypt or 
decrypt data using these methods.
Table 11-1.  Cryptographic Functions
Encryption Method
Encryption Function
Decryption Function
Asymmetric key
ENCRYPTBYASYMKEY()
DECRYPTBYASYMKEY()
Certificate
ENCRYPTBYCERT()
DECRYPTBYCERT()
Passphrase
ENCRYPTBYPASSPHRASE()
DECRYPTBYPASSPHRASE()
Chapter 11  Encryption

--- PDF PAGE 403 ---
389
Notice that the UPDATE statement that we used to encrypt the data uses a function 
called ENCRYPTBYKEY() to encrypt the data. Table 11-2 describes the parameters the 
ENCRYPTBYKEY() function accepts. If we wish only to encrypt a subset of cells, we can add 
a WHERE clause to the UPDATE statement.
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 11-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.
Table 11-2.  EncryptByKey() Parameters
Parameter
Description
Key_GUID
The GUID of the symmetric key that is used to encrypt the data
ClearText
The binary representation of the data that you wish to encrypt
Add_authenticator
A BIT parameter that indicates if an authenticator column should 
be added
Authenticator
A parameter that specifies the column that should be used as an 
authenticator
Chapter 11  Encryption

--- PDF PAGE 405 ---
391
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 11-3 describes the parameters for 
this function.
The script in Listing 11-12 demonstrates how to read the encrypted data in the 
CreditCardNumber column using the DECRYPTBYKEY() function after it has been 
encrypted without an authenticator.
Listing 11-12.  Reading an Encrypted Column
--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]
Table 11-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
Chapter 11  Encryption

--- PDF PAGE 413 ---
399
The next step is to enable secure enclaves within the SQL Server instance. Unlike 
most instance configurations, the instance must be restarted for the change to take 
effect. The script in Listing 11-18 will change the configuration.
Listing 11-18.  Enable Secure Enclaves
EXEC sys.sp_configure 'column encryption enclave type', 1;
RECONFIGURE ;
Tip  In prerelease versions of SQL Server 2019, Trace Flag 127 must be enabled 
globally, to enable rich computations.
We now want to encrypt the CreditCardNumber, ExpMonth, and ExpYear columns of 
the dbo.CreditCards table, which is loosely based on the Sales.CreditCard table of the 
AdventureWorks database.
When encrypting the data, we have a choice of two methods: deterministic or 
randomized. This is an important decision to understand, as it may have an impact on 
performance, security, and the features that are available with secure enclaves.
Table 11-4.  Key Store Values
Key Store Type
Description
Windows Certificate 
Store—Current User
The key or certificate is stored in the area of the Windows Certificate 
Store that is reserved for the profile of the user that created the 
certificate. This option may be appropriate if you use the database 
engine’s service account interactively to create the certificate.
Windows Certificate 
Store—Local Machine
The key or certificate is stored in the area of the Windows Certificate 
Store that is reserved for the local machine.
Azure Key Vault
The key or certificate is stored in the Azure Key Vault EKM service.
Key Storage Provider 
(CNG)
The key or certificate is stored in an EKM store that supports 
Cryptography API: Next Generation.
Chapter 11  Encryption

--- PDF PAGE 414 ---
400
Deterministic encryption will always produce the same encrypted value for the same 
plaintext value. This means that if deterministic encryption is used, operations including 
equality joins, grouping, and indexing are possible on an encrypted column, proving a 
BIN2 collation is used for the column. This leaves the possibility of attacks against the 
encryption, however.
If you use randomized encryption, then different encrypted values can be generated 
for the same plaintext values. This means that while encryption loopholes are plugged, 
for standard Always Encrypted implementations, equality joins, grouping, and indexing 
are not supported against the encrypted data.
When implementing Always Encrypted with secure enclaves, however, more 
functionality is available when using randomized encryption, than it is when using 
deterministic encryption. Table 11-5 details the compatibility of deterministic and 
randomized encryption, with and without secure enclaves.
Table 11-5.  Encryption Types and Feature Compatibility
Encryption Type
In-Place 
Encryption
Equality 
Comparisons
Rich 
Computations
Like
Deterministic Without 
Enclaves
No
Yes
No
No
Deterministic With 
Enclaves
Yes
Yes
No
No
Randomized Without 
Enclaves
No
No
No
No
Randomized With 
Enclaves
Yes
Yes (Inside 
Enclave)
Yes
Yes
We will use randomized encryption, so that we can fully benefit from secure enclave 
functionality. The script in Listing 11-19 will create the Chapter11AlwaysEncrypted 
database, before creating the dbo.CreditCards table, which is loosely based on the 
Sales.CreditCards table from the AdventureWorks database.
Chapter 11  Encryption

--- PDF PAGE 417 ---
403
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 11-6.
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 11-7.
Table 11-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 changing the key’s enclave-enabled setting.
Table 11-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
Chapter 11  Encryption

--- PDF PAGE 417 ---
403
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 11-6.
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 11-7.
Table 11-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 changing the key’s enclave-enabled setting.
Table 11-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
Chapter 11  Encryption

--- PDF PAGE 418 ---
404
An additional system table called sys.column_encryption_key_values 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 11-8 details the columns returned 
by this system table.
Therefore, we could use the query in Listing 11-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.
Listing 11-21.  Return Details of Columns That Use 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
Table 11-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
Chapter 11  Encryption