2

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()?

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.