Encrypting Data, Methods of Data Encryption

Encryption is a process of obfuscating data, thereby enhancing the data security. Generally, the concrete encryption procedure is carried out using an algorithm. The most important algorithm for encryption is called RSA. (It is an acronym for Rivers, Shamir, and Adelman, the last names of the three men who invented it.)

The Database Engine secures data with hierarchical encryption layers and a key management infrastructure. Each layer secures the layer beneath it, using a combination of certificates, asymmetric keys, and symmetric keys (see Figure 12-1).

The service master key in Figure 12-1 specifies the key that rules all other keys and certificates. The service master key is created automatically when you install the Database Engine. This key is encrypted using the Windows Data Protection API (DPAPI).

The important property of the service master key is that it is managed by the system. Although the system administrator can perform several maintenance tasks, the only task he or she should perform is to back up the service master key, so that it can be restored if it becomes corrupted.

As you can see in Figure 12-1, the database master key is the root encryption object for all keys, certificates, and data at the database level. Each database has a single database master key, which is created using the CREATE MASTER KEY statement (see Example 12.1). Because the database master key is protected by the service master key, it is possible for the system to automatically decrypt the database master key. Once the database master key exists, users can use it to create keys. There are three forms of user keys:

• Symmetric keys
• Asymmetric keys
• Certificates

The following subsections describe the user keys.

Symmetric Keys

An encryption system that uses symmetric keys is one in which the sender and receiver of a message share a common key. Thus, this single key is used for both encryption and decryption.

Using symmetric keys has several benefits and one disadvantage. One advantage of using symmetric keys is that they can protect a significantly greater amount of data than can the other two types of user keys. Also, using this key type is significantly faster than using an asymmetric key.

On the other hand, in a distributed environment, using this type of key can make it almost impossible to keep encryption secure, because the same key is used to decrypt and encrypt data on both ends. So, the general recommendation is that symmetric keys should be used only with applications in which data is stored as encrypted text at one place.

The Transact-SQL language supports several statements and system functions related to symmetric keys. The CREATE SYMMETRIC KEY statement creates a new symmetric key, while the DROP SYMMETRIC KEY statement removes an existing symmetric key. Each symmetric key must be opened before you can use it to encrypt data or protect another new key. Therefore, you use the OPEN SYMMETRIC KEY statement to open a key.

After you open a symmetric key, you need to use the EncryptByKey system function for encryption. This function has two input parameters: the ID of the key and text, which has to be encrypted. For decryption, you use the DecryptByKey function.

Asymmetric Keys

If you have a distributed environment or if a symmetric key does not keep your encryption secure, use asymmetric keys. An asymmetric key consists of two parts: a private key and the corresponding public key. Each key can decrypt data encrypted by the other key. Because of the existence of a private key, asymmetric encryption provides a higher level of security than does symmetric encryption.

The Transact-SQL language supports several statements and system functions related to asymmetric keys. The CREATE ASYMMETRIC KEY statement creates a new asymmetric key, while the ALTER ASYMMETRIC KEY statement changes the properties of an asymmetric key. The DROP ASYMMETRIC KEY statement drops an existing asymmetric key.

After you create an asymmetric key, use the EncryptByAsymKey system function to encrypt data. This function has two input parameters: the ID of the key and text, which has to be encrypted. For decryption, use the DecryptByAsymKey function.

Certificates

A public key certificate, usually simply called a certificate, is a digitally signed statement that binds the value of a public key to the identity of the person, device, or service that holds the corresponding private key. Certificates are issued and signed by a certification authority (CA). The entity that receives a certificate from a CA is the subject of that certificate.

Certificates contain the following information:

• The subject’s public key value
• The subject’s identifier information
• Issuer identifier information
• The digital signature of the issuer

A primary benefit of certificates is that they relieve hosts of the need to maintain a set of passwords for individual subjects. When a host, such as a secure web server, designates an issuer as a trusted authority, the host implicitly trusts that the issuer has verified the identity of the certificate subject.

The most important statement related to certificates is the CREATE CERTIFICATE statement. Example 12.1 shows the use of this statement.

USE sample;
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'p1s4w9d16!'
GO
CREATE CERTIFICATE cert01
	WITH SUBJECT = 'Certificate for dbo';Code language: PHP (php)

If you want to create a certificate without the ENCRYPTION BY option, you first have to create the database master key. (Each CREATE CERTIFICATE statement that does not include this option is protected by the database master key.) For this reason, the first statement in Example 12.1 is the CREATE MASTER KEY statement. After that, the CREATE CERTIFICATE statement is used to create a new certificate, cert01, which is owned by dbo in the sample database, if the current user is dbo.

Editing User Keys

The most important catalog views in relation to encryption are the following:

• sys.symmetric_keys
• sys.asymmetric_keys
• sys.certificates
• sys.database_principals

The first three catalog views provide information about all symmetric keys, all asymmetric keys, and all the certificates installed in the current database, respectively. The sys.database_principals catalog view provides information about each of the principals in the current database. (You can join the last catalog view with any of the three others to see information about who owns a particular key.)

Example 12.2 shows how the existing certificates can be retrieved. (In a similar way, you can get information concerning symmetric and asymmetric keys.)

select p.name, c.name, certificate_id
	from sys.database_principals p, sys.certificates c
	where p.principal_id = p.principal_idCode language: JavaScript (javascript)

A part of the result is

SQL Server Extensible Key Management

Another step in achieving greater key security is the use of Extensible Key Management (EKM). EKM has the following main goals:

• Enhanced key security through a choice of encryption provider
• General key management across your enterprise

EKM allows third-party vendors to register their devices in the Database Engine. Once the devices are registered, SQL Server logins can use the encryption keys stored on these modules as well as leverage advanced encryption features that these modules support. EKM also allows data protection from database administrators (except members of the sysadmin group). That way, you can protect the system against users with elevated privileges. Data can be encrypted and decrypted using Transact-SQL cryptographic statements, and SQL Server uses the external EKM device as the key store.

Methods of Data Encryption

SQL Server supports two methods of data encryption:

• Column-level encryption
• Transparent Data Encryption

Column-level encryption allows the encryption of particular data columns. Several pairs of complementary functions are used to implement column-level encryption. I will not discuss this encryption method further because its implementation is a complex manual process that requires the modification of your application.

Transparent Data Encryption (TDE) introduces a new database option that encrypts the database files automatically, without needing to alter any applications. That way, you can prevent the database access of unauthorized persons, even if they obtain the database files or database backup files.

Encryption of the database file is performed at the page level. The pages in an encrypted database are encrypted before they are written to disk and decrypted when they are read into memory.

TDE, like most other encryption methods, is based on an encryption key. It uses a symmetric key, which secures the encrypted database.

For a particular database, TDE can be implemented in four steps:

1. Create a database master key using the CREATE MASTER KEY statement. (Example 12.1 shows the use of the statement.)
2. Create a certificate using the CREATE CERTIFICATE statement (see Example 12.1).
3. Create an encryption key using the CREATE DATABASE ENCRYPTION KEY statement.
4. Configure the database to use encryption. (This step can be implemented by setting the SET ENCRPYTION clause of the ALTER DATABASE statement to ON.)