Comparação de esquema SSDT para procedimento armazenado assinado por um certificado

Antes de entrar em detalhes sobre a criação do certificado e a assinatura do módulo, podemos pelo menos verificar se as duas instâncias têm ou não o mesmo certificado. Execute o seguinte em ambas as instâncias:

SELECT [thumbprint], [cert_serial_number], [sid],
       [issuer_name], [subject],
       [expiry_date], [start_date]
FROM   sys.certificates
WHERE  [name] = N'cert_name';

Se os certificados forem iguais, todos esses campos terão valores idênticos em ambas as instâncias (ou em ambos os bancos de dados na mesma instância). Mas você só precisa verificar o thumbprintcampo, pois é um hash SHA-1 do certificado. Se esse valor for diferente entre os dois locais, você não está lidando com o mesmo certificado.

Você diz que

o Certificado foi criado em cada instância a partir do mesmo script

mas não mencione exatamente como os Certificados estão sendo criados. Eles podem ser gerados no SQL Server (isso é conhecido como "autoassinado") usando a seguinte sintaxe:

CREATE CERTIFICATE [cert_name]
  ENCRYPTION BY PASSWORD = 'certpassword' -- this is optional but best to use
  WITH SUBJECT = 'cert_subject';

Este método irá gerar uma nova chave privada (cada vez que for executado) e criptografá-la com a senha especificada na ENCRYPTION BY PASSWORDcláusula mais criptografada pela chave mestra do banco de dados (é por isso que é melhor especificar a senha).

Ou você pode criá-los fornecendo um literal Assembly, File ou Binary existente:

CREATE CERTIFICATE [cert_name]
FROM
   ASSEMBLY assembly_name
 | EXECUTABLE FILE = 'path_to_DLL'
 | FILE = 'path_to_file'
   WITH PRIVATE KEY ( FILE = 'path_to_private_key' )
 | BINARY = 0x......
   WITH PRIVATE KEY ( BINARY = 0x..... )

Os métodos FILEe BINARYpermitem especificar opcionalmente um arquivo de chave privada ou literal binário. Portanto, se estiver usando um desses métodos juntamente com a especificação da chave privada, ou se estiver usando o assinado ASSEMBLY, a criação de um certificado dessa maneira deve fornecer a você a mesma chave privada todas as vezes.

Então, como você obtém os valores FILEou BINARY?

• ARQUIVO:

  Depois de criar o Certificado inicial, exporte-o (incluindo a chave privada!) usando o seguinte:

  BACKUP CERTIFICATE [cert_name]
    TO FILE = 'path\to\cert_file.cer' 
    WITH PRIVATE KEY
    (
      DECRYPTION BY PASSWORD = 'cert_password',
      FILE = 'path\to\key_file.pvk',
      ENCRYPTION BY PASSWORD = 'file_password'
    );
  

  E no novo local:

  CREATE CERTIFICATE [cert_name]
    FROM FILE = 'path\to\cert_file.cer'
    WITH PRIVATE KEY
    (
      FILE = 'path\to\key_file.pvk',
      DECRYPTION BY PASSWORD = 'file_password',
      ENCRYPTION BY PASSWORD = 'cert_password'
    );
  

• BINARY: (disponível apenas no SQL Server 2012 e mais recente)

  Depois de criar o certificado inicial, obtenha os valores do certificado e da chave privada usando o seguinte:

  SELECT CERTENCODED(CERT_ID('cert_name')) AS [CertificateBinary],
         CERTPRIVATEKEY(
                        CERT_ID('cert_name'),
                        'temp_password',
                        'cert_password'
                       ) AS [PrivateKeyBinary];
  

  E no novo local:

  CREATE CERTIFICATE [cert_name]
    FROM BINARY = 0x{CertificateBinary}
    WITH PRIVATE KEY
    (
      BINARY = 0x{PrivateKeyBinary},
      DECRYPTION BY PASSWORD = 'temp_password',
      ENCRYPTION BY PASSWORD = 'cert_password'
    );
  

Se você estiver usando o SQL Server 2012 ou mais recente, o BINARYmétodo seria o mais fácil, pois é totalmente independente (ou seja, não depende de nenhum arquivo externo).

OBSERVE:

• Independentemente do método escolhido acima, a criação inicial do certificado só precisa acontecer uma vez e provavelmente precisa ser feita manualmente para que o(s) arquivo(s) de backup ou literais binários possam ser incorporados ao projeto SSDT.
• Se a chave privada estiver sendo usada para criar o Certificado no banco de dados de destino, não há necessidade da WITH SIGNATUREcláusula ADD SIGNATURE, pois ela só é usada quando a chave privada foi removida ou não foi usada na criação do Certificado.
  Neste caso (ou seja, todos esses campos sys.certificatescorrespondentes e usando a chave privada no CREATE CERTIFICATE), os módulos de assinatura em dois locais diferentes que têm exatamente a mesma definição (como em: byte a byte o mesmo) produzirão a mesma assinatura
• Se a chave privada não estiver sendo usada para criar o Certificado, ou foi removida (sim, você pode fazer isso), então você precisa especificar a WITH SIGNATUREcláusula e passar o literal binário da assinatura que foi gerada pela mesma chave privada e a mesma definição de módulo de um local que pelo menos em um ponto tenha a chave privada. Esta é uma configuração mais segura porque permite apenas a assinatura de módulos para os quais você fornece as assinaturas. Se um Certificado não tiver uma chave privada, ele ainda pode ser usado para verificar a validade dos módulos assinados, mas não pode ser usado para assinar módulos novos ou alterados (o que é bem legal).

MAS , se:

• os certificados em ambos os locais são idênticos e
• a comparação de esquema não sinalizou as definições do módulo como sendo diferentes e
• ADD SIGNATUREestá sendo usado no banco de dados de destino usando a WITH SIGNATUREcláusula,

• o literal binário que está sendo usado é o mesmo encontrado no banco de dados de origem por meio de:

  SELECT OBJECT_NAME(major_id), crypt_property
  FROM   sys.crypt_properties;
  

então você precisa verificar o OBJECT_DEFINITIONmódulo em ambos os locais. Eles precisam ser idênticos , incluindo caixa, etc. Você pode fazer um SELECT CONVERT(VARBINARY(MAX), OBJECT_DEFINITION(OBJECT_ID(N'{module_name}')))só para ter certeza. É possível que a comparação do esquema esteja ignorando diferenças nos comentários e/ou maiúsculas e minúsculas da CREATEpalavra-chave no início da definição e/ou algum espaço em branco, etc.

Consulte as seguintes páginas do MSDN para obter detalhes:

• CRIAR CERTIFICADO
• CERTIFICADO DE BACKUP
• CÓDIGO CERTO
• CERTPRIVATEKEY
• CERT_ID
• ADICIONAR ASSINATURA
• sys.certificates
• sys.crypt_properties