Início / dba / Perguntas / 94785
Accepted
Razvan Zoitanu
Asked: 2015-03-10 08:05:37 +0800 CST

Restaurar banco de dados com coluna criptografada no SQL Server, sem substituir a Chave Mestra de Serviço existente

  • 772

Cenário:

Server1 executando SQL Server 2012 com Service Master Key A, db1 com Database Master Key 1, chave simétrica e certificado disponível. A senha usada para proteger o DBMK 1 é desconhecida, a criptografia é habilitada pelo SMK A.

Server2 executando SQL Server 2012 com SMK B, db2 com DBMK 2, chave simétrica e certificado disponível. A senha usada para proteger DBMK 2 é desconhecida, SMK B usado para criptografia.

Uma restauração de db2 para Server1 é necessária. Há uma coluna criptografada em uma tabela que armazena senhas para um aplicativo ASP.NET interno. Existem outros aplicativos acessando dados criptografados em db1 no Server1 .

Pergunta:

Melhor opção para restaurar db2 para Server1 e acessar os dados criptografados sem causar problemas para db1?

Testado:

Uma restauração simples trará os dados db2, a chave simétrica e o certificado para o Servidor1, mas o SMK A não pode ser usado para descriptografar os dados: "Crie uma chave mestra no banco de dados ou abra a chave mestra na sessão antes de executar esta operação ." Restaurado SMK B em um servidor de teste e tudo estava bem. Não é possível usar esta solução no Server1, pois acredito que restaurar SMK B para Server1 (sobrescrever SMK A) desabilitará meu acesso aos dados criptografados em db1. Não quero que isso aconteça.

Algum conselho? Obrigada.

sql-server restore

2 respostas

  1. Best Answer

    Server1 executando SQL Server 2012 com Service Master Key A, db1 com Database Master Key 1, chave simétrica e certificado disponível.

    Presumo que a chave mestra db1 esteja criptografada com o SMK. Isso torna tudo criptografado pela chave mestra do banco de dados 'disponível' para aplicativos, sem ter que abrir explicitamente a chave mestra do banco de dados.

    O que você precisa fazer é restaurar o banco de dados, abrir a chave mestra do banco de dados usando a senha DBMK e adicionar a criptografia Server 2 SMK ao DBMK:

    RESTORE DATABESE ... FROM ...;
USE ...;
OPEN MASTER KEY DECRYPTION BY PASSWORD = ...;
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
    • 5

  2. Usei uma instância de teste vazia do SQL 2012, restaurei o SMK do Server2 e restaurei o db2 (minha chave simétrica admin_key e o certificado admin_cert foram incluídos por padrão no backup/restauração).

    BACKUP SERVICE MASTER KEY TO FILE = 'path\prod.SMK' ENCRYPTION BY PASSWORD = pass RESTORE SERVICE MASTER KEY FROM FILE = 'path\prod.SMK' DECRYPTION BY PASSWORD = pass

    Essa configuração agora permite acesso aos dados na coluna criptografada, assim como no sistema de produção. Execute a consulta no db2 nesta instância de teste para demonstrar:

    OPEN SYMMETRIC KEY admin_key DECRYPTION BY CERTIFICATE admin_cert SELECT ID, CONVERT(VARCHAR(1000),DecryptByKey(Password)) [decrypted_Password] FROM table_with_enc_column CLOSE SYMMETRIC KEY admin_key

    Comece com este script no db2 na instância de teste para remover a criptografia SMK e alternar para a criptografia de senha:

    OPEN SYMMETRIC KEY admin_key DECRYPTION BY CERTIFICATE admin_cert ALTER MASTER KEY ADD ENCRYPTION BY PASSWORD = pass ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY CLOSE SYMMETRIC KEY admin_key

    Agora faça backup desse banco de dados da instância de teste e restaure no servidor1 de destino. Não há necessidade de restaurar o SMK neste.

    Execute este script no db2 no Server1 para definir a criptografia SMK:

    OPEN MASTER KEY DECRYPTION BY PASSWORD = pass ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY ALTER MASTER KEY DROP ENCRYPTION BY PASSWORD = pass

    Tudo bem, dados em colunas criptografadas estão acessíveis no Server1 tanto em db1 quanto em db2, com SMK A conforme pretendido.

    Se você tiver quaisquer dados criptografados em suas instâncias, certifique-se de saber as senhas de seu DBMK e faça backup de seu SMK. Caso contrário, o backup db pode ser inútil. Há uma postagem do MSDN muito detalhada sobre como lidar com problemas com o SMK: http://blogs.msdn.com/b/lcris/archive/2007/11/14/sql-server-2005-how-to-recover-when-the -service-master-key-smk-is-not-accessible.aspx

    • 0

relate perguntas