How to generate ENCRYPTED_VALUE for CEK in SQL Server Always Encrypted

Question

It is possible to generate ENCRYPTED_VALUE in different way than by SSMS? I like to enable "Always encrypted" feature for chosen columns but I am wondering about encrypting data by unknown key.

In my oppinion there always should be way to decrypt data without connecting to azure key vault(like i understood for encrypting data SQL Server is using symmetric AES256).

CREATE COLUMN ENCRYPTION KEY MyCEK   
WITH VALUES  
(  
    COLUMN_MASTER_KEY = MyCMK,   
    ALGORITHM = 'RSA_OAEP',   
    ENCRYPTED_VALUE = (i am talking about this value)
);

score 4 · Answer 1 · answered Apr 06 '18 at 14:30

After search session with google i discovered how to do that. Here is sample code(c#) that generate this value.

        var provider = new SqlColumnEncryptionAzureKeyVaultProvider(GetTokenAsync);
        var randomBytes = new byte[32];

        using (var rng = new RNGCryptoServiceProvider())
        {
            rng.GetBytes(randomBytes);
        }

        var base64 = Convert.ToBase64String(randomBytes);

        var encryptedKey = provider.EncryptColumnEncryptionKey(masterKeyPath, "RSA_OAEP", randomBytes);
        var encryptedKeySerialized = "0x" + BitConverter.ToString(encryptedKey).Replace("-", "");

I recommend you guys to look at this article if you want to know more about that: https://blogs.msdn.microsoft.com/sqlsecurity/2015/09/25/creating-custom-key-store-providers-for-always-encrypted-azure-key-vault-example/

How to generate ENCRYPTED_VALUE for CEK in SQL Server Always Encrypted

1 Answers1