-->

Always Encrypted - create certificate error window

2019-08-24 14:17发布

问题:

when i try to create certificate, column master key and column encryption key using the below PS script its works fine in 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:LocalMachine\My -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

but getting error in windows server 2012. if i remove the following:

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

and use -DNSName only then it just create column master key and throw error while create column encryption key.

someone please provide me correct syntax which works on Windows server 2012 and create certificate , column master key and column encryption key?

回答1:

Please refer to Create a self-signed certificate using PowerShell section of this article.

To be used as Always encrypted CMKs, certificates require a specific configuration.

You should be able to create a certificate to be used as CMK using the following commands

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:CurrentUser\My -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:LocalMachine\My -KeyExportPolicy Exportable -Type DocumentEncryptionCert -KeyUsage KeyEncipherment -KeySpec KeyExchange -KeyLength 2048

This should work with Server 2012, you will have to install makecert utility if it does not exist:

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