Understanding SQL Server 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.)