SQL Server 2008 - How to Encrypt Your Data Using Transparent Data Encryption (TDE)

Views: 1.733
This post is the 1 part of 4 in the series. Data Protection
Reading Time: 9 minutes


With the advent of GDPR, business data security concerns are growing, and an area that was once overlooked is in evidence more than ever now. As a result, IT professionals, especially DBAs, have been looking for ways to reduce the risk of data exposure and one way to do this is to encrypt data to prevent unauthorized access by third parties.

My idea in this article is to demonstrate a SQL Server solution that lets you encrypt data, which is the Transparent Data Encryption (TDE), available since SQL Server version 2008 in Enterprise and Developer editions.

Be sure to check out my post SQL Server 2016 - How to Encrypt Your Data Using Always Encrypted, another SQL Server data encryption solution, available since the 2016 release in Express, Standard, Enterprise, and Developer editions.

For the examples below, I will use the following script for base generation:

Transparent Data Encryption (TDE)

Transparent Data Encryption (TDE) encrypts SQL Server data files, which is known as at rest data encryption. In a scenario where physical media (such as backup drives or tapes) is stolen, a malicious third party can restore or attach the database and browse the data. One solution to this is to encrypt sensitive data in the database and protect the keys used to encrypt the data with a certificate. This prevents anyone without the keys from using the data, but this type of protection must be planned in advance.

TDE performs real-time I / O encryption and decryption of data and log files and protects data on disk, ie data and log files, providing the ability to comply with many laws, regulations and guidelines established in various sectors. This allows software developers to encrypt data using AES and 3DES encryption algorithms without changing existing applications.

With essentially a touch of magic, all the contents of MDF files, LDF files, snapshots, tempdb and backups are encrypted. Encryption occurs in real time, as data is written from memory to disk, and decryption occurs when data is read from disk and moved to memory. Encryption is done at the database level, so you can choose to encrypt as many databases as you want.

TDE is able to minimize resource utilization and hide its activities from user applications and the Relational Engine because all encryption / decryption occurs when data pages are moved between the buffer pool and disk. As SQL Server moves buffer pool data pages to either the MDF file, the LDF file, or tempdb, the data is encrypted in real time before being written to disk. In contrast, as data pages are moved from the MDF or tempdb file to the buffer pool, they are decrypted. In other words, when data is on disk, it is encrypted, but when it is in memory, it is not encrypted.

Regarding performance, the penalty for using Transparent Data Encryption, according to Microsoft, is between 3 to 5% performance drop only. A much lower value when compared to the performance penalty when using Always Encrypted, for example.

Encryption uses a database encryption key (DEK), which is stored in the database boot record for availability during recovery. The DEK is a symmetric key protected by a certificate stored in the server master database or an asymmetric key protected by an EKM module. TDE supports several different encryption options, such as AES with 128 bits, 192 bits or 256 bits or 3 Key Triple DES keys. You make your choice when implementing TDE.

Always Encrypted vs Transparent Data Encryption (TDE)

Below, I will list some similarities and differences between these two SQL Server encryption solutions:

Always EncryptedTransparent Data Encryption (TDE)
Column levelDatabase Level
Client Encryption (using a driver)Server Encryption (Database Engine)
Server does not know encryption keysServer knows encryption keys
Data in memory is encryptedData in memory is unprotected (plain-text)
Data on the network is encryptedData on the network is unprotected (plain-text)
Only users with key access can view the original data. Not even the DBA can view the original data without the key.DBA can view original data without key
Backups and log files are encryptedBackups and log files are encrypted
Requires application changes (may be small or large depending on the encryption algorithm you choose)No app changes required
Available from SQL Server 2016 - All editions, up to Express (Express and Standard from 2016 SP1)Available from SQL Server 2008 - Enterprise and Developer Only

How to use Transparent Data Encryption (TDE)

To implement Transparent Data Encryption (TDE), we must follow the 4 steps below:

  • Create a master key: A master key is created for the first time. This key, accessible with a password, is used to secure a certificate that will be created in the next step. This key is stored in the master database in an encrypted format.
  • Create or obtain a master key protected certificate: This certificate is used to protect the database encryption key we will create in the next step. In addition, this certificate is protected by the master key we created in the previous step. The certificate is stored in the master database in an encrypted format.
  • Create a database encryption key: This is the key that will be used by SQL Server to actually encrypt the data. It is protected by the certificate created in the previous step. This key is stored in the encrypted database and stored in an encrypted format.
  • Enable TDE: After all of the above have been created, a command will be executed to tell SQL Server to begin encrypting all data using the database encryption key created in the previous step. This process may take some time, depending on the size of the database. Ideally, the database should not be used in production until the database has completed the initial encryption process.

The image below demonstrates well this TDE architecture:

To enable Transparent Data Encryption (TDE) on a database, you must use the following commands: (remember that tempdb is encrypted automatically when encryption is enabled on any other instance database)

After executing these commands, your bank will be encrypted. To check which banks are encrypted or to track the progress of the encryption process, use the query below:


Recalling that the percent_complete column indicates the progress of the database encryption process and the encryption_state column indicates what state the encryption is currently in, whose values ​​in this column are:
0 = No database encryption key present, no encryption
1 = Unencrypted
2 = Encryption in progress
3 = Encrypted
4 = Key Change in Progress
5 = Decryption in Progress
6 = Protection change in progress (the certificate or asymmetric key that is encrypting the database encryption key is changing)

After you complete encrypting your database, you will see a warning message informing you that you have to back up your certificate and private key IMMEDIATELY. If you lose your certificate or private key and need to restore this bank to another server, you will not be able to.

Warning: The certificate used for encrypting the database encryption key has not been backed up. You should immediately back up the certificate and the private key associated with the certificate. If the certificate ever becomes unavailable or if you must restore or attach the database on another server, you must have backups of both the certificate and the private key or you will not be able to open the database.

Command to back up certificate and private key:

Restoring a Bank with Transparent Data Encryption (TDE)

If you try to restore an encrypted backup file without restoring the master key first, you will encounter this error message:

Msg 33111, Level 16, State 3, Line 12
Cannot find server certificate with thumbprint '0xD98C862BF2A4B16D41DC8A96CBE819EFDCF33C00'.
Msg 3013, Level 16, State 1, Line 12
RESTORE DATABASE is terminating abnormally.

Now I will show how to restore the database with TDE in another instance:

Caso você esteja se deparando com a mensagem de erro abaixo, você provavelmente vai precisar corrigir as permissões da master key e chave privada para habilitar a herança:

The certificate, asymmetric key, or private key file is not valid or does not exist; or you do not have permissions for it.

Para corrigir a herança desses 2 arquivos, siga os passos abaixo:

Transparent Data Encryption e logs de transação

A habilitação de um banco de dados para usar TDE tem o efeito de zerar a parte remanescente do log de transações virtuais para impor o próximo log de transações virtuais. Isso garante que nenhum texto não criptografado seja deixado nos logs de transações depois que o banco de dados for definido para criptografia.

Todos os dados gravados no log de transações antes de uma alteração na chave de criptografia do banco de dados serão criptografados usando a chave de criptografia do banco de dados anterior.

Depois que uma chave de criptografia de banco de dados foi modificada duas vezes, um backup de log deve ser executado para que a chave de criptografia de banco de dados possa ser modificada novamente.

Transparent Data Encryption (TDE) e o In-Memory OLTP

O TDE pode ser habilitado em um banco de dados que tenha objetos OLTP In-Memory. No SQL Server 2016 e nos registros de log OLTP In-Memory, os dados são criptografados se o TDE estiver habilitado. No SQL Server 2014 os registros de log OLTP In-Memory são criptografados se o TDE estiver habilitado, mas os arquivos no filegroup MEMORY_OPTIMIZED_DATA não são criptografados.

Limitações do Transparent Data Encryption (TDE)

  • O TDE não protege os dados na memória, portanto, os dados confidenciais podem ser vistos por qualquer pessoa que tenha direitos de DBO em um banco de dados ou direitos de SA para a instância do SQL Server. Em outras palavras, o TDE não pode impedir que os DBAs visualizem os dados que desejam ver.
  • TDE não é granular. O banco de dados inteiro é criptografado.
  • TDE não protege as comunicações entre aplicativos clientes e o SQL Server, portanto, outros métodos de criptografia devem ser usados ​​para proteger os dados que trafegam pela rede e podem ser interceptados por usuários mal-intencionados.
  • No TDE, todos os arquivos e os filegroups do banco de dados são criptografados. Se algum filegroup do banco de dados estiver marcado como READ ONLY, haverá falha na operação de criptografia de banco de dados.
  • Dados FILESTREAM não são criptografados.
  • Se um banco de dados estiver sendo usado no database mirror ou log shipping, ambos os bancos de dados serão criptografados. As transações de logs serão criptografadas quando enviadas entre eles.
  • Quando qualquer banco de dados em uma instância do SQL Server tiver a TDE ativada, o banco de dados tempdb será automaticamente criptografado, o que pode contribuir para um desempenho ruim dos bancos de dados criptografados e não criptografados em execução na mesma instância.
  • Embora menos recursos sejam necessários para implementar a TDE do que a criptografia no nível da coluna, ainda haverá um pouco de overhead, o que pode impedir que ela seja usada em SQL Servers que estejam enfrentando problemas de bottlenecks da CPU.
  • Os bancos de dados criptografados com o TDE não podem aproveitar a nova compactação de backup do SQL Server 2008. Se você quiser aproveitar a compactação e a criptografia de backup, precisará usar um aplicativo de terceiros, como o SQL Backup, que permite executar essas duas tarefas sem penalidade.

Dynamic Data Masking

Após demonstrar o uso dessa solução de criptografia, você estar se perguntando: “E o Dynamic Data Masking (DDM)???”. Bom, para começar, o DDM não é uma solução de criptografia de dados e sim uma solução de mascaramento de dados.

Enquanto a criptografia de dados efetivamente impede que os seus dados sejam restaurados (inclusive, a partir de backups) e acessados indevidamente, o mascaramento de dados com o Dynamic Data Masking apenas restringe o conteúdo que é mostrado ao final de um comando de SELECT, ou seja, caso você tenha acesso a um arquivo de backup, você pode restaurá-lo em uma instância onde você seja sysadmin e terá acesso livre aos dados antes mascarados.

Para saber mais sobre o Dynamic Data Masking, veja o artigo SQL Server 2016 – Mascaramento de dados com o Dynamic Data Masking (DDM), onde demonstro como utilizá-lo, restrições e até mesmo como “quebrar” o mascaramento e acessar os dados originais (mesmo sem ter permissão para isso).

Bom pessoal, espero que tenham gostado desse artigo e agora comecem a proteger melhor seus dados!
Grande abraço e até a próxima!