Sql-server – How to generate the ENCRYPTED_VALUE for CREATE COLUMN ENCRYPTION KEY

always-encryptedencryptionpowershellsql serversql-server-2016

I can make a certificate in the windows certificate store for the master column key easily enough with:

$cert = New-SelfSignedCertificate `
    -Subject "Always Encrypted Sample Cert" `
    -CertStoreLocation Cert:\CurrentUser\My `
    -Type Custom
$thumbprint = $cert.ThumbPrint
Write-Verbose "Cert thumbprint $thumbprint"

$smoDatabase.ExecuteNonQuery(@"
CREATE COLUMN MASTER KEY [$($MasterKeyName)] 
WITH (
    KEY_STORE_PROVIDER_NAME = 'MSSQL_CERTIFICATE_STORE',
    KEY_PATH = N'CurrentUser/My/$($thumbprint)'
);
"@);

The problem come with the CREATE COLUMN ENCRYPTION KEY

CREATE COLUMN ENCRYPTION KEY key_name 
WITH VALUES (
    COLUMN_MASTER_KEY = [$($MasterKeyName)], 
    ALGORITHM = 'RSA_OAEP', 
    ENCRYPTED_VALUE = $EncryptedValue
);

I don't know how to get the calue for ENCRYPTED_VALUE. I try the following:

C:\Users\zippy> $certProvider = new-object 'System.Data.SqlCLient.SqlColumnEncryptionCertificateStoreProvider'
C:\Users\zippy> $byte = [byte[]]@() # I don't know what to put here
C:\Users\zippy> $certProvider.EncryptColumnEncryptionKey('Current User/My/0879e6a82f7fe3f88e91e37e69f08c8fa1f4cc48', 'RSA_OAEP', $byte)

And then I get

Exception calling "EncryptColumnEncryptionKey" with "3" argument(s): "Empty column encryption key specified.
Parameter name: columnEncryptionKey"
At line:1 char:1
+ $certProvider.EncryptColumnEncryptionKey('Current User/My/0879e6a82f7 …
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : ArgumentException

So how do I get the value to put in EncryptColumnEncryptionKey()?

Best Answer

Since your thumbprint is being returned as a System.String you actually cannot easily convert it to a System.Byte. I have not gotten a full example that you are doing to work completely but will show you how I convert the thumbprint, and it seems to work:

$encode = [System.Text.Encoding]::UTF8
$r1 = $encode.GetBytes($thumbprint)

$sha = New-Object System.Security.Cryptography.SHA256CryptoServiceProvider
$r2 = $sha.ComputeHash($r1)
[System.Convert]::ToBase64String($r2)

$certPath = 'CurrentUser/My/$($cert.Thumbprint)"
$certProvider = New-Object System.Data.SqlClient.SqlColumnEncryptionCertificateStoreProvider
$certProvider.EncryptColumnEncryptionKey($certPath,'RSA_OAEP',$r2)

The only thing I am getting right now to figure out is an error on Invalid provider type specified. Which I do have the March Preview of SSMS 2016 on my laptop, not sure if that has something to do with this or not.

Related Question