始终加密 - 创建证书错误 Windows 服务器 2012年

Question

>当我尝试使用以下PS脚本创建证书，列主密钥和列加密密钥时，它在Windows 10中工作正常

Import-Module "SqlServer"
$serverName = "XXX"
$databaseName ="XX"
$connStr = "Server = " + $serverName + "; Database = " + $databaseName + "; Integrated Security=true"
$connection = New-Object Microsoft.SqlServer.Management.Common.ServerConnection
$connection.ConnectionString = $connStr
$connection.Connect()
$server = New-Object Microsoft.SqlServer.Management.Smo.Server($connection)
$database = $server.Databases[$databaseName] 
$cert = New-SelfSignedCertificate -Subject "Cert" -CertStoreLocation Cert:LocalMachineMy -KeyExportPolicy Exportable -Type DocumentEncryptionCert -KeyUsage KeyEncipherment -KeySpec KeyExchange -KeyLength 2048

$cmkSettings = New-SqlCertificateStoreColumnMasterKeySettings -CertificateStoreLocation "LocalMachine" -Thumbprint $cert.Thumbprint 

$cmkName = "CMK1"
New-SqlColumnMasterKey -Name $cmkName -InputObject $database -ColumnMasterKeySettings $cmkSettings
$cekName = "CEK1"
New-SqlColumnEncryptionKey -Name $cekName -InputObject $database -ColumnMasterKey $cmkName

但是在Windows Server 2012中出现错误。 如果我删除以下内容：

-Subject, -KeyExportPolicy Exportable -Type DocumentEncryptionCert -KeyUsage KeyEncipherment -KeySpec KeyExchange -KeyLength 2048 

并仅使用-DNSName，然后它只会创建列主密钥并在创建列加密密钥时抛出错误。

有人请为我提供适用于 Windows Server 2012 并创建证书、列主密钥和列加密密钥的正确语法？

Answer 1

请参阅本文的"使用 PowerShell 创建自签名证书"部分。

要用作始终加密的 CMK，证书需要特定配置。

您应该能够使用以下命令创建要用作 CMK 的证书

New-SelfSignedCertificate is a Windows PowerShell cmdlet that creates a self-signed certificate. The below examples show how to generate a certificate that can be used as a column master key for Always Encrypted.
$cert = New-SelfSignedCertificate -Subject "AlwaysEncryptedCert" -CertStoreLocation Cert:CurrentUserMy -KeyExportPolicy Exportable -Type DocumentEncryptionCert -KeyUsage KeyEncipherment -KeySpec KeyExchange -KeyLength 2048 
# To create a certificate in the local machine certificate store location you need to run the cmdlet as an administrator.
$cert = New-SelfSignedCertificate -Subject "AlwaysEncryptedCert" -CertStoreLocation Cert:LocalMachineMy -KeyExportPolicy Exportable -Type DocumentEncryptionCert -KeyUsage KeyEncipherment -KeySpec KeyExchange -KeyLength 2048

这应该适用于服务器 2012，如果它不存在，则必须安装 makecert 实用程序：

makecert.exe -n "CN=Always Encrypted Certificate - exported" -pe -sr CurrentUser -r -eku 1.3.6.1.5.5.8.2.2,1.3.6.1.4.1.311.10.3.11 -ss my -sky exchange -sp "Microsoft Strong Cryptographic Provider" -sy 1 -len 2048 -a sha256