Overview

This section provides an overview of how the Extensible Key Management (EKM) API, as provided for Microsoft SQL Server, can be used to protect databases when using nShield Web Services SQLEKM. A brief description of how to perform encryption using Microsoft SQL Server with the Web Services 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.

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 keys to protect its databases. These 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 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 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.

The Web Services SQLEKM provider supports the generation and management of these keys. Authorized access to the secure environment of a HSM, and the keys under its protection, is controlled by a protection domain. To use a protection domain, 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.

  • Knowledge of the correct protection domain(s) and associated passphrase(s).

  • For CLE, knowledge of the keys in use, and their passwords (if any).

  • Knowledge of the appropriate encrypted database to read or write to.

If Security World data (or 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 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 Web Services SQLEKM provider to perform encryption, see Using the SQLEKM provider.

Querying encrypted data

When the client sends a query to SQL Server, the Web Services 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 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) 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 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.

TDE encryption/decryption only

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.

TDE and CLE/decryption

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