Data Vault Design in Azure SQL - Read PII with Customer Keys

Problem Statement

In today’s modern era, data is referred to as the new oil or the new diamond. Data is the key - the power to build more accurate ML/AI models for better analysis of customers or to build/upgrade the products. This increase in data consumption in turn leads to many data security issues. Over the past couple of years, data security attacks have increased significantly. Personally Identifiable Information (PII) uses data to confirm an individual's identity, so protecting it is essential for personal privacy. With multiple clients migrating over to the cloud, cloud security and protecting the PII data becomes of paramount importance to prevent any data hacks. 

One can protect the data in the cloud by ensuring private connections, strict Firewall rules Identity authorization, etc but the data management team having access to the data storage layer can access and/or compromise the PII data. One way to ensure data safety is by enabling Dynamic data masking (DDM) thereby limiting sensitive data exposure by masking it to nonprivileged users. But are there any Client-side encryption methods to ensure the safety of data without compromising on the data ingestion process even for the ones having admin access on the data architecture?

Prerequisites

1. Azure SQL Database
2. Azure Key Vault
3. SSMS

Solution

In Azure SQL Database, there is server-side encryption like Transparent data encryption to protect data at rest. But a DAB having access to the database can still access the entire data in plain text despite Server-side encryption enabled. Hence, there needs to be Client-side encryption in combination with Server-side encryption to ensure PII data is always protected and only accessible to data owners governing the encryption keys to decrypt the data. Others including the DBA will only be able to see data in encrypted state and not the underlying plain text data.

Azure SQL Database provides an out-of-the-box functionality of Always encrypted to accomplish Client-side encryption. To protect the data in memory from data theft, one can use Always Encrypted which encrypts sensitive data in memory or in use during computations.

Customer-managed TDE work (reference: Microsoft Doc)

Implementation

1. In Azure Portal, navigate to the Azure SQL server configuration and proceed to the Transparent data encryption security setting.
2. To use Bring Your Own Key (BYOK), Click on the Customer-managed key; then navigate to “Select a Key” and finally Click on “Change key”.

On the next page, you need to create a new Azure Key Vault (AKV) or select an existing AKV.

Steps to Create a new AKV (reference: Microsoft doc).

In case there is already an AKV, select it from the drop-down.

In the key field, click on Create new key. It redirects you to a page, as shown below. On this page, enter the key name, select the key type as RSA, and use the default RSA key size as 2048.

In the final step, Select the version of the Key.

Click on Select, and you can view the key configurations in the customer-managed key selection.

Note. At the bottom, it gives a message, “SQL uses Get, Wrap Key, Unwrap Key permissions to access the selected key vault. These permissions are only used to access the key vault for TDE.”

Also, the AKV must have Soft-delete and Purge protection enabled else there would be the below error.

Create the Encryption Keys

I) Column Master Key

1. Log in to the Azure SQL database in which we need to enable encryption via an AD account via SSMS.
2. Expand the Database, expand the Security, and finally expand the Always Encrypted Keys.
   
3. Right-click on the Column Master Keys folder and select New Column Master Key to launch the New Column Master Key wizard.
   
4. Provide the name for the key and Select AKV from the key store drop-down. Click on the Sign In and authenticate with the necessary credentials.
   

Note. The Credential should have to create, get, list, sign, verify, wrap, and unwrap permissions within the Access Policies of the AKV.

1. Upon successful authentication, select the correct subscription, AKV, and key from the options presented. Click OK to close the wizard.
   

Script version

II) Column Encryption Key

Similarly, right-click on the Column Encryption Keys folder and select New Column Encryption Key to launch the New Column Encryption Key Wizard. Provide a name for the key and select the appropriate column master key to protect the column encryption key. Click OK to close the wizard.

Both Keys

1. Apply Encryption on Tables
2. In our use case, the sample data consists of Employee details consisting of Name and SocialId (PII: which should be encrypted)
   

Expand the Database, expand the Tables, and finally expand the Columns of the table selected.

Right-click the column and choose Encrypt Column. The Always Encrypted wizard would open.

On the Column Selection page click the check box next to the column(s) to be encrypted choose either deterministic or randomized in the Encryption Type and select the column encryption key created earlier in the Encryption Key column.

Click Next and Proceed to Finish Now.

Note. If the table has a huge amount of data or is actively being written(transactional), it is better to change the option to Generate a PowerShell script to run later and schedule the encryption to run during an off hour’s maintenance time.

Updates table definition

  Select Query execution on the Employee table with the SocialId column being encrypted.

1. View the Decrypted version of the data.
2. Create a new connection to the database via SSMS with the below Config. When connecting to the server expand the Options on the Connect to Server dialog, switch to the Additional Connection Parameters page enter the text Column Encryption Setting = Enabled, and click Connect.
   

When running queries in SSMS against a table with encrypted data you may be prompted to authenticate to Azure before being shown the decrypted data.

Challenges in implementing the solution

1. Any misstep with the customer-managed key or accidental Azure Key Vault deletion would lead to loss of database access or the database being in an inaccessible state.
2. Unlike service-managed key, where Azure will take care of key management and rotation seamlessly with no user intervention required; in customer-managed key, the user needs to manage the backup and recovery of the keys which adds an additional overhead.

Business Benefit

1. The customer-managed key allows separation of duties between the management of keys and data to help meet compliance with organization security policies.
2. Key Vault administrator can revoke key access policy permissions to revoke access to a database.
3. TDE with customer-managed keys improves on service-managed keys by enabling central management of keys in Azure Key Vault, giving customers full and granular control over usage and management of the TDE protector.
4. Allows central management of Keys in Azure key vault.