Como verifico se a criptografia da chave mestra do banco de dados é válida?

Para determinar programaticamente se o SMK atual foi usado para proteger o DMK, você deve ser capaz de simplesmente tentar uma operação que exija o DMK. Essa operação precisaria primeiro descriptografar o DMK para usá-lo. Supondo que você não tenha aberto o DMK explicitamente (usando a senha fornecida ao criá-lo), descriptografar o DMK exigirá o SMK. Se o SMK atual não for o SMK correto , o DMK não será descriptografado automaticamente e a operação falhará. Então:

• Se você possui um Certificado que existe com certeza no Banco de Dados que está sendo restaurado, tente usá-lo:

  SELECT SIGNBYCERT( CERT_ID( '{certificate_name}' ), 'test' );
  

  Isso deve retornar um NULLvalor não VARBINARY. Se o valor de retorno for NULL, o DMK precisará ser regenerado (conforme as instruções abaixo).

• Se nenhum certificado existir com certeza no banco de dados que está sendo restaurado, tente criar um. Se o SMK puder ser usado para descriptografar automaticamente o DMK, o certificado será criado; caso contrário, a operação falhará:

  BEGIN TRY
    CREATE CERTIFICATE [TestCert] WITH SUBJECT = 'yadda yadda yadda';
    DROP CERTIFICATE [TestCert];
    PRINT 'All good, yo!';
  END TRY
  BEGIN CATCH
    PRINT ERROR_MESSAGE();
  END CATCH;
  

No entanto, como você acabou de restaurar um banco de dados proveniente de outra instância e não restaurou o SMK dessa outra instância na nova instância, é seguro assumir que a resposta é: "não, o DMK não está criptografado com o SMK deste servidor".

Este é um cenário esperado que requer as seguintes etapas para remediar:

USE [newly_restored_db];
OPEN MASTER KEY DECRYPTION BY PASSWORD = '{password}'; -- password used to protect DMK
ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = '{password}';
CLOSE MASTER KEY;

A página do MSDN para CREATE MASTER KEYestados (ênfase adicionada):

Para SQL Server e Parallel Data Warehouse, a chave mestra normalmente é protegida pela chave mestra de serviço e pelo menos uma senha. Caso o banco de dados seja movido fisicamente para um servidor diferente (envio de log, restauração de backup, etc.), o banco de dados conterá uma cópia da chave mestra criptografada pela chave mestra de serviço do servidor original (a menos que essa criptografia tenha sido explicitamente removida usando ALTER MASTER KEY DDL) e uma cópia criptografada por cada senha especificada durante as operações CREATE MASTER KEY ou ALTER MASTER KEY DDL subsequentes. Para recuperar a chave mestra e todos os dados criptografados usando a chave mestra como raiz na hierarquia de chaves após a movimentação do banco de dados, o usuário terá [para] usar [a] instrução OPEN MASTER KEY usando um dos a(s) senha(s) usada(s) para proteger a Chave Mestra, restaure um backup da Chave Mestra ou restaure um backup da Chave Mestra de Serviço original no novo servidor.

A página do MSDN para OPEN MASTER KEYos estados:

Quando um banco de dados é anexado ou restaurado pela primeira vez a uma nova instância do SQL Server, uma cópia da chave mestra do banco de dados (criptografada pela chave mestra do serviço) ainda não é armazenada no servidor. Você deve usar a instrução OPEN MASTER KEY para descriptografar a chave mestra do banco de dados (DMK). Depois que o DMK for descriptografado, você terá a opção de ativar a descriptografia automática no futuro usando a instrução ALTER MASTER KEY REGENERATE para fornecer ao servidor uma cópia do DMK, criptografada com a chave mestra de serviço (SMK). Quando um banco de dados foi atualizado de uma versão anterior, o DMK deve ser regenerado para usar o algoritmo AES mais recente. Para obter mais informações sobre como regenerar o DMK, consulte ALTER MASTER KEY (Transact-SQL). O tempo necessário para regenerar a chave DMK para atualizar para AES depende do número de objetos protegidos pela DMK. Gerar novamente a chave DMK para atualizar para AES é necessário apenas uma vez e não tem impacto em regenerações futuras como parte de uma estratégia de rotação de chaves.

A página do MSDN para ALTER MASTER KEY afirma:

A opção REGENERATE recria a chave mestra do banco de dados e todas as chaves que ela protege. As chaves são primeiro descriptografadas com a chave mestra antiga e depois criptografadas com a nova chave mestra. Essa operação de uso intensivo de recursos deve ser agendada durante um período de baixa demanda, a menos que a chave mestra tenha sido comprometida.