Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
I want to create a new column master key using certificate created in advanced. I use the following command to create a certificate in the Windows Certificate Store - Local Machine.
New-SelfSignedCertificate -CertStoreLocation cert:\LocalMachine\My -DnsName TEST_CERT_01
The issue is that when I try to select this certificate, I get the following error:
Can anyone tell what's wrong with the certificate?
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: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
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
