Always Encrypted manually decrypt Column Encryption Key

Question

I've configured Always Encrypted for my SQL installation, that is I've got a CMK pointing towards a Windows Keystore key, which in turn is used to decrypt the CEK. Now I'm trying to think of some nice backup solutions for the CMK.

Currently I have the exact same RSA key configured in Azure, I've confirmed both keys to work (Windows Keystore key and Azure) by encrypting with the first and decrypting with the latter.

But the problem I'm having is, in case I lose the windows keystore key, I lose the ability to decrypt Always Encrypted keys. The Azure key doesn't "expose" the key, meaning I can encrypt and decrypt with the key, but I can't export it.

When configuring key rotation in SQL you need the "original key". I've tried to simply make a new CMK in SQL which points to the Azure environment by using "ALTER COLUMN ENCRYPTION KEY", but I get an error when I try to access the data.

My guess is that the CEK contains some metadata linking it to the key that is Windows based.

My question then is, is there a way to manually decrypt the column encryption key using a valid RSA key?

Utkarsh Pal · Accepted Answer · 2022-09-06T03:41:02.147

My question then is, is there a way to manually decrypt the column encryption key using a valid RSA key?

Yes, you can manually decrypt the column encryption key and master key using Always Encrypted with secure enclaves, but these features are only allowed in DC-series hardware configuration along with Microsoft Azure Attestation which are available only in few Locations. So, you need to select a location (an Azure region) that supports both the DC-series hardware and Microsoft Azure Attestation.

Note: DC-series is available in the following regions: Canada Central, Canada East, East US, North Europe, UK South, West Europe, West US. Choose DC-series while deploying the SQL Database by following the steps below.

Make sure to SQL Server is deployed in DC-series supported location. Click on configure database.
Select hardware configuration
Select DC-series, click on OK, Apply and deploy the database.
Now create attestation provider using Azure Portal. Search for attestation in search bar and select Microsoft Azure Attestation.

On the Overview tab for the attestation provider, copy the value of the Attest URI property to clipboard and save it in a file. This is the attestation URL, you will need in later steps.
Select Policy on the resource menu on the left side of the window or on the lower pane.
Set Attestation Type to SGX-IntelSDK.
Select Configure on the upper menu.

Set Policy Format to Text. Leave Policy options set to Enter policy.
In the Policy text field, replace the default policy with the below policy.

       [ type=="x-ms-sgx-is-debuggable", value==false ]
        && [ type=="x-ms-sgx-product-id", value==4639 ]
        && [ type=="x-ms-sgx-svn", value>= 0 ]
        && [ type=="x-ms-sgx-mrsigner", value=="e31c9e505f37a58de09335075fc8591254313eb20bb1a27e5443cc450b6e33e5"]

    => permit(); }; ```

Configure your database in SSMS. Click on Options and give attestation URL which you have copied in step 5.
Using the SSMS instance from the previous step, in Object Explorer, expand your database and navigate to Security > Always Encrypted Keys.
Provision a new enclave-enabled column master key:
Right-click Always Encrypted Keys and select New Column Master Key....
Select your column master key name: CMK1.Make sure you select either Windows Certificate Store (Current User or Local Machine) or Azure Key Vault.
Select Allow enclave computations.

Now simply encrypt your column. See below example to encrypt.

ALTER TABLE [HR].[Employees]
ALTER COLUMN [SSN] [char] (11) COLLATE Latin1_General_BIN2
ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK1], ENCRYPTION_TYPE = Randomized, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL
WITH
(ONLINE = ON);

ALTER TABLE [HR].[Employees]
ALTER COLUMN [Salary] [money]
ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK1], ENCRYPTION_TYPE = Randomized, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL
WITH
(ONLINE = ON);

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

Verify the encrypted data.

To decrypt using customer encrypt key, see below example.

ALTER TABLE [HR].[Employees]  
ALTER COLUMN [SSN] [char](11) COLLATE Latin1_General_BIN2  
WITH (ONLINE = ON); 
GO

Always Encrypted manually decrypt Column Encryption Key

1 Answers1