Transparent Data Encryption (TDE) in SQL Server

Introduction

Encryption brings data into a state that cannot be interpreted by anyone who does not have access to the decryption key, password, or certificates. Though encryption does not restrict the access to the data, it ensures that if data loss happens, then in that case data is useless to the person who does not have access to the decryption key/password/certificates. Though when you use encryption, there should be a maintenance strategy for passwords, keys, and certificates.

To meet the demands of regulatory compliance and corporate data security standards, SQL Server allows you to enable encryption at column/cell level or on the database level. You can even use file level encryption provided by Windows for database files.

Though there are several ways/levels to implement encryption in SQL Server, I am going to focus on Transparent Data Encryption (TDE) in this article, which was introduced in SQL Server 2008 and available in later releases.

Transparent Data Encryption (TDE) is a feature introduced in SQL Server 2008 and available in later versions for bulk encryption at the database file level (data file, log file and backup file) i.e. the entire database at rest. Once enabled for a database, this feature encrypts data into pages before it is written to the disk and decrypts when read from the disk. The best part of this feature is, as its name implies, its completely transparent to your application. This means literally no application code changes (only administrative change to enable it for a database) are required and hence no impact on the application codefunctionalities when enabling TDE on a database being referenced by that application.

If there is a requirement to meet the demands of regulatory compliance and overall concern for data privacy, as a DBA you just need to enable it at the database level and rest of things are taken care by SQL Server to ensure data files, log files and even that specific database backup files are encrypted to prevent unauthorized access to the data in the TDE enabled database.

When you enable or disable TDE, the encryption and decryption operations are scheduled on background threads (called the encryption scan or scan - that scans all database files for encryption when enabling or decryption when disabling TDE) by SQL Server. You can view the status of these operations using the catalog views and dynamic management views as demonstrated next.

Please note, as I said before TDE works on data at rest for the entire database; in other words, it encrypts data when writing to disk and decrypts it when reading from disk at I/O level through the buffer pool. This means, data in the buffer pool remains there in clear text format. Hence, if you want to protect data in the buffer pool with encryption, you need to employ a different technique.

These are the steps you need to perform to enable TDE for a database, assuming you have the required permissions for creating a database master key and certificates in the master database and CONTROL permissions on the user database.

See the original post here:
Transparent Data Encryption (TDE) in SQL Server