tailieunhanh - The Real MTCS SQL Server 2008 Exam 70/432 Prep Kit- P39

The Real MTCS SQL Server 2008 Exam 70/432 Prep Kit- P39: Congratulations on your journey to become certified in SQL Server 2008. This book will help prepare you for your exam and give you a practical view of working with SQL Server 2008. | 172 Chapter 5 Managing Data Encryption Requirements The SQL Server 2008 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 SQL Server 2008 cryptography 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 SQL Server 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 SQL Server service account. The SMK is then encrypted using local machine credentials. The SMK can only be decrypted by the service account used during the SQL Server setup processing at installation or by an account that has access to the SQL Server 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 SQL Server instance. Table describes the key hierarchy in TDE to enable the encryption of a user database. Managing Data Encryption Chapter 5 173 Table The Hierarchy in TDE Step SQL Server Key Dec SQL Command 1 Service master key SMK 2 Master database s database master key DMK and certificate Created by SQL Server 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 .