主页 / dba / 问题 / 123150
Accepted
Mark Freeman
Asked: 2015-12-08 14:12:51 +0800 CST

由证书签名的存储过程的 SSDT 架构比较

  • 772

我在 Visual Studio Professional 2015 中使用 SSDT 14.0.50730.0。我正在比较两个实例之间的数据库，发现存在与其签名不匹配的存储过程。

在这两种情况下，证书都是在每个实例中使用相同的脚本创建的，并且签名也是由 CERTIFICATE 使用密码在每个实例中使用相同的脚本添加的。

但是，架构比较显示源和目标中的过程都显示为具有带有不匹配的签名值的签名的证书。生成的发布脚本删除证书签名，修改过程，然后添加证书签名带签名。签名值与源实例中显示的相匹配。但是，脚本失败并显示消息“公钥的签名无效”。

我必须做什么才能使这些程序同步？

sql-server ssdt

1 个回答

  1. Best Answer

    在深入了解有关证书创建和模块签名的任何细节之前，我们至少可以验证两个实例是否具有相同的证书。在两个实例上运行以下命令：

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

    如果证书相同，那么所有这些字段在两个实例（或同一实例上的两个数据库）中都将具有相同的值。但您主要只需要检查该thumbprint字段，因为它是证书的 SHA-1 哈希值。如果两个位置之间的值不同，那么您处理的不是同一个证书。

    你这么说

    证书是在同一个脚本的每个实例中创建的

    但不要具体说明证书是如何创建的。它们可以通过使用以下语法在 SQL Server 中生成（这称为“自签名”）：

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

    此方法将生成一个新的私钥（每次运行时）并ENCRYPTION BY PASSWORD使用由数据库主密钥加密的子句中指定的密码对其进行加密（这就是为什么最好指定密码的原因）。

    或者，您可以通过提供现有的程序集、文件或二进制文字来创建它们：

    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..... )

    和方法允许FILE有BINARY选择地指定私钥文件或二进制文字。因此，如果使用其中一种方法并指定私钥，或者如果使用已签名的ASSEMBLY，则以这种方式创建证书应该每次都为您提供相同的私钥。

    那么如何获得FILEorBINARY值呢？

    • 文件：

      创建初始证书后，使用以下命令导出它（包括私钥！）：

      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'
  );

      在新的地方：

      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'
  );

    • 二进制：（仅适用于 SQL Server 2012 及更新版本）

      创建初始证书后，使用以下方法获取证书和私钥值：

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

      在新的地方：

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

    如果您使用的是 SQL Server 2012 或更新版本，那么该BINARY方法将是最简单的，因为它是完全独立的（即不依赖任何外部文件）。

    请注意：

    • 无论选择上述哪种方法，初始证书创建只需要发生一次，并且可能需要手动完成，以便备份文件或二进制文字可以合并到 SSDT 项目中。
    • 如果私钥用于在目标数据库中创建证书，则不需要WITH SIGNATURE子句，ADD SIGNATURE因为它仅在私钥被删除或未用于创建证书时使用。在这种情况下（即匹配和使用私钥的
      所有字段），在两个具有完全相同定义的不同位置签署模块（如：逐字节相同），将产生相同的签名sys.certificatesCREATE CERTIFICATE
    • 如果私钥未用于创建证书，或已被删除（是的，您可以这样做），那么您需要指定WITH SIGNATURE子句并传入由同一私钥生成的签名的二进制文字以及来自某个位置的相同模块定义，该位置至少有一点确实拥有私钥。这是一种更安全的设置，因为它只允许对您为其提供签名的模块进行签名。如果证书没有私钥，它仍然可以用于检查已签名模块的有效性，但不能用于对新的或更改的模块进行签名（这很酷）。

    但是，如果：

    • 两个位置的证书相同，并且
    • 模式比较没有将模块定义标记为不同，并且
    • ADD SIGNATURE正在使用WITH SIGNATURE子句在目标数据库中使用，

    • 使用的二进制文字与通过以下方式在源数据库中找到的相同：

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

    那么您需要OBJECT_DEFINITION在两个位置检查该模块。它们需要相同，包括外壳等。您可以做一个SELECT CONVERT(VARBINARY(MAX), OBJECT_DEFINITION(OBJECT_ID(N'{module_name}')))只是为了确定。模式比较可能会忽略CREATE定义开头的注释和/或关键字的大小写和/或某些空白等的差异。

    有关详细信息，请参阅以下 MSDN 页面：

    • 4

相关问题