Estou tentando configurar a criptografia de backup no SQL 2017 e encontrei alguns problemas.
Não tenho problemas em criar a chave mestra e o certificado em uma máquina. Também não tenho problemas para instalar o certificado em outra máquina e ler o backup, no entanto, tenho um problema em que isso não funciona se a outra máquina estiver executando uma conta de serviço diferente daquela em que o certificado é criado.
Aqui estão as etapas que estou tomando (também tentei restaurar a chave mestra, mas isso também gera um erro):
/* Server 1 */
/* Create the master key */
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'SomeRandomSecureString';
GO
/* Create the certificate to be used for backups */
CREATE CERTIFICATE BackupCert
WITH SUBJECT = 'Backup Encryption Certificate';
GO
/* Backup the master key */
BACKUP MASTER KEY TO FILE = '\\FileShare\DatabaseMasterKey_Master.key'
ENCRYPTION BY PASSWORD = 'SomeRandomPwd';
BACKUP CERTIFICATE BackupCert TO FILE = '\\FileShare\BackupCert.cer'
WITH PRIVATE KEY (FILE = '\\FileShareBackupCert.pvk',
ENCRYPTION BY PASSWORD = 'RandomEncryptionPwd');
GO
/* Server 2 */
/* Create master key */
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'SomeRandomSecureString';
GO
/* Restore the cert */
CREATE CERTIFICATE BackupCert FROM FILE = '\\FileShare\BackupCert.cer'
WITH PRIVATE KEY (FILE = '\\FileShare\BackupCert.pvk',
DECRYPTION BY PASSWORD = 'RandomEncryptionPwd');
--Msg 15208, Level 16, State 6, Line 32
--The certificate, asymmetric key, or private key file is not valid or does not exist; or you do not have permissions for it.
/* Try restoring the master key instead */
DROP MASTER KEY;
RESTORE MASTER KEY FROM FILE = '\\FileShare\DatabaseMasterKey_Master.key'
DECRYPTION BY PASSWORD = 'RandomEncryptionPwd'
ENCRYPTION BY PASSWORD = 'RandomEncryptionPwd';
--Msg 15317, Level 16, State 2, Line 39
--The master key file does not exist or has invalid format.
Referências que eu olhei para tentar descobrir o que está acontecendo aqui:
- https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/backup-encryption?view=sql-server-2017
- https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/create-a-database-master-key?view=sql-server-2017
- https://learn.microsoft.com/en-us/sql/t-sql/statements/create-certificate-transact-sql?view=sql-server-2017
- https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/create-an-encrypted-backup?view=sql-server-2017
Também garanti que o SMK seja o mesmo entre todas as máquinas, mas ainda não tive sorte em restaurar o certificado.
Tenho certeza de que estou fazendo algo errado aqui, mas não consegui descobrir o quê.
Todas as idéias seriam apreciadas. Obrigado.
Você precisa corrigir as permissões NTFS no certificado. Por padrão, o SQL, ao criar o backup, definirá as permissões NTFS para que apenas a conta que executa o SQL Service possa ler o arquivo de backup do certificado.
É por isso que funciona com uma única conta e não funciona com duas contas.