2

I see that the various articles on SQL Server 2016's Always Encrypted feature describe creating the column encryption key using either SQL Server Management Studio UI or by using the New-​Sql​Column​Encryption​Key powershell command. But what if the C# client application itself wants to create the column encryption key? What is the format of this ENCRYPTED_VALUE column?

CREATE COLUMN ENCRYPTION KEY CEK1
WITH VALUES (
COLUMN_MASTER_KEY = CMK1,
ALGORITHM = 'RSA_OAEP',
ENCRYPTED_VALUE = ??????
explunit
  • 18,967
  • 6
  • 69
  • 94

1 Answers1

4

For the Certificate Store provider, you can use the SqlColumnEncryptionCertificateStoreProvider.EncryptColumnEncryptionKey Method, e.g. as shown below:

var randomBytes = new byte[ 32 ];
using (var rng = new RNGCryptoServiceProvider())
{
    rng.GetBytes( randomBytes );
}
var provider = new SqlColumnEncryptionCertificateStoreProvider();
var encryptedKey = provider.EncryptColumnEncryptionKey( masterKeyPath, "RSA_OAEP", randomBytes );
var encryptedKeySerialized = "0x" + BitConverter.ToString( encryptedKey ).Replace( "-", "" );

Credit to this blog post for pointing me in the right direction.

explunit
  • 18,967
  • 6
  • 69
  • 94
  • This was just what I needed. I couldn't find a way of doing this without powershell until this. It's quite easy to get it to work with Key Vault also, just change the provider. – Ian1971 Apr 10 '18 at 14:28