Understanding Transparent Data Encryption

14 June 2012 · Sql Server 2008 · Article · 6 visite

Understanding Transparent Data Encryption

With overall concern for data privacy as well as the increase in regulatory compliance in the area of data security, encryption is used as a method for protecting data. Encryption is a way of keeping data confidential and unreadable by unauthorized users.

SQL Server 2008 introduced transparent data encryption (TDE) to provide the ability to encrypt entire databases, data, and log files without the requirement of application changes and with minimal performance impact. TDE protects data files at rest by encrypting the data and log files for a specific database on disk. When a transaction requires data from data or log files, the specific data pages are decrypted in memory. Once a TDE is enabled on a database, all the database’s backups are encrypted. Also, tempdb will be encrypted. It’s interesting to note that filestream data (the new filestream data types) will be encrypted as well.

In the past, it was necessary to either use a third-party tool, or if you were using cell-based encryption, you would need to change the data type of the column to varbinary. This would on occasion require changes to foreign keys and make searching more complex. The application needed to make an additional call to encrypt and decrypt the data. Transparent data encryption will allow you to apply encryption to a database without having to change the application that accesses the database. All data types, keys, indexes, and so on can be used to their full potential without sacrificing security or leaking information on the disk when using transparent data encryption.

Test Day Tip

Be sure to understand which features are available in which editions, especially when it comes to encryption!

Head of the Class...: Encryption and Security

But why use encryption if my database server and facilities are physically secure?

While this may be true for your organization, the reality is the backups of the data and log files are susceptible to loss or theft. Often the backups are stored off site. Ideally they should be stored in a physically secure location. More importantly, without TDE or another post-backup encryption method, the backup data and log files can be restored on another server and information can be stolen.

More often than not, the use of backup files and tapes used for disaster recovery at remote sites as focused more for availability and the security concerns of the media are not as rigid.

The economic realities of outsourcing and downsizing have increased the real threat of losing data to theft. Regulations to maintain data privacy and integrity have created a key operational requirement in the data management business. While database encryption is not required to protect information as such as credit card, Social Security numbers, and other personally identifiable information (PII) to meet the government regulations per Sarbanes-Oxley and the Health Insurance Portability and Accountability Act of 1996 (HIPAA), it is an easier and more cost-effective solution for a growing international business problem.

Requirements

The SQLServer2008 Enterprise and Developer editions only support TDE. TDE-encrypted databases cannot be attached/used by other editions.

Enabling TDE

The steps to enable TDE on a database consist of the following:

1.	Creating a service master key at the database instance.
2.	Creating a database master key and associated certificate in the master database.
3.	Creating a database encryption key in the user database to be encrypted.
4.	Setting the user database to use the encryption.

Before describing the mechanics of creating the components of the TDE, let’s review the SQLServer2008cryptography scheme.

At its core TDE uses the Windows Data Protection application program interface (API) to encrypt and decrypt keys and data. In Microsoft Window 2000, the Data Protection API (DPAPI) was introduced to encrypt and decrypt data. Since SQLServer 2005, DPAPI is used to generate a key for the database instance, also known as the service master key (SMK). At the time the database instance is created, the SMK is generated by using the DPAPI functions and the Windows credentials of the SQLServer service account. The SMK is then encrypted using local machine credentials. The SMK can only be decrypted by the service account used during the SQLServer setup processing at installation or by an account that has access to the SQLServer service account’s Windows credentials or belongs to the same security group.

The SMK is used to encrypt and decrypt all other keys within the SQLServer instance.

Table 5.1 describes the key hierarchy in TDE to enable the encryption of a user database.

Table 5.1. The Hierarchy in TDE
Step	SQL Server Key	Dec	SQL Command
1	Service master key (SMK)	Created by SQLServer at the time of setup, the SMK is encrypted using Windows Operating System’s Data Protection API (DPAPI) and the local computer key that is derived from the Windows credentials of the SQLServer service account and the computer	BACKUP SERVICE MASTER KEY TO FILE = '<complete path and filename>' ENCRYPTION BY PASSWORD = '<password>';
2	Master database’s database master key (DMK) and certificate	The DMK is created and stored in the master database. The DMK is a symmetric key used to protect the private keys of certificates and asymmetric keys that are present in the database. When it is created, the DMK is encrypted by using the Triple Data Encryption Standard (DES) algorithm and a user-supplied password. Please note it is best practice to back up the DMK in a safe location.	USE master GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘some password’; CREATE CERTIFICATE tdeCert WITH SUBJECT = ‘TDE Certificate’; BACKUP CERTIFICATE tdeCert TO FILE = ‘path_to_file’ WITH PRIVATE KEY (FILE = ‘path_to_private_key_file’, ENCRYPTION BY PASSWORD = ‘ TDE cert p@sswurd’);
3	Database encryption key (DEK)	The DEK is stored in the boot record of the encrypted database and is created by using the DMK certificate.	CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE tdeCert

TDE encrypts the data and log files using the database encryption key (DEK) and the specified encryption algorithm defined for the target TDE database. The complexity of encryption in TDE arises from the SQLServer encryption hierarchy to create and use the DEK as well as the specified encryption algorithm.

SQL Server supports a number of encryption algorithms including 3 Key Triple DES, AES with 128-bit, 192-bit or 256-bit key. The definition or best practices of encryption keys are beyond the scope of this book, but as a general rule strong encryption algorithms require more CPU resources and in generally are slower.

Configuring & Implementing...: Backup Service Master Key

Creating a backup of the SMK is the first activity that should be done after an SQL Server installation. The SMK backup should be stored in a secure, off-site location. It’s important to protect and have a backup of the SMK, as it’s the “Master Key” to encryption for the SQL database.

BACKUP SERVICE MASTER KEY TO FILE = '<pathname>\<filename>'

ENCRYPTION BY 'password'

Note that the password specified in the T-SQL commands are subject to password complexity checks.

The complexity checks include the following:

Must be eight characters long with a maximum of 128 characters
Does not contain all or part of the user account name
Contains characters from three of the following:

Uppercase Latin letter (A thru Z)

Lower case Latin letters (a thru z)

Numeric digits 0 thru 9

Non-alphanumeric characters: ! (exclamation point), $ (dollar sign), # number sign, % (percent sign).

How Data Is Encrypted

Using the DEK and encryption algorithm, TDE encrypts the database files at the database page level. Before the data page is written to disk, each page is encrypted and decrypted when the page is read into memory. The page is encrypted and decrypted using the DEK.

When TDE is enabled for a database, the is_crypted column in the sys.databases view is changed to 1, indicating the database is set for encryption. The encryption scan process then starts and scans all the database files associated to the database and encrypts the database files using the specified encryption algorithm specified when creating the DEK. The encryption process takes a shared lock on the database and encrypts each page in the specified database.

During this process, detaching the database or file structure changes is denied. Data existing in the log file is not encrypted. Entries in the log file are encrypted entries after the encryption process is complete.

Once the database encryption is complete, all database encryption and decryption is performed in memory. Each page is decrypted when the page is read into memory and each page is encrypted before the data page is written to disk. The page is encrypted and decrypted using the DEK.

Understanding Transparent Data Encryption