Always On Encrypted – Generating Certificates and Column Encryption Key ENCRYPTED_VALUE

NOTE: This needs .NET Framework 4.6 to be installed!

To use Always On Encrypted we:

• Create a certificate with required properties to be used with Always On Encryption
• Create a column master key definition
• Create a column encryption key using an encrypted value

How do we script this?

First we create a certificate in Windows, outside of SQL Server!

We use powershell to create a self-signed Certificate with the required options to be an certificate for Always On Encryption:

New-SelfSignedCertificate -CertStoreLocation Cert:\CurrentUser\My -DNSName “CN=Always Encrypted Certificate” -KeyUsage KeyEncipherment -TextExtension @(“2.5.29.37={text}1.3.6.1.5.5.8.2.2,1.3.6.1.4.1.311.10.3.11”) -provider “Microsoft Strong Cryptographic Provider”

which returns the Thumbprint property of the new Certificate (e.g. 18AD6F1E32BED9C299B9AE91FEB9AA0CEB87ABE9)

CertStoreLocation can be

• Cert:\CurrentUser\My – User level
• Cert:\LocalMachine\My – Machine level

As per https://technet.microsoft.com/en-us/library/hh848633.aspx the options include:

2.5.29.37 – Enhanced Key Usage includes

1.3.6.1.5.5.8.2.2 – IP security IKE intermediate

1.3.6.1.4.1.311.10.3.11 – Key Recovery

The Thumbprint can also be checked via certmgr.msc (CurrentUser Certificates) or certlm.msc (Local Machine Certifcates) or

dir Cert:\CurrentUser\My

dir Cert:\LocalMachine\My

Secondly in SQL Server we create a column master key definition using certificate provider and thumbprint for our certificate

As per https://msdn.microsoft.com/en-us/library/mt146393.aspx the provided key store provider is MSSQL_CERTIFICATE_STORE also a custom keystore provider can be created.

CREATE COLUMN MASTER KEY DEFINITION CMK1
WITH (
KEY_STORE_PROVIDER_NAME = N’MSSQL_CERTIFICATE_STORE’,
KEY_PATH = N’CurrentUser/My/18AD6F1E32BED9C299B9AE91FEB9AA0CEB87ABE9′
);

Thirdly we generate a column encryption key encrypted value using powershell

As per https://msdn.microsoft.com/en-gb/library/mt146372.aspx the plaintext value should be 256 bits (32 bytes)

$cmkprov = New-Object System.Data.SqlClient.SqlColumnEncryptionCertificateStoreProvider

$InBytes = New-Object Byte[] 32

$OutBytes = New-Object Byte[] 32

$RNG = New-Object System.Security.Cryptography.RNGCryptoServiceProvider

$RNG.GetBytes($InBytes,0,8)

[System.BitConverter]::ToString($InBytes)

// AC-78-F7-57-87-37-D7-B8-00-00-00-00-00-00-00-00-00-00-00-00-00-00-00-00-00-00-00-00-00-00-00-00

$OutBytes = $cmkprov.EncryptColumnEncryptionKey(“CurrentUser/My/18AD6F1E32BED9C299B9AE91FEB9AA0CEB87ABE9″,”RSA_OAEP”,$InBytes)

“0x” + [System.BitConverter]::ToString($OutBytes) -Replace ‘[-]’,”

// Join this into 1 long line and use to create the column encryption key

0x016E000001630075007200720065006E00740075007300650072002F006D0079002F0031003800610064003600660031006500330032006200650
06400390063003200390039006200390061006500390031006600650062003900610061003000630065006200380037006100620065003900124965
5EE838EC9BB949DB58103D9C77A0650825E8DE23007496279046759EA855CBC4B0D431061DCD0A0FCD7046DB4A55F8A6D3E83A773495299F33B6F07
3F73CD211A65933DACE945522E864AD7933D969944445566E5B90D63FF35F1AD874455C8770D9FE7A02586B49D843F99831B41036018835338D00CC
35F270D1C715A83FA08A3F5211428E5AA565846DBB1E977751E15A6F149043C71F08CC1B1C21B14DAAE641F17B9457BCCF2C0BC843A75FD8EC37D2F
443B91909316D3895D0660D8BB5C7F6B493884C898F9A021ED5A5BE298F06A2D714F538234F92A539DFA4611BE67DA846F6FC656A093FBE5BC70543
B5325A415953FAE66DC8E6DA29DEFB21BE14B3FFF24B37ACED94BD078922E7797A0ACE3F6182F8FCEFDE62C88E9DDF63F638B34148121DDF194657D
763C4BBFA2C748FBEE51C9FC6F6BBE1B440ED2E29FF2A99AD132587A23603D7E951F64C52FA88C30A798AADB5DBDF909E511B6CEBB29180BABFA154
4B6C700D44DBCBE90EB375CE6CE62C8AE906393F0671CA5921F6DB5BD7B6711F85B63FEEA08BBD3E371F1E1C02C147B47AF997E7BCCCC7EF558FB15
83414820DE11A4536EBC337D4A5EDE3F24DD816EEC11E96F1789670CF19E8AF63EABD423803E3E58CB63723E21C28ACC38B3DB98F14157E7AA94D66
EB5B045A232C3C7A0713A9E02E59A85F2FFBB521B6F2ECED1A407C76460A1A6655

Fourth we create the column encryption key using the encrypted value we calculated.

CREATE COLUMN ENCRYPTION KEY CEK1
WITH VALUES
(
COLUMN MASTER KEY DEFINITION = CMK1,
ALGORITHM = ‘RSA_OAEP’,
ENCRYPTED_VALUE = 0x016E000001630075007200720065006E00740075007300650072002F006D0079002F0031003800610064003600660031006500330032006200650064003900630032003900390062003900610065003900310066006500620039006100610030006300650062003800370061006200650039007083AC9696C6C9E594AC69EB68754C3C43F719EA7C18EFBAAB0B51F084E52678D38689AF0B055BEA5798DC64A15D9360D6183EB2111DD708D2AF2777CF683C88460DAE04F59C5B108EBA2C2421F8FC6DC0E8F5AF318D79CD52B0DAE03996214EA61447E44575D023E80DE66FEDA417F62B0502EE534350EEA59429BDDC99A1C97E78E1A5DFC702D35F4E92A8BE0E54515AC2574C2C2D1EB76E495B98E12973006B75990913B47EAA8B675852468F2ED18C20F7DC3B2E263D9378D97DC6B164D1119951DC015FECBDC4B44CF2D20D441B3ED843FFCF4DC2B6CAC3C8FCC131767ABF7CBED95B0D53F809F5CF7554B84900CAFADFBA5AA70EA99E183F213DDA7F91050CD9EC5FD3DC670734C20EE0761438C7803B6225F97D86EBB30451FE0D805C1C2A27A228C6EEF53ED0CC7668CB95B46D026E266651E102634241DEB66BC94500B2D8462E39713259101150C6D2EE7129F5668B301205580A1B4528011D875C706FAF13B658AC61B415CADEC887481B79C270A3DE7A66584123FE1FBFC89768A323210FAEA056F4AC1FFC5727B49581EC8F97F03186EDE5648E209FF0FD5E7A931080B1D69BA9A68D9D54640C13F0482C47F301F7DFD9EB31E96D3EC5466D7E734EEF48CDFE8FA540404C78506AB109A5378CD9D8DA7CB60B91F0A52A6F797624B2AFD26DF1C3A63F9FB5239F67DBB0819F4F54856F316A18244127BBA0AC2D
);

Now we can create a table and encrypt columns using this Column Encryption Key

CREATE TABLE dbo.Client
(
ClientId INT IDENTITY (1,1),
ClientAge INT
ENCRYPTED WITH (ENCRYPTION_TYPE = DETERMINISTIC,
ALGORITHM = ‘AEAD_AES_256_CBC_HMAC_SHA_256’,
COLUMN_ENCRYPTION_KEY = CEK1),
ClientSecret  NVARCHAR(200)
ENCRYPTED WITH (ENCRYPTION_TYPE = RANDOMIZED,
ALGORITHM = ‘AEAD_AES_256_CBC_HMAC_SHA_256’,
COLUMN_ENCRYPTION_KEY = CEK1),
);

The SQL Server Security blog entry where I asked about this is https://blogs.msdn.microsoft.com/sqlsecurity/2015/06/04/getting-started-with-always-encrypted/