Introduction

IT security is, generally, defined as a defensive approach to protect a company and its assets from unauthorized access by an intruder. IT security efforts include network security appliances, HoneyPots, robust authentication, limiting authorization to least necessary privileges, as well as other perimeter security defenses. However, these approaches do not provide definitive protection of the company's most valuable asset, its data, because a single intrusion could result in sensitive data being compromised. Additionally, in today's workplace culture the disgruntled employee may be as much of a threat as any external threat.

Data encryption is a direct response to internal and external security threats that may also meet compliance regulations. Encryption provides strong security for data "at-rest"; in our case, the data stored in the database, but to be effective should be implemented as a part of a broader security plan. There are many issues involved with the implementation of encryption, details that require decisions and actions to ensure the success of the implementation and the security of the data. This document will discuss the issues associated with database encryption implemented using SQL Server's native Transparent Database Encryption (TDE) mechanism.

Encryption Overview

Encryption has been integral to human history beginning with the Babylonian use of Intaglio other historical examples include the Caesar Cipher, Scytale Transposition Cipher, Enigma, and even JimKryptos sculpture. Throughout history our society has enjoyed the ability to protect information using cryptographic methods including steganography, microdots, invisible ink, digital watermarks, and encryption which may be defined as the conversion of data so as to keep its meaning private. As the amount of sensitive data collected by commercial entities continues to grow the regulatory requirements for protecting the sensitive data will become more robust; meeting the regulatory requirements will necessarily require the continued use of data encryption methods.

Encryption requires the application of an algorithm to transform the target data into a form that is unusable to anyone that does not have access to the encryption process used. In practical terms encryption applies a cryptographic algorithm with a "key" to the target data producing the encrypted form of the data which cannot be accessed without the key used to encrypt the data. The two primary forms of key encryption are symmetric and asymmetric which are distinguished by the number of keys used in the encryption / decryption process. Symmetric encryption uses a single key while asymmetric encryption uses a pair of keys generally referred to as public and private keys.

While asymmetric encryption appears ideal for implementation because only the public key need ever be shared there are disadvantages with regard to performance. A sampling of asymmetric algorithms includes RSA, DSA, ELGamal, ECDSA, and XTR. Figure 1 demonstrates the asymmetric encryption process.

Figure 1 Asymmetric Key Encryption / Decryption Process

Symmetric algorithms require a single key for both encryption and decryption which allows for high-performance; however, with this approach the strength of the encryption is dependent on the security of the key. Common symmetric algorithms include AES/Rijndael, Blowfish, DES, Triple DES, Serpent, and IDEA to name only a few. Figure 2 demonstrates the symmetric encryption process.

Figure 2 Symmetric Key Encryption Process

Both symmetric and asymmetric encryption approaches are vulnerable to brute force attacks and cryptanalysis. Brute force is an attack during which every possible permutation of the key value is attempted. Cryptanalysis, on the other hand, applies computational techniques to circumvent the encryption. In general, the use of sufficiently long keys will mitigate these attacks.

In summary, a symmetric key algorithm is fast but less secure than an asymmetric algorithm. Another approach is a hybrid wherein a symmetric key is used to encrypt the data while an asymmetric key is used to encrypt the symmetric key. It may be important to know in order to maintain perspective that there is only one encryption algorithm that is impossible to crack, One-Time Pad (OTP), any other algorithm may be broken given sufficient time and / or computer resources.

Security concerns, in general, and encryption, specifically, are new concepts for most IT professionals; therefore, a Glossary of Security / Encryption Terms is included as an appendix for reference.

Overview of Transparent Database Encryption

The primary benefit of Transparent Database Encryption (TDE) is the ability to encrypt data without affecting any application that uses the data while providing security for the entire database. TDE is implemented at the database-level, unlike cell-level encryption TDE does not require modification to applications or database column data types; furthermore, database-level encryption allows for higher performance than cell-level encryption. However, TDE may allow more data leakage because encrypted data is decrypted when read into the buffer pool; therefore, the data is not protected if the operating system writes data from memory to disk during paging operations, or during hibernation, or memory dumps, nor is the data protected while in memory.

Database encryption is achieved by leveraging the Data Protection API (DPAPI) in Windows® which protects the Service Master Key (SMK) which protects the Database Master Key (DMK) which is used to protect the certificate or asymmetric keys which are used to protect the Database Encryption Key (DEK). These dependencies create a security chain from the operating system to the data eliminating user interaction thus strengthening security. The relationships and dependencies between keys is represented in Figure 3 below:

Figure 3 SQL Server encryption key hierarchy with TDE and EKM (Source: BOL - http://msdn.microsoft.com/en-us/library/cc278098.aspx)

The hierarchy of keys in TDE is protected from the DPAPI to the DEK allowing the server to manage encryption and decryption automatically. The DMK and the certificate are stored in the MASTER database while the DEK is stored in the user database. This hierarchy and the key management chain provide TDE the capability to transparently encrypt and decrypt the database.

The process for encrypting a database is conceptually simple:

1. Create a Master Key
2. Obtain an Authentication Certificate
3. Create DEK
4. Enable TDE on the database

However, significant complexity will be introduced if the database encryption strategy is undertaken without proper planning that addresses important implementation issues. Those issues are discussed in the following section.

Encryption Issues

The level of security necessary to protect the database should be documented during the planning phase. Individually and in combination the following encryption mechanisms are available to secure the database:

• Encrypting File System (EFS)
• Cell-level
• BitLocker
• Transparent Database Encryption (TDE)

Discussion of the benefits and performance implications of each mechanism and their combinations is beyond the scope of this paper.

Data encryption must address two equally important issues: encryption technology and cryptographic key (key) management. Encryption technology provides for variable granularity of data protection, performance, and integration with existing applications, as well as ease of implementation and management. However, the success of the selected encryption strategy may depend most on key management policies and processes. Key management issues include: key access, key storage, and cryptographic algorithm. Key management is one of many important issues that must be considered when planning the encryption project.

The important issues to consider during the planning phase of the encryption project are listed below:

• Encryption Algorithm : DES, Triple DES, TRIPLE_DES_3KEY, RC2, RC4, 128-bit RC4, DESX, 128-bit AES, 192-bit AES, and 256-bit AES
• Key Management : Key Storage, Hardware Security Module (HSM), Key Scheduling, Key Availability / Mobility / Security
• Performance Impact. Encryption / Decryption - Microsoft claims 3-5%; however, independent tests indicate 6-12%..
• TempDB Encryption - Encryption of any one DB will encrypt TempDB.
• Transaction Log is encrypted.
• Log Shipping Implementation Changes - Encrypted database log shipping requires the recipient database to possess the key in order to apply the logs.
• Backup and Recovery Plan Changes - Encrypted databases cannot be recovered to a different instance without the key.
• Disaster Recovery Plan Changes - Encrypted databases cannot be recovered to a different instance without the key.
• Increased Disk Space Requirements - No SQL Server native backup compression. Third party tools may be available; however, in general, encrypted data cannot be significantly compressed.
• TDE operates during I/O; therefore, any data written to disk outside of the buffer pool is not protected
• No Support for FILESTREAM data-type

The diagram in Figure 4 represents a nominal encryption project planning process with each major area of consideration represented. The end result of the planning process is to produce a document detailing the decisions made that address the issues related to encrypting the database.

Figure 4 Encryption Planning Process

Summary

A comprehensive IT security policy provides a layered defense against threats to the system. However, even the most thorough perimeter network and physical defenses do not obviate the vulnerability of plaintext data stored in databases. Data encryption provides a means to protect sensitive data from unauthorized access as a part of a coordinated IT security policy that includes network security, robust authentication and authorization, as well as other physical security considerations. SQL Server and Windows® provide several mechanisms for the protection of data either at the file, database, or data levels.
Transparent database encryption (TDE) is a new technology available in SQL Server 2008 Enterprise Edition which provides a simplified the data encryption option. TDE is a database-level encryption mechanism that reduces the implementation complexity by negating the need to modify the data and / or the client applications. However, the benefits of performance and simplicity are balanced by TDE's potential for data leakage; therefore, for the most sensitive data TDE alone may not suffice as a data security strategy.

Any data protection strategy must weigh the costs and benefits of implementation to arrive at a usable solution that meets the security requirements defined by the business. TDE's protection of sensitive data in low to moderate threat environments may be sufficient for some business requirements while highly sensitive data or data in high threat environments will require the combination of TDE with other encryption mechanisms such as cell-level encryption, EFS, or BitLocker.