CHAPTER 10

image

Encryption

Encryption is a process of obfuscating data with an algorithm that uses keys and certificates so that if security is bypassed and data is accessed or stolen by unauthorized users, then it will be useless, unless the keys that were used to encrypt it are also obtained. This adds an additional layer of security over and above access control, but it does not replace the need for an access control implementation. Encrypting data also has the potential to considerably degrade performance, so you should use it on the basis of need, as opposed to implementing it on all data as a matter of routine.

In this chapter, we discuss the SQL Server encryption hierarchy before demonstrating how to implement Transparent Data Encryption (TDE) as well as cell-level encryption. We also discuss the management and performance implications of these technologies.

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.

Encryption Concepts

Before we discuss the hierarchy in detail, it is important to understand the concepts that relate to encryption. The following sections provide an overview of the main artifacts that are involved in encryption.

Symmetric Keys

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

Asymmetric Keys

In contrast to a symmetric key, which uses the same algorithm to both encrypt and decrypt data, an asymmetric key uses a pair of keys or algorithms. You can use one for encryption only and the other for decryption only. 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 and provides a digitally signed statement, which binds the public key to a principal or device, which holds the corresponding private key.

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 or domain secret information. DPAPI is used to encrypt the Service Master Key, which is the top level of the SQL Server encryption hierarchy.

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. In SQL Server 2012 and above, 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.

Because of the new encryption algorithm used in SQL Server 2012 and above, when you upgrade an instance from SQL Server 2008 R2 or below, it is good practice to regenerate the key.

The issue with regenerating the Service Master Key, however, is that it involves decrypting and then re-encrypting 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 10-1. You should be aware, however, that if the process fails to decrypt and re-encrypt 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 re-encrypted unusable. You will have no way to regain access to this data.

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, offsite 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 10-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 re-encrypted.

Image 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 10-3.

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

As with the Service Master Key, if the restore is unable to decrypt and re-encrypt 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 loose access to the data encrypted using the key(s) that could not be decrypted and re-encrypted.

Hierarchy

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

9781484207116_Fig10-01.jpg

Figure 10-1. Encryption hierarchy

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 10-5 demonstrates how you might import the Thales EKM module after you install the database security pack.

Image 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, 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.

Image Caution  Even if you enable TDE at the subscriber, data is still stored in plain text while it is in intermediate files. This, arguably, poses a greater risk than using FTE (Full-Text Indexes), so you should closely consider the risk/benefit scenario.

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. TDE performance considerations are further discussed later in this chapter.

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.

Image 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

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 10-6 creates a database called Chapter10Encrypted and then creates a table that is populated with data. Finally, it creates a Database Master Key and a server certificate.

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 10-2.

9781484207116_Fig10-02.jpg

Figure 10-2. Manage Database Encryption wizard

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.

Image Note  Choosing an algorithm is essentially a tradeoff 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 10-7.

Managing TDE

When configuring TDE, we are given a warning that the certificate used to encrypt the Database Encryption Key has not been backed up. Backing up this certificate is critical and you should do so before you configure TDE or immediately afterward. If the certificate becomes unavailable, you have no way to recover the data within your database. You can back up the certificate by using the script in Listing 10-8.

Migrating an Encrypted Database

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

9781484207116_Fig10-03.jpg

Figure 10-3. An attempt to restore an encrypted database on a new instance

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 10-9.

Image 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.

Performance Considerations for TDE

In order to observe the performance implications of Transparent Data Encryption, we create a duplicate of the Chapter10 Encrypted database, and store it in plain text, as opposed to encrypting it. The code to create the Chapter10 database is in Listing 10-10.

We now run a SELECT statement and an INSERT statement against the Chapter10 database as a benchmark of performance. You can view this script in Listing 10-11.

Figure 10-4 shows the results of the benchmark queries.

9781484207116_Fig10-04.jpg

Figure 10-4. Results of benchmark queries against plain-text database

We now repeat the benchmark against the Chapter10 Encrypted database so that we can compare the performance. Figure 10-5 shows the results of running the same script against the encrypted database.

9781484207116_Fig10-05.jpg

Figure 10-5. Results of benchmark queries against encrypted database

You can see that the SELECT query ran approximately 10 percent slower against the encrypted database. The INSERT statement ran approximately 15 percent slower than it did on the plain-text version of the database. This result depends on many factors including processor speed, disk speed, and server load.

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 10-1 lists the cryptographic functions that you can use to encrypt or decrypt data using these methods.

Table 10-1. Cryptographic Functions

Encryption Method

Encryption Function

Decryption Function

Asymmetric key

ENCRYPTBYASYMKEY()

DECRYPTBYASYMKEY()

Certificate

ENCRYPTBYCERT()

DECRYPTBYCERT()

Passphrase

ENCRYPTBYPASSPHRASE()

DECRYPTBYPASSPHRASE()

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.

The script in Listing 10-12 will create a duplicate of the Chapter10Encrypted database. The script then creates a Database Master Key for this database and a certificate. After that, it creates a symmetric key that will be encrypted using the certificate. Finally, it opens the symmetric key and uses it to encrypt the CreditCardNumber column in our SensitiveData table.

Notice that the UPDATE statement that we used to encrypt the data uses a function called ENCRYPTBYKEY() to encrypt the data. Table 10-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.

Table 10-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

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 10-12, you still have a security risk caused by the deterministic nature of the algorithm used for 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.

Image Caution  It is very important that the values in the authenticator column are never updated. If they are, you may lose access to your sensitive data.

The script in Listing 10-13 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.

Image Tip  This script is included as an example, but you should avoid running it at this point so you are able to follow later code examples.

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 10-3 describes the parameters for this function.

Table 10-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 10-14 demonstrates how to read the encrypted data in the CreditCardNumber column using the DECRYPTBYKEY() function after it has been encrypted without an authenticator.

The sample of the results from the final query in this script is shown in Figure 10-6. 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.

9781484207116_Fig10-06.jpg

Figure 10-6. Results of DECRYPTBYKEY( )

Performance Considerations for Cell-Level Encryption

Cell-level encryption has implications for both the performance and the size of your database. The script in Listing 10-15 compares the size of the CreditCardNumber column in the Chapter10 and Chapter10CellEncrypted databases.

You can see from the results in Figure 10-7 that the encrypted CreditCardNumber column consumes almost 65MB of space, compared to the 18MB of space consumed by the plain-text version of the column. If we had used a longer algorithm, then there would have been additional bloat.

9781484207116_Fig10-07.jpg

Figure 10-7. Encryption bloat results

Let’s now repeat the benchmark test that we performed against the TDE encrypted database earlier in this chapter. This time, we run the benchmark against the Chapter10CellEncrypted database to compare performance. Because we need to decrypt the data, we need to modify the benchmarking script. Listing 10-16 contains the modified version.

You can see from the results in Figure 10-8 that there are some drastic performance overheads. The SELECT statement took over eight times longer to execute and the INSERT was 34 percent slower.

9781484207116_Fig10-08.jpg

Figure 10-8. Performance benchmark results

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.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.116.60.158