Defense-in-Depth

Defense-in-depthis a technique used across the IT landscape. It refers to implementing multiple layers of security. For example, a company likely has a perimeter firewall on the outskirts of the network. There may then be further firewalls inside the network, between data centers or network blocks. From the SQL Server perspective, defense-in-depth is achieved by using an encryption strategy to supplement the access control strategy. It does not replace the need for access control, but it does provide an additional layer of defense.

Symmetric Keys

A symmetric keyis an algorithm to encrypt data. It is the weakest form of encryption because it uses the same algorithm for both encrypting and decrypting the data. Although it is the weakest form of encryption, it is also the method that has the least performance overhead. You can encrypt a symmetric key with a password, with another key or with a certificate.

Asymmetric Keys

Unlike a symmetric key, which uses the same algorithm to decrypt or encrypt data, an asymmetric keyuses a pair of keys (algorithms). One of the keys is used only for encryption and the other is used only for decryption. The key that is used to encrypt the data is called the private key and the key that is used to decrypt the data is known as the public key.

Certificates

A certificate is issued by a trusted source, known as a certificate authority (CA) . It uses an asymmetric key, but also provides a digitally signed statement that binds the public key to a principal or device, which holds the corresponding private key.

Self-Signed Certificates

A self-signed certificateis a certificate that has been signed by the same entity that its identity certifies. Self-signed certificates can be created by SQL Server.

Windows Data Protection API

The Windows Data Protection API (DPAPI ) is a cryptographic application programming interface (API) that ships with the Windows operating system. It allows keys to be encrypted by using user secret information or domain secret information. DPAPI is used to encrypt the service master key, which is the top level of the SQL Server encryption hierarchy. The service master key is discussed in the “SQL Server Encryption Concepts” section of this chapter.

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. It is used to encrypt database master keys, credentials, and the passwords for linked servers by using the DPAPI. The service master key is stored in the master database. There is always one service master key per instance. Since SQL Server 2012, the service master key has been a symmetric key generated using the AES 256 algorithm. Older versions of SQL Server used the Triple DES algorithm.

If you need to regenerate the service master key, then all keys within the instance’s encryption hierarchy must be decrypted and then re-encrypted. This means that every key and certificate that is encrypted directly or indirectly from the master key must be regenerated. 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 5-1. You should be aware that if the process fails to decrypt and re-encrypt any key that is below it in the hierarchy, then by default, the whole regeneration process fails. You can change this behavior by using the FORCE keyword, which forces the process to continue after errors.

Listing 5-1. Regenerate the Service Master Key

ALTER SERVICE MASTER KEY REGENERATE ;

Because the service master key is crucial, it is very important to back it up after building a new instance and after the key is regenerated. You should then store the backup in a secure offsite location, so that it is available in disaster recovery scenarios. You can also restore the backup of this key when you migrate an instance to a different server to avoid issues with the encryption hierarchy. The script in Listing 5-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 decrypted and re-encrypted.

Listing 5-2. Back up and Restore the Service Master Key

--Backup Service Master Key

BACKUP SERVICE MASTER KEY
TO FILE = 'c:keysservice_master_key'
ENCRYPTION BY PASSWORD = 'Pa$$w0rd' ;

--Restore Service Master Key

RESTORE SERVICE MASTER KEY
FROM FILE = 'c:keysservice_master_key'
DECRYPTION BY PASSWORD = 'Pa$$w0rd                                  ' ;

A database master key is a symmetric key, encrypted using the AES 256 algorithm. The service master key is used to encrypt private keys and certificates that are stored within a database. It is encrypted using a password as the secret; but a copy is created, which is encrypted using the service master key. This allows the service master key to be opened automatically when required. If this copy does not exist, then you need to open it manually.

If the copy does not exist or is corrupt, the key needs to be explicitly opened in order for you to use a key that is below it in the hierarchy (a key that has been encrypted, using the service master key). Copies of the service master key are stored within the database and the master database.

It is as important to back up a service master key as it is to back up a service master key, because losing the key results in data loss for any data that is below it in the encryption hierarchy. In some cases, this could be an entire database. The script in Listing 5-3 demonstrates how to create a service master key for the AdventureWorks2016 database. It then backs up the key and attempts to restore it. The FORCE keyword can be used for service master keys in the same way it can be used for a service master key. This keyword forces the decrypt and re-encrypt process to continue on error. There is a possibility of data loss, however.

Listing 5-3. Administering a Database Master Key

USE AdventureWorks2016                                                
GO

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

BACKUP MASTER KEY TO FILE = 'c:keysChapter5_master_key'
ENCRYPTION BY PASSWORD = 'Pa$$w0rd';

RESTORE MASTER KEY
FROM FILE = 'c:keysChapter5_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

EKM and Key Stores

An Extensible Key Management (EKM ) module allows you to generate and manage keys and certificates used to secure SQL Server data within a third-party hardware security module (HSM) . The EKM module provides the interface with SQL Server by using the Microsoft Cryptographic API (MS-CAPI) . This is more secure because the key is not being stored with the data. It also means that you can benefit from advanced features that may be offered by a third-party vendor, such as key rotation and secure key disposal. When using an HSM, you may also witness improved performance because the encryption and decryption of keys are hardware-based.

Key stores provide secure storage and a trusted source for keys and certificates. Windows Certificate Store provides the functionality within your own Windows Server enterprise. Azure Key Vault offers key storage within Windows Azure. There are also third-party and open source key store providers, such as Amazon Key Management Services (which is a service within the Amazon Web Services ecosystem), KeyWhiz, and Vault, to name but a few.

SQL Server Encryption Hierarchy

Figure 5-1 illustrates the encryption hierarchy in SQL Server.

Figure 5-1. SQL Server encryption hierarchy

Encrypting Data with a Password or a Passphrase

The most basic level of encrypting data in SQL Server is the ENCRYPTBYPASSPHRASE()function, which allows you to encrypt data by directly using a password or a passphrase, rather the SQL Server encryption hierarchy.

To illustrate this, let’s look at the Sales.CreditCard table in the AdventureWorks2016 database. This table stores information on customers’ credit card information in plain text, which is not a great idea from a security perspective and it may also be against regulatory requirements on data protection.

Imagine that the company’s compliance department has noticed this issue during an audit and has tasked you with encrypting the credit card number column. To encrypt this column, you need to perform the following tasks:

• Create a new column of type VARBINARY

• Encrypt the values in the CardNumber column and insert them into the new column

• Drop the original column

• Update queries and ETL processes to use the new column

Listing 5-4. Drop Dynamic Data Mask

USE AdventureWorks2016
GO

ALTER TABLE Sales.CreditCard   
ALTER COLUMN CardNumber DROP MASKED ;  

The first task is to add a new column to the table. This can be achieved using the script in Listing 5-5. Because the column initially has no values and there is no DEFAULT constraint, you should allow NULL values. This can be changed once the column is populated.

Listing 5-5. Add a New Column to Hold the Encrypted Credit Card Numbers

USE AdventureWorks2016
GO

ALTER TABLE Sales.CreditCard ADD
        CardNumberEncrypted varbinary(8000) NULL ;

The next task is to populate the new column. To achieve this, encrypt the values of the CardNumber column, using the ENCRYPTBYPASSPHRASE() function. This function accepts the parameters described in Table 5-1.

The script in Listing 5-6 demonstrates how the CardNumberEncrypted column can be populated.

Listing 5-6. Populate the Encrypted Column

  UPDATE Sales.CreditCard
  SET CardNumberEncrypted = ENCRYPTBYPASSPHRASE('Pa$$w0rd', CardNumber, 0) ;

You can now set the CardNumberEncrypted column to not allow NULL values and to drop the original column. This is demonstrated in Listing 5-7.

Listing 5-7. Set Encrypted Column NOT NULL and Drop Original Column

--Set CardNumberEncrypted column to be NOT NULL
ALTER TABLE Sales.CreditCard
ALTER COLUMN CardNumberEncrypted VARBINARY(256) NOT NULL ;

--Do not run following section, if you plan to follow later examples

DROP INDEX AK_CreditCard_CardNumber ON Sales.CreditCard ;
GO

ALTER TABLE Sales.CreditCard
        DROP COLUMN CardNumber                                                                                                 ;

Changing ETL processes and queries depends on how your database is being used, of course. The AdventureWorks2016 database is an OLTP database, so it is likely that credit card numbers are updated either by salespeople, or by customers directly, as opposed to via ETL process. There may be downstream ETL processes, however, which move the data into a data warehouse or archived database.

Let’s assume that there is a stored procedure that was previously used to return customers’ credit card information from a web portal. This fictional stored procedure is shown in Listing 5-8. Assume that the user’s BusinessEntityID has been determined elsewhere in the front-end app based upon login information.

Listing 5-8. Return Credit Card Information

CREATE PROCEDURE ReturnCredCardInfo @BusinessEntityID INT
AS
BEGIN
        SELECT p.BusinessEntityID, p.firstName, p.LastName, cc.CardNumber, cc.CardType, cc.ExpMonth, cc.ExpYear
        FROM Person.Person p
        INNER JOIN Sales.PersonCreditCard pcc
                ON p.BusinessEntityID = pcc.BusinessEntityID
                INNER JOIN Sales.CreditCard cc
                        ON pcc.CreditCardID = cc.CreditCardID
        WHERE p.BusinessEntityID = @BusinessEntityID ;
END

To work with the new, encrypted column, you need to modify the stored procedure to use the DECRYPTBYPASSPHRASE() function. This function accepts the parameters described in Table 5-2.

The script in Listing 5-9 demonstrates how to rewrite the procedure. Note that in addition to decrypting the column, you must also convert the result back to an NVARCHAR value for meaningful results to be returned. You know that 25 characters is sufficient for the NVARCHAR value, because it is the length of the original CardNumber column.

Listing 5-9. Modify the Procedure to Work with the Encrypted Column

ALTER PROCEDURE ReturnCredCardInfo @BusinessEntityID INT
AS
BEGIN
        SELECT p.BusinessEntityID
                , p.firstName
                , p.LastName
                , CONVERT(NVARCHAR(25),
                        DECRYPTBYPASSPHRASE('Pa$$w0rd',cc.CardNumberEncrypted, 0)
                  )
                ,  cc.CardType
                , cc.ExpMonth
                , cc.ExpYear
        FROM Person.Person p
        INNER JOIN Sales.PersonCreditCard pcc
                ON p.BusinessEntityID = pcc.BusinessEntityID
                INNER JOIN Sales.CreditCard cc
                        ON pcc.CreditCardID = cc.CreditCardID
        WHERE p.BusinessEntityID = @BusinessEntityID ;
END

This approach still leaves a security hole, however. The data is decrypted by the application, so although the user may not have the permissions to see some data, all of the data is decrypted. This is appropriate in some scenarios, such as when a sales team manages credit card information and customers do not have direct access to the application .

Imagine a scenario, however, where you want users to be able to manage their own credit card information. In this instance, you might want to ensure that all data remains encrypted, except for a user’s own credit card number.

To implement this strategy, when a user inputs his credit card information, it is encrypted using the password that the customer uses to log in to the application. The front-end application can simply pass the credit card number and the user’s password to a stored procedure, via parameters. Listing 5-10 describes two stored procedures. The first can be used by the front-end application to add a new credit card. The second returns the credit card number. Notice that the ENCRYPTBYPASSPHRASE() and DECRYPTBYPASSPHRASE() functions accept variables as parameters, as well as hard-coded strings.

Listing 5-10. Encrypt and Decrypt Data Based Upon a User’s Password

USE AdventureWorks2016
GO
CREATE PROCEDURE dbo.AddCreditCard
                                        @BusinessEntityID         INT
                                        ,@CreditCardNumber        NVARCHAR(25)
                                        ,@CardType                NVARCHAR(50)
                                        ,@ExpMonth                TINYINT
                                        ,@ExpYear                 SMALLINT
                                        ,@Password                NVARCHAR(128)
AS
BEGIN
        DECLARE @CreditCardID        INT ;
        BEGIN TRANSACTION
                INSERT INTO Sales.CreditCard
                                ( CardType ,
                                  ExpMonth ,
                                  ExpYear ,
                                  ModifiedDate,
                                  CardNumberEncrypted
                                )                                                                                                                                                                                                                                                                                  
                VALUES  ( @CardType,
                                  @ExpMonth,
                                  @ExpYear,
                                  SYSDATETIME(),
                                  ENCRYPTBYPASSPHRASE(@Password, @CreditCardNumber, 0)
                                ) ;
                SET @CreditCardID = @@IDENTITY ;

                INSERT INTO Sales.PersonCreditCard
                                ( BusinessEntityID ,
                                  CreditCardID ,
                                  ModifiedDate
                                )
                VALUES  ( @BusinessEntityID,
                                  @CreditCardID,
                                  SYSDATETIME()
                                ) ;
        COMMIT
END
GO

CREATE PROCEDURE ReturnCredCardInfo
                                        @BusinessEntityID        INT
                                        ,@Password               NVARCHAR(128)
AS
BEGIN
        SELECT
                CONVERT(NVARCHAR(25), DECRYPTBYPASSPHRASE(@Password,cc.CardNumberEncrypted, 0)) AS CreditCardNumber
        FROM Person.Person p
        INNER JOIN Sales.PersonCreditCard pcc
                ON p.BusinessEntityID = pcc.BusinessEntityID
                INNER JOIN Sales.CreditCard cc                              
                        ON pcc.CreditCardID = cc.CreditCardID
        WHERE p.BusinessEntityID = @BusinessEntityID ;
END
GO

Encrypting Data with Keys and Certificates

When encrypting data using the SQL Server encryption hierarchy, data can be encrypted using a symmetric key, an asymmetric key, or a certificate. Table 5-3 describes the functions that are exposed by SQL Server for encrypting and decrypting data using keys and certificates.

To demonstrate how to encrypt data using a symmetric key, you first create a certificate. You then create a symmetric key, which is encrypted using this new certificate, in the AdventureWork2016 database. Next, you update the CreditCardNumberEncrypted column so that the credit card numbers are encrypted using this symmetric key, as opposed to a passphrase.

The CREATE CERTIFICATE T-SQL statement accepts the arguments described in Table 5-4 when being used to generate a new key .

The CREATE SYMMETRIC KEY T-SQL statement accepts the arguments described in Table 5-5.

The ENCRYPTBYKEY() function accepts the parameters described in Table 5-6.

Listing 5-11 demonstrates how to create the symmetric key and use it to encrypt the CardNumberEncrypted column. Notice that you need to open the key before you use it. You then close the key after you have completed the activity .

Listing 5-11. Encrypt Data with a Symmetric Key

USE AdventureWorks2016
GO

--Create the certificate
CREATE CERTIFICATE CreditCardCert
WITH SUBJECT = 'Credit Card Numbers';
GO

--Create the symmetric key
CREATE SYMMETRIC KEY CreditCardKey
WITH ALGORITHM = AES_128
ENCRYPTION BY CERTIFICATE CreditCardCert;

--Open the key
OPEN SYMMETRIC KEY CreditCardKey
DECRYPTION BY CERTIFICATE CreditCardCert;

--Encrypt the data, using the symmetric key
UPDATE Sales.CreditCard
        SET CardNumberEncrypted = ENCRYPTBYKEY(Key_GUID('CreditCardKey'), CardNumber);

--Close the key
CLOSE SYMMETRIC KEY CreditCardKey ;

Data encrypted with a symmetric key can be decrypted using the DECRYPTBYKEY() function. This function accepts the parameters described in Table 5-7.

The script in Listing 5-12 demonstrates how to use the DECRYPTBYKEY() function to read the CardNumberEncrypted column. Notice that you once again need to open and close the key .

Listing 5-12. Decrypt Data With DECRYPTBYKEY()

USE AdventureWorks2016
GO

--Open the key
OPEN SYMMETRIC KEY CreditCardKey
DECRYPTION BY CERTIFICATE CreditCardCert;

--Decrypt the data, using the symmetric key
SELECT CONVERT(NVARCHAR(30), DECRYPTBYKEY(CardNumberEncrypted)) AS CreditCardNumber
FROM Sales.CreditCard ;

--Close the key
CLOSE SYMMETRIC KEY CreditCardKey ;

Considerations for TDE with Other Technologies

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—even when data is persisted alongside the schema.

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, new full-text indexes are encrypted. Existing full-text indexes are only encrypted after they are imported during an upgrade.

If your database is replicated, then it is important to manually enable TDE on the subscribers. This is because replication does not automatically send the data from a TDE-encrypted database to the subscribers in an encrypted form.

Due to the nature of TempDB, this system database is always encrypted using TDE, if any user database on the instance has TDE enabled. This stops potential attackers from stealing data at rest while it is spooled to TempDB or stored in a temporary table, and so forth. It does mean, however, that databases on the instance that are not enabled for TDE may still notice a performance penalty, which is caused by TDE.

TDE is incompatible with instant file initialization. Instant file initialization speeds up operations that create or expand files, because 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 any files that are associated with any databases that you encrypt with TDE. This is because of a hard technical requirement for files to be zeroed out when TDE is enabled on a database.

Files used by buffer cache extensions are not encrypted by TDE. If you wish to encrypt the files associated with buffer cache extensions, then you must use system-level encryption tooling.

Implementing TDE

Implementing Transparent Data Encryption involves the following steps:

1. Create a service master key for the master database (If one does not already exist).

2. Create a certificate or asymmetric key in the master database .

3. Create a database encryption key in the database that you wish to encrypt.

4. Alter the database to enable Transparent Database Encryption.

When you enable TDE for a database, a background process moves through each page in every data file and encrypts it. This does not make the database inaccessible, 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 the database

• Taking the database offline

• Detaching a database

• Setting a database or filegroup as READ_ONLY

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 process involves changing the data within the pages to obfuscate them.

The script in Listing 5-13 follows the steps required to encrypt the AdventureWorks2016 database. The arguments accepted by the CREATE DATABASE ENCRYPTION KEY statement are described in Table 5-8.

Listing 5-13. Encrypt the AdventureWorks2016 Database

USE Master
GO

Create the Database Master Key (if it does not already exist)

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

--Create the Server Certificate

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

USE AdventureWorks2016
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 AdventureWorks2016
SET ENCRYPTION ON                                   ;
GO

Administering TDE

When working with TDE-encrypted databases, there are administrative scenarios that you should be aware of. These are discussed in the following sections.

BACKUP CERTIFICATE TDECert
TO FILE = 'C:certificatesTDECert'
WITH PRIVATE KEY (file='C:certificatesTDECertKey',
ENCRYPTION BY PASSWORD='Pa$$w0rd') ;

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'
) ;

Implementing Always Encrypted

When implementing Always Encrypted, the creation of tables with encrypted columns and the creation of key metadata are supported in T-SQL, PowerShell, or via the SSMS GUI. Other activities, however, such as provisioning keys and the actual encryption of data are only supported in PowerShell or via the SSMS GUI. They cannot currently be achieved with T-SQL. Therefore, this section demonstrates how to configure Always Encrypted via SSMS.

You will use Always Encrypted to secure the CreditCardNumber, ExpMonth, and ExpYear columns of the Sales.CreditCard table of the AdventureWorks2016 database. To achieve this, the first step is to create a column master key . You will use the Windows Certificate Store to store this key.

In Object Explorer, drill though Databases ➤ AdventureWorks2016 ➤ Security ➤ Always Encrypted Keys and select New Column Master Key from the context menu of the Column Master Keys node. This causes the New Column Master Key dialog box to be invoked, as illustrated in Figure 5-3.

Figure 5-3. New Column Master Key dialog box

In this dialog box, you enter a name for the column master key and then selected the type of store that the key is stored in from the Key Store drop-down list. Table 5-9 describes all possible values for Key Store. You can choose an existing key or certificate, or alternatively use the Generate Certificate button to create a self-signed certificate in the appropriate store to use as the column master key . In this example, a self-signed certificate was generated.

If you generate the certificate, as opposed to selecting an existing certificate, it immediately appears within the chosen key store. For example, Figure 5-4 shows the certificate within the Current User area of the Windows Certificate Store.

Figure 5-4. Windows Certificate Store

Now that the column master key has been created, you can generate a column encryption key . To do this, you select New Column Encryption Key from the context menu of the Databases ➤ AdventureWorks2016 ➤ Security ➤ Always Encrypted Keys ➤ Column Encryption Keys node in Object Explorer. This causes the New Column Encryption Key dialog box to be invoked, as illustrated in Figure 5-5.

Figure 5-5. New Column Encryption Key dialog box

In this dialog box, you have entered a name for the column encryption key and selected the appropriate column master key from the drop-down list.

The final step is to encrypt the CreditCardNumber, ExpMonth, and ExpYear columns. When encrypting the data, you have a choice of two methods: deterministic or randomized. This is an important decision to understand, as it may have an impact of performance as well as security.

Deterministic encryption always produces the same encrypted value for the same plain text value. This means that if deterministic encryption is used, operations—including equality joins, grouping and indexing—are possible on an encrypted 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 plain text values. This means that while encryption loopholes are plugged, equality joins, grouping, and indexing are not supported against the encrypted data.

You will use deterministic encryption because you expect the columns to have a high cardinality. You will again use SSMS for this action because T-SQL only has support for encrypting data in new columns, not existing columns. The process of encrypting the data includes changing the column collation to BIN2, because this is the only collation currently supported by Always Encrypted.

To invoke the Always Encrypted wizard for the CardNumber column, drill though Databases ➤ AdventureWorks2016 ➤ Tables in Object Explorer. Select Encrypt Columns from the context menu of the Sales.CreditCard table. After passing through the welcome page of the wizard, the Column Selection page is displayed, as illustrated in Figure 5-6.

Figure 5-6. Column selection page

On this page, you first use the check boxes on the left-hand side to select the columns that you want to encrypt. You then have a choice of selecting an encryption key for each column individually, or using the check box and drop-down list at the top of the page to choose a single key that is used to encrypt all selected columns. You have used the latter option. Finally, you need to specify if each column should be encrypted using deterministic or randomized encryption.

The warning next to the CardNumber column is informing you that the column’s collation is changed to the supported BIN2 collation. It is not possible to select the DateModified column because there is a default constraint on the column. This is not supported by Always Encrypted.

The Master Key Configuration page simply informs you that no further configuration is required. If you had chosen to create new column encryption key s on the Column Selection page, then you could use this page to associate the new keys with a column master key .

The Run Settings page provides an option for performing the encryption immediately or scripting the action out to PowerShell. The Summary page provides an overview of the actions to be performed. After clicking the Finish button on the Summary page, the encryption is performed. The Results page should be reviewed for success status.