Download certificate from Key Vault w/ private key for use in SQL Server (TDE) via powershell

Question

I need to download an x509 Certificate from Azure Key Vault and import it in SQL Server to use for TDE.

Note: I'm not referring to EKM providers/ASYMMETRIC KEYs, where SQL accesses key vault to pull the key when needed.

My use case is simple: grab a cert from key vault and load it into SQL server. Due to security requirements, it's not possible to save the certificate to a file where SQL Server can access it.

I can download the certificate successfully and convert it to a X509Certificate2 object, but when I run the CREATE CERTIFICATE statement in SQL Server, it completes successfully but doesn't include the private key.

I believe I need to specify a PRIVATE KEY (BINARY = private_key_bits ) clause, but I can't figure out how to extract the private key from the X509Certificate2 object in a format that SQL Server will accept.

This powershell script runs w/o errors, but doesn't include the private key:

 $keyVault = "my-key-vault"
 $CertName = "tde-certificate"

 $SQLInstance = ".\SQLEXPRESS"
 $SQLCertName = "KeyVault_TDECert"


# download the KeyVaultSecret for the cert as base64 string
$certBase64 = Get-AzKeyVaultSecret -VaultName $keyVault -Name $CertName -AsPlainText

# Convert to a [System.Security.Cryptography.X509Certificates.X509Certificate2] object
$certBytes = [convert]::FromBase64String($certBase64)
$cert = new-object System.Security.Cryptography.X509Certificates.X509Certificate2( $certBytes, $null)  # byte[], $null for password

# CREATE CERTIFICATE in SQL Server.
# $cert.GetRawCertDataString() returns hex encoded data, which SQL Server gladly accepts.

$sql = "CREATE CERTIFICATE [$SQLCertName] FROM BINARY = 0x$($cert.GetRawCertDataString()) "

Invoke-sqlcmd -ServerInstance $SQLInstance -Database master -Query $sql

$cert.HasPrivateKey returns $true.

$cert.PrivateKey.key.ExportPolicy equals None which should mean unrestricted.

 $cert.PrivateKey.key | Select-Object *

Algorithm          : RSA
AlgorithmGroup     : RSA
ExportPolicy       : None
Handle             : Microsoft.Win32.SafeHandles.SafeNCryptKeyHandle
IsEphemeral        : False
IsMachineKey       : False
KeyName            : <xxxxxx>
KeySize            : 3072
KeyUsage           : AllUsages
ParentWindowHandle : 0
Provider           : Microsoft Enhanced RSA and AES Cryptographic Provider
ProviderHandle     : Microsoft.Win32.SafeHandles.SafeNCryptProviderHandle
UIPolicy           : System.Security.Cryptography.CngUIPolicy
UniqueName         : <xxxxxxx>

How can I download a certificate from Key Vault with the private key and load it into SQL Server?

---

Update 1:

Changed to .Export("pfx"); need to add "Exportable" to constructor to make private key exportable.

$cert = new-object System.Security.Cryptography.X509Certificates.X509Certificate2( $certBytes, $null)  # byte[], $null for password

# CREATE CERTIFICATE in SQL Server.
# $cert.GetRawCertDataString() returns hex encoded data, which SQL Server gladly accepts.

$sql = "CREATE CERTIFICATE [$SQLCertName] FROM BINARY = 0x$($cert.GetRawCertDataString()) "

to

$cert = new-object System.Security.Cryptography.X509Certificates.X509Certificate2( $certBytes, $null, "Exportable")  # byte[], $null for password,

# CREATE CERTIFICATE in SQL Server.
# $cert.GetRawCertDataString() returns hex encoded data, which SQL Server gladly accepts.

$sql = "CREATE CERTIFICATE [$SQLCertName] FROM BINARY = 0x$([Convert]::ToHexString($cert.Export(Pfx))) "

Now I receive a SQL error:

Msg 15468, Level 16, State 6, Line 5
An error occurred during the generation of the certificate.

Google turns up an old MS blog post where this occurred due to pfx not being a supported certificate format (affect SQL 2014). I'm testing on SQL 2022 (but would like support for 2016+)

Answer 1

Get-AzKeyVaultSecret allows you to download a certificate as a .pfx file. SQL Server expects the public certificate data to be in DER format and the private key data to be in .pvk format.

The X509Certificate2.GetRawCertDataString() method returns the public data in DER format.

The .pvk is a Microsoft proprietary format that is a thin wrapper around a csp blob. A csp blob is private key binary representation used in the native Win32 Crypto API. Format details from this SO question: How do I create/export to a .PVK file using C#?.

# download the KeyVaultSecret for the cert as base64 string
$certBase64 = Get-AzKeyVaultSecret -VaultName $keyVault -Name $CertName -AsPlainText

#convert to a [System.Security.Cryptography.X509Certificates.X509Certificate2] object
$certBytes = [convert]::FromBase64String($certBase64)
$cert = new-object System.Security.Cryptography.X509Certificates.X509Certificate2( $certBytes, $null, "Exportable")  # byte[], $null for password, "Exportable"

# GetRawCertData() is DER format
$certHex = $cert.GetRawCertDataString()

# $cert.PrivateKey is a [System.Security.Cryptography.RSACng]
# convert to type [RSACryptoServiceProvider] so we can call ExportCspBlob()
$keyParams = $cert.PrivateKey.ExportParameters($true)
$rsaProvider = new-object System.Security.Cryptography.RSACryptoServiceProvider
$rsaProvider.ImportParameters($keyParams)
$cspBlob = $rsaProvider.ExportCspBlob($true)

# pvk header, from  https://stackoverflow.com/questions/51750788/how-do-i-create-export-to-a-pvk-file-using-c
#
# [uint]0xB0B5F11E   # PVK magic number
# [uint]0
# [uint]1     # KEYTYPE_KEYX for RSA
# [uint]0     # not encrypted
# [uint]0     # encryption salt length

$pvkHeader =  [convert]::ToHexString([System.BitConverter]::GetBytes(0xB0B5F11E)) `
    + [convert]::ToHexString([System.BitConverter]::GetBytes(0u)) `
    + [convert]::ToHexString([System.BitConverter]::GetBytes(1u)) `
    + [convert]::ToHexString([System.BitConverter]::GetBytes(0u)) `
    + [convert]::ToHexString([System.BitConverter]::GetBytes(0u)) `
    + [convert]::ToHexString([System.BitConverter]::GetBytes([Uint32]$cspBlob.Length))

$privateKeyHex = $pvkHeader + [Convert]::ToHexString($cspBlob)

$sql = "CREATE CERTIFICATE [$SQLCertName] FROM BINARY = 0x$($certHex) 
`n
WITH PRIVATE KEY `n(BINARY = 0x$($privateKeyHex));
"

Invoke-sqlcmd -ServerInstance $SQLInstance -Database master -Query $sql