Overview

This chapter provides an overview of how the Extensible Key Management (EKM) API, as provided for Microsoft SQL Server, can be used to protect databases through encryption. It explains how the nShield Database Security Option Pack supports this by including the security benefits of an nShield HSM and associated nShield Security World software. A brief description of how to perform encryption operations on Microsoft SQL Server using the SQLEKM provider is also given.

Encryption should be part of a wider scheme of security practices to protect your database assets that should take into account any regulatory or legal requirements for data protection. Administration and management of encryption within any organization is a serious issue that requires appropriate training and resources.

Data in transit between a database server and client may not be encrypted. Communication between servers and clients should be independently encrypted to ensure security during data transmission. The encryption schemes described here are designed only to protect data at rest.

Figure 2.1 provides a graphical overview of the cryptographic architecture outlined here.

Figure 2.1: Cryptographic architecture

A Microsoft SQL Server service permits the creation of one or more databases. When a client request is made to the SQL Server, it determines which of the databases are the subject of the query, and loads data that is the subject of the query into available memory from disk storage.

From a security perspective, the Microsoft SQL Server supports the use of cryptographic keys to protect its databases. These encryption keys can be used to perform two levels of encryption.

Transparent Data Encryption (TDE) is used to encrypt an entire database in a way that does not require changes to existing queries and applications. A database encrypted with TDE is automatically decrypted when SQL Server loads it into memory from disk storage, which means that a client can query the database within the server environment without having to perform any decryption operations. The database is encrypted again when saved to disk storage. When using TDE, data is not protected by encryption whilst in memory. Only one encryption key at a time per database can be used for TDE.
To use Cell-Level Encryption (CLE), you must specify the data to be encrypted and the key(s) with which to encrypt it. CLE uses one or more keys to encrypt individual cells or columns. It gives the ability to apply fine-grained access policies to the most sensitive data in a database. Only the specified data is encrypted: other data remains unencrypted. This mode of encryption can minimize data exposure within the database server and client applications. You can apply CLE to database tables that are also encrypted using TDE. Note that when using CLE, data is only decrypted in memory when required for use. Separate data can be encrypted using different encryption keys within the same data table.

There may be performance trade-offs between speed and security, regarding use of TDE or CLE, but these issues are beyond the scope of this document.

Cryptographic keys can be stored by the database itself, or off-loaded to a SQLEKM provider. Use of a SQLEKM provider is more secure because encryption keys are stored separately from the associated encrypted data. Typically, a SQLEKM provider will also support encryption acceleration and enhanced facilities dedicated to the generation, back up, management and secure protection of the encryption keys. These facilities become more important as the amount of encrypted data, and the number of encryption keys, increases.

Use of the nShield SQLEKM provider in conjunction with an nShield HSM provides the following benefits:

Ability to store keys from across an enterprise in one place for easy management
Key retention (rotate keys while keeping the old ones)
Reduced costs of regulatory compliance
FIPS certification
Common criteria certification.

When the nShield HSM(s), Security World and nShield SQLEKM provider software have been correctly set up, the appropriate encryption keys can be made available to a Microsoft SQL Server database. Authorized access to the secure environment of a HSM and encryption keys under its protection is controlled by an Operator Card Set (OCS) or a softcard. To use an OCS or softcard, you must first set up a database credential.

To read from or write to an encrypted database, a user must have all of the following:

An authorized database login, with password, that maps to an appropriate database credential.
The correct OCS cards, or knowledge of the correct softcard(s).
The passphrase(s) associated with the OCS cards or softcard(s).
The nShield Security World holding the encryption keys.
For CLE, knowledge of the encryption keys in use, and their passwords (if any).
An nShield HSM with the software to drive it and, if necessary, the authorized administrative mechanisms to load it with the Security World data.
Knowledge of the appropriate encrypted database to read or write to.

If Security World data (or encryption keys) are lost, they can be securely recovered from a backup as authorized through secure administrative means. It is important to maintain an up-to-date backup of your data.

When use of encryption keys is legitimately made available to the database, the continuing security of data protected by those keys becomes dependent on access offered through SQL Server in accordance with your organization’s security policies.

For more information about:

Configuring the SQLEKM provider to perform encryption operations on SQL Server, see Using the SQLEKM provider.
Restoration of Security World data from backup, see Disaster recovery.

Querying encrypted data

When the client sends a query to SQL Server, the SQLEKM provider checks the level of encryption on the database that is the subject of the query. If SQL Server uses a database that employs TDE, the process of loading the assigned encryption keys and encrypting the database when it is stored is done automatically. The reverse decryption operation is also automatic when a TDE encrypted database needs to be used and is loaded into memory. If a database is encrypted using TDE only, this is transparent to the client or user who does not need to be aware of the encryption status or specify any encryption or decryption operations when querying the database. Backup and transaction logs are similarly encrypted.

CLE can be used with or without TDE. In either case, when using CLE the target data must be explicitly encrypted in memory before being stored, or explicitly decrypted after being loaded into memory from storage. You must specify:

The fields to be encrypted or decrypted.
The (correct) cryptographic key to be used.

CLE is not automatic. If you use it, you must be aware of the encryption or decryption process.

Note that if TDE is used in combination with CLE, then after the CLE has been performed, the encrypted cells will be additionally encrypted by the TDE process when the data is stored. When the TDE process decrypts, the cells are returned to memory in their original encrypted form and must be decrypted a second time using the appropriate cell-level cryptographic key. The database-level TDE processes remain automatic.

Example queries

The following example queries use a database table of customer information that includes first names, second names and payment card numbers. The queries concern the details of customers whose first names are Joe.

Example 1: TDE encryption/decryption only

In this example, the entire database is encrypted with TDE.

Figure 2.2: TDE encryption/decryption only

The database is decrypted when it is loaded into memory from disk storage. As this happens before the query is performed, the query does not have to specify any decryption operation:

USE TestDatabase
SELECT * FROM Customers WHERE
FirstName LIKE ('%Joe%');

Example 2: TDE combined with CLE/decryption

In this example, the database is encrypted with TDE, and the column of credit card numbers in the table of customers is additionally protected with CLE.

Figure 2.3: TDE and CLE/decryption

The query does not have to take account of TDE on the database because it is automatically decrypted on loading into memory from disk storage before the query is performed. However, the query must specify the (cell-level) decryption of the column of credit card numbers before the details of customers called 'Joe' can be returned.

USE TestDatabase
SELECT [FirstName], [SecondName], CAST(DecryptByKey(CardNumber) AS VARCHAR)
AS 'Decrypted card number'
FROM Customers WHERE [FirstName] LIKE ('%Joe%');