Recentemente tivemos uma demanda por conta de LGPD, onde tivemos que habilitar criptografia na camada do Database em uma instancia MSSQL, utilizaremos de início TDE (Transparent database Encryption) como o nome já diz, será transparente para a nossa aplicação.

È possível ver que a base está com Encryption Enabled = False.

Propriedades do database no SSMS mostrando Encryption Enabled = False antes do TDE

Primeiro iremos criar a Master Key, esta chave terá um password, é muito importante salvá-lo.

Script no SSMS com USE Master e CREATE MASTER KEY ENCRYPTION BY PASSWORD, senha tarjada

E então criaremos o certificado que a Master Key irá utilizar.

Comando CREATE CERTIFICATE WITH SUBJECT no SSMS criando o certificado do TDE

Validando a criação do certificado através da query.

SELECT name, pvt_key_encryption_type_desc, thumbprint FROM sys.certificates;
Resultado do SELECT em sys.certificates no SSMS listando o certificado do TDE com ENCRYPTED_BY_MASTER_KEY

Agora deveremos conectar no database que iremos criptografar e criar a encryption key dentro deste database utilizando o certificado que criamos em nossa master.

Estamos utilizando o Algoritmo AES_256

USE <DATABASE>;

CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256

ENCRYPTION BY SERVER CERTIFICATE TDE_CERT_GM;

CREATE DATABASE ENCRYPTION KEY com AES_256 no SSMS e o aviso para fazer backup do certificado

Podemos ver que recebemos um Aviso ao executar o comando, e está dizendo que devemos fazer um backup imediatamente deste certificado e sua chave privada, senão em caso de algum restore, não poderemos abrir mais este database sem estes itens!

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.

Vamos agora vamos habilitar a criptografia em nosso database.

ALTER DATABASE SET ENCRYPTION ON no SSMS com a mensagem Commands completed successfully

Esta etapa pode demorar um pouco, dependendo do tamanho do seu database, e pode ser avaliado o progresso através da query abaixo.

DECLARE @state tinyint;<br>DECLARE @encyrption_progress<br>TABLE(sample_time DATETIME, percent_complete DECIMAL(5,2))
SELECT @state = k.encryption_state<br>FROM sys.dm_database_encryption_keys k<br>INNER JOIN sys.databases d<br>ON k.database_id = d.database_id<br>WHERE d.name = ‘TestTDE’;
WHILE @state != 3<br>BEGIN<br>INSERT INTO @encyrption_progress(sample_time, percent_complete)<br>SELECT GETDATE(), percent_complete<br>FROM sys.dm_database_encryption_keys k<br>INNER JOIN sys.databases d<br>ON k.database_id = d.database_id<br>WHERE d.name = ‘TestTDE’;
WAITFOR delay ’00:00:01′;SELECT @state = k.encryption_state<br>FROM sys.dm_database_encryption_keys k<br>INNER JOIN sys.databases d<br>ON k.database_id = d.database_id<br>WHERE d.name = ‘TestTDE’;<br>END
SELECT * FROM @encyrption_progress;

E então nosso database está criptografado!

Propriedades do database no SSMS mostrando Encryption Enabled = True após habilitar o TDE

Mas antes de finalizarmos, lembra do nosso Warning?

Precisamos Fazer um backup de nosso certificado e master Key! Não deixe isso para depois, pode ser um problemão!

Todos estes itens estão vinculados ao database Master, então devemos dar um use neste database, você deverá utilizar a mesma senha que fez para criação da Master Key.

use master;

Servico MK:
BACKUP SERVICE MASTER KEY
TO FILE = ‘G:\TDE\svcMASTERKEY.bak’
ENCRYPTION BY PASSWORD = ‘XPTO’;

MK:
BACKUP MASTER KEY
TO FILE = ‘G:\TDE\MASTERKEY.key’
ENCRYPTION BY PASSWORD = ‘XPTO’;

CERTIFICATE:
BACKUP CERTIFICATE TDE_CERT_GM
TO FILE = ‘G:\TDE\TDE_CERT_GM.cert’
WITH PRIVATE KEY(
FILE = ‘G:\ TDE\TDE_CERT_GM.prvk’,
ENCRYPTION BY PASSWORD = ‘XPTO’
);

Script no SSMS com BACKUP SERVICE MASTER KEY, BACKUP MASTER KEY e BACKUP CERTIFICATE, caminhos e senhas tarjados

E então finalizamos nossa implementação de TDE , para outros databases , você deverá apenas repetir o procedimento depois da criação do certificado.

Lembre-se de armazenar esses backups sempre em lugares seguro e suas respectivas senhas.

Até a Próxima!

Criado por: Matsuo Furushima