Criando um backup criptografado no SQL Server
Olá pessoal tudo certo?
No post de hoje vou demonstrar como podemos aumentar a segurança dos nossos backups e impedir que os mesmos sejam restaurados em outra instância indevidamente.
Nos dias atuais, a informação é o bem mais precioso que as empresas possuem, e por esse motivo o roubo de informações pode gerar grandes prejuízos. Empresas concorrentes podem desejar as informações da sua empresa e podem apelar para práticas ilícitas. Uma delas seria roubar o backup dos bancos de dados da sua empresa.
Até o SQL Server 2012, podíamos criar um backup com PASSWORD, o que gerava uma pequena “segurança”, mas a partir do SQL Server 2014 está opção foi descontinuada. Entretanto, foi disponibilizado a opção de realizar o Backup Criptografado. Com isso, podemos garantir que uma base de dados somente pode ser restaurada em uma instância que tenha o certificado ou a chave assimétrica, ou seja, garantindo que o destino seja seguro. Essa opção do backup criptografado está disponível tanto para um Backup Local, como para o Backup no Azure.
Para realizar a criptografia dos backups, precisamos definir os algoritmos e se vamos escolher chave assimétrica ou certificado.
Algoritmos de Criptografia
Os Algoritmos de Criptografia compatíveis com o SQL Server 2016 são:
- AES 128;
- AES 192;
- AES 256;
Para usar algoritmos mais antigos (não recomendado) você deve definir o nível de compatibilidade do banco de dados para 120 ou inferior.
Passos para criar um Backup Criptografado com Certificado:
Criar a Chave de Criptografia: Escolha uma senha para criptografar a chave mestre que será armazenada no banco de dados, você pode criar uma Master Key por database. Mas para criptografia de backup você deve criar na database “Master”
- Criando a chave mestre:
No exemplo abaixo, a chave mestre ficou com a senha “tiagoneves.net/blog”.
Para mais informações sobre Master Key veja no link: https://docs.microsoft.com/pt-br/sql/t-sql/statements/create-master-key-transact-sql
USE Master; GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'tiagoneves.net/blog'; GO
Criar o Certificado que deverá ser importado na instância onde a base de dados poderá ser restaurada, lembrando que o certificado deverá ser armazenado em um local seguro, pois sem ele, não será possível restaurar a base de dados.
Use Master go CREATE CERTIFICATE BKP_SAFE WITH SUBJECT = 'Certificado para segurança dos backups', EXPIRY_DATE = '20191231'; GO
Parâmetros:
Nome do Certificado: defina um nome para o certificado;
Subject: Descrição do certificado. Auxilia na identificação do certificado na view “sys.certificates”;
Expiry_Date: Data de Expiração do certificado. Caso não seja especificado, o certificado terá validade de 1 ano após a data de início do certificado.
Se você criar o certificado e ele expirar você deverá criar um novo certificado, para não ter problema ao fazer os restore das suas bases de dados.
Após criar o certificado, devemos fazer o backup do mesmo para armazenar em um local seguro.
Use Master go BACKUP CERTIFICATE BKP_SAFE TO FILE = 'C:\MSSLQSERVER\Certificado\BKP_SAFE.cer'
Podemos exportar o certificado colocando ainda mais segurança exportando a chave privada, ou seja, para fazer a importação do certificado em outro servidor, você deverá ter a chave privada junto.
BACKUP CERTIFICATE BKP_SAFE TO FILE = 'C:\MSSLQSERVER\Certificado\BKP_SAFE.cer' WITH PRIVATE KEY (FILE = 'C:\MSSLQSERVER\Certificado\BKP_SAFE.key', ENCRYPTION BY PASSWORD = 'tiagoneves')
Parâmetros:
TO_File: Local onde será exportado o certificado.
Private_Key: Local onde será armazenada a chave privada do certificado.
ENCRYPTION BY PASSWORD: Senha utilizada para criptografar a chave privada do certificado.
Para realizar o restore de forma correta, devemos criar o certificado no servidor de destino. Com isso, devemos ter o certificado e, nesse caso, como criamos uma chave privada, também precisaremos dela para importar o certificado.
Agora podemos criar os nossos backups criptografados.
BACKUP DATABASE AdventureWorks TO DISK = 'C:\MSSLQSERVER\BKP\AdventureWorks_Full_Dados.bak' WITH COMPRESSION, ENCRYPTION ( ALGORITHM = AES_256, SERVER CERTIFICATE = BKP_SAFE ), STATS = 10, FORMAT GO
Parâmetros:
COMPRESSION: Habilita a compactação do backup;
ENCRYPTION: Habilita a criptografia do backup;
ALGORITHM: Devemos informar o algoritmo de criptografia que será utilizado;
SERVER CERTIFICATE: Devemos informar qual o certificado vamos utilizar para criptografar o backup;
STATS: Progresso do Backup (em percentual %);
FORMAT: Formata a mídia de backup.
Ao tentar realizar o restore do backup em outra instância sem utilizar o certificado, recebemos o seguinte erro:
Msg 33111, Level 16, State 3, Line 1
Cannot find server certificate with thumbprint ‘0x5E12ACAADF862A3236182AC0233F733CA3B0EE86’.
Msg 3013, Level 16, State 1, Line 1
RESTORE FILELIST is terminating abnormally.
Para realizar o restore de forma correta, devemos criar o certificado no servidor de destino. Com isso, devemos ter o certificado e, nesse caso, como criamos uma chave privada, também precisaremos dela para importar o certificado.
Criando o certificado:
No servidor de destino, devemos criar o certificado com o comando abaixo:
Primeiro devemos criar uma Master Key na instância de destino:
Use master go CREATE MASTER KEY ENCRYPTION BY PASSWORD = '%m#9908$u)gt)'
Após criar a Master Key devemos criar o certificado, a partir do certificado que fizemos o backup.
Use master Go CREATE CERTIFICATE BKP_SAFE FROM FILE = 'C:\Temp\BKP\Certificado\BKP_SAFE.cer' WITH PRIVATE KEY (FILE = 'C:\Temp\BKP\Certificado\BKP_SAFE.key', DECRYPTION BY PASSWORD = 'tiagoneves');
Parâmetros:
FROM_FILE: Local do certificado que será utilizado;
Private_Key: Local da chave privada do certificado;
DECRYPTION BY PASSWORD: Senha utilizada para descriptografar a chave privada do certificado
Para verificar as informações do certificado podemos utilizar a view “sys.certificates”. Segue abaixo o resultado:
SELECT * FROM sys.certificates WHERE name = 'BKP_SAFE'
Com o certificado importado, agora podemos fazer o restore da database sem problemas.
Bom pessoal por hoje é isso.
Abraços,
Tiago Neves
Oi Tiago, como eu faço pra restaurar um backup que foi feito com criptografia no SQL Server 2017, em outro servidor? Você tem algum tutorial assim? Obrigada