Transparent Data Encryption and its tricks

Transparent Data Encryption – TDE não é mais uma novidade, mas sim uma realidade que devemos usar. Um ponto importante que vale ressaltar é que esse recurso está presente apenas na edição ENTERPRISE do SQL Server.
Existem alguns passos a passos na internet de como implementar, mas eu vou deixar um script que pode ser seguido.

Master Key e Certificados

Uma forma simples de implementar o TDE é utilizar um certificado gerado pelo próprio SQL Server que é protegido pela Master Service Key.
Se você está implementando ou pretende implementar lembre-se que o mais importante de tudo é fazer backup do certificado e das keys!
PARE TUDO e faça BACKUP 
O principal aspecto para utilizar o TDE está relacionado à protecao de chaves incluindo backups separados das chaves de criptografia e a capacitade de restaurar e recuperar esse banco de dados em outro local. Assim como qualquer outro plano de DR, a restauração de bancos de dados de TDE é algo que devemos praticar antes que ocorra o desastre. Você não quer descobrir depois de um evento  que você não estava fazendo o backup da chave TDE corretamente.
Mais uma vez eu utilizei o dbatools para me ajudar a automatizar minha tarefa e quero agradecer ao Claudio Silva  (blog/twitter) por me ajudar. Algumas linhas de powershell são suficientes para isso 🙂
Agora que o background está pronto, o TDE pode ser habilitado.
A diferença quando se está utilizando AlwaysON Availability Group está na preparação dos ambientes antes de habilitar o TDE. Ou seja, você deve criar chaves e certificados em todas réplicas que pertencem ao AG.

/*
  Following steps must be executed on PRIMARY REPLICA
*/
-- Check if MASTER KEY is created
USE master; 
GO
SELECT * FROM sys.symmetric_keys; 
SELECT * FROM sys.certificates;
/*
  Step 1: On Primary Replica - Creation of the Database MASTER KEY (DMK)
  We can skype this step because we already have it
  Make sure that MASTER KEY Backup is working correctly.
        Review backup routine! This is most important step to do
*/
-- Create a Master Key IF NOT EXISTS
USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'use_a_strong_password';
GO
-- Backup the Master Key
BACKUP MASTER KEY
TO FILE = '\\PrimaryReplica\TDE\PrimaryReplica_MasterKeyBackup.key'
ENCRYPTION BY PASSWORD = 'use_a_strong_password';
GO
/*
  Step 2: On Primary Replica - Creation of the CERTIFICATE
  Certificate is named as TDECert and it will use MASTER KEY to en
*/
-- Create Certificate Protected by Master Key
CREATE CERTIFICATE TDECert
WITH
  SUBJECT = 'Transparent Data Encryption Cerficate';
GO
/*
 Backup the Certificate
 Review backup routine. We should back up it to two/three different location
 Pick up a strong password
*/
BACKUP CERTIFICATE Cert_For_TDE TO FILE = '\\PrimaryReplica\TDE\PrimaryReplica_Cert_For_TDE_Backup.cer'
WITH PRIVATE KEY ( FILE = '\\PrimaryReplica\TDE\PrimaryReplica_Cert_For_TDE_PrivKey.pvk'
                  ,ENCRYPTION BY PASSWORD = 'use_a_strong_password' );
/*
  Step 3: On Primary Replica - Creation of Database Encryption Key (DEK)
*/
USE YourDatabase;
GO
/*
 Create a Database Encryption Key
 Choose between AES_128 and AES_256
 Your are not going to encrytp it yet!
*/
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128 -- AES_256
ENCRYPTION BY SERVER CERTIFICATE Cert_For_TDE;
/*
  Following stes must be executed on SECONDARY REPLICA
*/
-- Check if MASTER KEY is created
USE master;
GO
SELECT * FROM sys.symmetric_keys;
SELECT * FROM sys.certificates;
-- Create a Master Key IF NOT EXISTS
USE master;
GO
/*
  Step 4: On Secondary Replica - Creation of the DMK
  Make sure that MASTER KEY Backup is working correctly.
  Review backup routine.
*/
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'use_a_strong_password';
-- Backup the Master Key on secondary in order we have this when it turns into Primary and we need not worry when recovery event occurs.
-- Review backup routine! This is most important step to do
BACKUP MASTER KEY
TO FILE = '\\SecondaryReplica\TDE\SecondaryReplica_MasterKeyBackup.key' -- You can use same folder to store keys (I just change it to make things easy)
ENCRYPTION BY PASSWORD = 'use_a_strong_password';
/*
  Step 5: On Secondary Replica - Creation of the Certificate from the Primary Certificate Backup
*/
-- Create Certificate Protected by Master Key
CREATE CERTIFICATE Cert_For_TDE
FROM FILE = '\\PrimaryReplica\TDE\PrimaryReplica_Cert_For_TDE_Backup.cer'
WITH PRIVATE KEY ( FILE = '\\PrimaryReplica\TDE\PrimaryReplica_Cert_For_TDE_PrivKey.pvk'
                  ,DECRYPTION BY PASSWORD = 'same_strong_password_for_backup' );
-- Backup the Certificate
-- Review backup routine! This is most important step to do
BACKUP CERTIFICATE Cert_For_TDE TO FILE = '\\SecondaryReplica\TDE\SecondaryReplica_Cert_For_TDE_Backup.cer'
WITH PRIVATE KEY ( FILE = '\\SecondaryReplica\TDE\SecondaryReplica_Cert_For_TDE_PrivKey.pvk'
                  ,ENCRYPTION BY PASSWORD = 'use_a_strong_password' );
/*
  Step 6: On Primary Replica – Enabling TDE Encryption
  Following stes must be executed on PRIMARY REPLICA
*/
-- Now you are going to encrypt it
ALTER DATABASE YourDatabase SET ENCRYPTION ON;
GO
/*
  Step 7: On the Primary Replica and Secondary Replicas – Monitoring Encryption
  This code must be run in SQLCMD mode
*/
:CONNECT PrimaryReplica
SELECT
  @@SERVERNAME
 ,db.name
 ,db.is_encrypted
 ,dm.encryption_state
 ,dm.percent_complete
 ,dm.key_algorithm
 ,dm.key_length
FROM master.sys.databases                                db
  LEFT OUTER JOIN master.sys.dm_database_encryption_keys dm
    ON db.database_id = dm.database_id;
GO
:CONNECT SecondaryReplica
SELECT
  @@SERVERNAME
 ,db.name
 ,db.is_encrypted
 ,dm.encryption_state
 ,dm.percent_complete
 ,dm.key_algorithm
 ,dm.key_length
FROM master.sys.databases                                db
  LEFT OUTER JOIN master.sys.dm_database_encryption_keys dm
    ON db.database_id = dm.database_id;
GO

Uma vez que você efetive o recurso ele será executado silenciosamente em segundo plano, criptografando o banco de dados enquanto as atividades continuam. Enquanto a criptografia inicial está em execução, podemos verificar a coluna percent_complete na sys.dm_database_encryption_keys para ver progresso (veja o step 7).
http://www.sqlservercentral.com/articles/always+on/135432/
Esse processo seria simples, se não fosse pelo grande volume de transações que eu tenho. Um sistema de telemetria que a todo instante está recebendo e enviando informações de veículos no mundo.
O CPU foi a quase 100% e gerou um performance ruim para o sistema. O que quero dizer é que processos que executavam em 500 milissegundos passaram a executar em 1200/1500 milissegundos.
Se você procurar na documentação só exista a opção de ON e OFF para ENCRYPTION. Uma vez que eu iniciei o processo para criptografar o database eu tenho que esperar terminar. E agora? Quanto tempo isso vai demorar para finalizar no meu database?
Existe uma trace flag documentada (Thanks God Microsft) que pausa o processo e simplesmente não faz rollback! Fantástico pois não perco o que foi criptografado e meu database fica com o status de “encryption in progress”. Ou seja, as páginas que foram criptografadas continuam assim e o as páginas que não foram criptografadas serão lidas normalmente.
https://blogs.msdn.microsoft.com/markweberblog/2017/04/04/transparent-data-encryption-tde-traceflag-5004-and-interrupting-encryption-scanning/
Como qualquer outra TF basta você ativar ou desativar. Para parar o processo ative a TF:

DBCC TRACEON(5004) 
GO

Para reiniciar o processo de onde parou basta desativar a TF e executar o comando novamente para criptografar o database.

DBCC TRACEOFF(5004) 
GO 
ALTER DATABASE YourDatabase SET ENCRYPTION ON;
GO

Isso salvou minha vida, pois eu pude habilitar o processo em horários de menor utilização e mesmo assim se impactasse o sistema eu seria capaz de parar.

Backup Compression

Um fator que notamos depois de habilitar o TDE foi a compressão de backups não estava funcionando corretamente, ou seja, simplesmente ignorava a opção e gerava um backup gigantesco.
Pesquisando um pouco achei uma excelente explicação para esse comportamento.
https://blogs.msdn.microsoft.com/sql_server_team/backup-compression-for-tde-enabled-databases-important-fixes-in-sql-2016-sp1-cu4-and-sql-2016-rtm-cu7/
Depois de entender melhor esse comportamento, foi a hora de verificar o que estava ocorrendo. Para isso utilizei um extended event (backup_restore_progress_trace) que facilita e muito a interpretar o que está acontencendo no momento do backup.
O Edvaldo Castro (blog/twitter) explica muito bem esse novo XE.
https://edvaldocastro.com/benchmarking-teste-do-novo-extended-event/
Você deve querer ver uma mensagem de MaxTransferSize maior de 64 KB:

https://blogs.msdn.microsoft.com/sql_server_team/new-extended-event-to-track-backup-and-restore-progress/#.VgtLzHpVhBd
Nesse momento fizemos duas ações:
1 – Atualizamos para o service pack e cumulative update mais recente visto que estávamos com a versão inferior descrita no link (https://blogs.msdn.microsoft.com/sql_server_team/backup-compression-for-tde-enabled-databases-important-fixes-in-sql-2016-sp1-cu4-and-sql-2016-rtm-cu7/)
2 – Mudamos nossa rotina de backup para se adequar as necessidades.

E o tempdb?

A partir do primeiro database utilizando TDE o tempdb passa ser criptografado automaticamente. Um curioso caso sobre o tempdb foi desvendado pelo Bob Ward, recomendo a leitura!
https://blogs.msdn.microsoft.com/bobsql/2017/01/26/sql-server-mysteries-the-case-of-tde-and-permanent-tempdb-encryption/

Eu tenho FileStream, vai criptografar?

A documentação é clara, dados em FileStream não são criptografados. Nossa solução foi utilizar BitLocker
https://docs.microsoft.com/en-us/windows/security/information-protection/bitlocker/bitlocker-how-to-deploy-on-windows-server

Como ficou a CPU?

Nossos testes mostraram um leve aumento de CPU mas nada que comprometeu  a performance. Monitoramos alguns processos críticos e vimos que o aumento foi em média de 3-5% de CPU e tempo total de duração aumentou 100-150 milissegundos.

Transaction-Log? Hummmm

Eu não vi nenhum tipo de problema com isso. Espero que continue assim 🙂
Alguns recursos que não utilizamos e ainda não testei como Buffer Pool Extention – a documentação diz que não é criptogrado e assim como FileStream uma opção seria usar BitLocker. Quero verificar o quanto isso pode afetar a área de memória ou não.

Certificado expirado

O processo de substituir com segurança o certificado é chamado rotating the encryption key. É importante fazer isso, e o SQL Server torna isso um processo simples e rápido. A substituição dos seus certificados expirados é um processo rápido e simples, e é uma parte importante da manutenção da segurança da sua criptografia
Você também pode usar ou restaurar um certificado expirado no servidor.
https://blogs.msdn.microsoft.com/sqlsecurity/2010/06/14/database-encryption-key-dek-management/

Rotação de chaves

Rotation the encryption key é o processo de passar do certificado antigo para o novo. Nesse caso, tudo o que acontece é que a chave de criptografia do banco de dados é descriptografado com o certificado antigo e criptografado novamente com o novo certificado. Esse novo valor criptografado é armazenado no banco de dados sobrescrevendo o antigo. Por ser um processo rápido, a rotação frequente não é um problema.

Novo certificado

O primeiro passo é criar um novo certificado que será usado para criptografar o banco de dados. Você pode seguir os mesmos passos acima.

Rotação do certificado

Para substituir o certificado usado para TDE, adicione o novo certificado como acima, então execute o comando

USE YourDatabase
GO
ALTER DATABASE ENCRYPTION KEY
ENCRYPTION BY SERVER CERTIFICATE TDECert_Rotate_Key;
GO

Faça backup do novo certificado e do database.

Remover TDE

Para remover o TDE é necessário executar o comando abaixo.

ALTER DATABASE yourdatabase SET ENCRYPTION OFF;
GO

Aguarde até que o processo de descriptografia esteja concluído. Use o sys.dm_database_encryption_keys DMV para determinar seu status

USE YourDatabase
GO
DROP DATABASE ENCRYPTION KEY
GO

Faça backup do novo certificado e do database.
Para finalizar, aprenda que o TDE não é apenas um recurso que liga e desliga para entrar em conformidade com órgãos regulamentadores. Você deve se preparar e entender melhor esse recurso antes de utiliza-lo em seu ambiente de producao. Este foi um dos recursos que eu mais gostei de implementar pois me aprofundei em um assunto que eu mesmo tinha deixado de lado.
Fica uma pergunta! Quando falamos de data-in-rest o TDE é suficiente para deixar meus dados seguros ou precisamos fazer um backup com criptografia? Em um próximo post falarei como utilizar backup encryption
https://blogs.msdn.microsoft.com/sqlsecurity/2010/06/14/database-encryption-key-dek-management/
https://blogs.msdn.microsoft.com/sqlsecurity/2016/10/05/feature-spotlight-transparent-data-encryption-tde/

Tiago Balabuch

I'm enthusiast, fascinated, passionate, fanatic by SQL Server and in the off-hours Cloud Data Administrator who loves what does