Transparent data encryption for SQL Server 2008

For a database administrator, security is one of the most important aspects to consider when protecting a database that you support. We use a variety of mechanisms and techniques to protect our data and databases, such as firewalls, authentication, and data encryption. But although we set security for our environment, there are always problems with database security. Although we are protecting our database, what if someone steals MDF files or backup files? But there is no workaround for using a third-party solution to control this scenario before SQL Server 2008. There is no local way to deal with this problem. SQL Server 2008 has introduced a new feature to protect the database, which is called transparent data encryption (transparent-encryption)--TDE, which provides protection for the entire database. The content of this article includes:

What is transparent data encryption?

The execution of the TDE.

Is my database secure now?

What do I need to consider before activating TDE?

What does it affect when TDE is activated?

What is transparent data encryption?

Microsoft SQL Server 2008 introduces another level of encryption-transparent data encryption. TDE is a full database-level encryption that is not limited to fields and records, but protects data files and log files. TDE execution on a database is very simple and transparent to the application that connects to the selected database. It does not need to make any changes to existing applications. This protection is applied to data and log files, as well as to backup files. Once TDE is activated on one database, it is not permissible for a backup to revert to another instance of SQL Server or additional data files to another instance of SQL Server unless the certificate used to protect the database encryption key (DEK) is available.

The TDE encryption feature is applied to the page level. Once activated, the pages are encrypted before they are written to disk and decrypted before they are read to memory. One thing to keep in mind is that communication channels between SQL Server and client applications are not protected and encrypted through TDE.

The following figure shows how SQL Server uses TDE to encrypt a database:

Transparent data encryption uses a Data encryption key (DEK) to encrypt the database, which is stored in the database startup record. DEK is protected by a certificate stored in the primary database. Optionally, Dek can be protected by an asymmetric key placed in the Hardware Security module (HSM) and by external Key Management (EKM) support. The private key of the certificate is encrypted by the database master key of the symmetric key, which is usually protected by a strong password. Note that although this certificate can be protected by a password, TDE requires that the certificate be protected by the database master key. The database master key is protected by the service master key, and the service master key is protected by the Data Protection API.