You are the database administrator of your company. The network contains an instance of SQL Server 2008 named SQL1.
SQL1 contains a database named CorpData that contains confidential information.
You want to encrypt the CorpData database. Which Transact-SQL should you run first?

A.
ALTER DATABASE CorpData SET ENCRYPTION ON;

B.
USE CorpData; GO
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE SrvCertificate;

C.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘p@ssw0rd’;

D.
CREATE CERTIFICATE SrvCertificate WITH SUBJECT = ‘Server Certificate’;

Explanation:

You should run the following Transact-SQL first:
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘p@ssw0rd’;
In SQL Server 2008, Transparent Data Encryption (TDE) is used to encrypt the contents of an entire database.TDE uses a database encryption key to encrypt the data. To encrypt a database by using TDE, the followingsteps must be performed:1. Create a master key. To do this, you should run the following statement:
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘p@ssw0rd’;
2. Create a server level certificate by using the following Transact-SQL statement:
CREATE CERTIFICATE SrvCertificate WITH SUBJECT = ‘Server Certificate’;
3. Create a database encryption key by using the server certificate. You can use the following Transact-SQLstatement to do this:
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE SrvCertificate;
4. Alter the database and set encryption on. You can use the following statement to do this:
ALTER DATABASE CorpData
SET ENCRYPTION ON;
When you encrypt a database file, the encryption is performed at the page level. SQL Server also allowsencryption at the cell level. Cell-level encryption provides a more granular level of encryption than database-levelencryption. With cell-level encryption, data is not decrypted until it is used, even if a page is loaded into memory.This prevents sensitive data from being displayed in clear text.

Objective:
Managing SQL Server Security

Sub-Objective:
Manage transparent data encryption.

References:
MSDN > MSDN Library > Servers and Enterprise Development > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Security and Protection > SecureOperation > SQL Server Encryption > Understanding Transparent Data Encryption (TDE) MSDN > MSDN Library > Servers and Enterprise Development > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Security and Protection > SecureOperation > SQL Server Encryption > SQL Server and Database Encryption Keys > SQL Server and DatabaseEncryption Keys How-to Topics > How to: Enable TDE using EKM