Visual Studio Database Project and Always Encrypted

We can use Always Encrypted to secure user data in particular column in SQL Server. I won't explain about Always Encrypted in this article, so please read the official doc.

It's very easy to configure via SSMS or PowerShell.

• Configure Always Encrypted using SQL Server Management Studio
• Configure Always Encrypted using PowerShell

However, as developer I want to configure it via Visual Studio Database project as we can manage it via source control.

Prerequisites

There are several combinations to support this scenario, and I use Azure SQL as database engine and Azure Key Vault to store master key.

• Azure SQL
• Key Vault
• Visual Studio with database template

We can use PowerShell to auto provision these services, but I believe most of us already have resource or IaC. In this article, I use my existing resources.

Create Keys

To use Always Encrypted, we need two keys.

• Column Master Key (Store in Key Vault)
• Column Encryption Key (Store in SQL database)

Create Master Key in Azure Key Vault

There are many ways to create key, and I use PowerShell in this sample. I took this script from here and just use some portion.

As I already have my Key Vault setup, I comment out several commands. The important thing to note is permission. We need get, create, delete, list, wrapKey,unwrapKey, sign, verify permissions to use Always Encrypted.

Import-Module Az
Connect-AzAccount
$SubscriptionId = "<Azure SubscriptionId>"
$resourceGroup = "devto"
$azureLocation = "eastus"
$akvName = "kenakamukv"
$akvKeyName = "CMK1"
$azureCtx = Set-AzConteXt -SubscriptionId $SubscriptionId # Sets the context for the below cmdlets to the specified subscription.
#New-AzResourceGroup -Name $resourceGroup -Location $azureLocation # Creates a new resource group - skip, if your desired group already exists.
#New-AzKeyVault -VaultName $akvName -ResourceGroupName $resourceGroup -Location $azureLocation # Creates a new key vault - skip if your vault already exists.
Set-AzKeyVaultAccessPolicy -VaultName $akvName -ResourceGroupName $resourceGroup -PermissionsToKeys get, create, delete, list, wrapKey,unwrapKey, sign, verify -UserPrincipalName $azureCtx.Account
$akvKey = Add-AzKeyVaultKey -VaultName $akvName -Name $akvKeyName -Destination "Software"

Confirm a key is added.

Create Column Encryption Key in Database Project

We manage keys as part of database project.

1. Create database project via Visual Studio. I created "AlwaysEncrypted" database project.

2. Add new item and select "Column Encryption Key".

3. Enter names for both keys, and select "Azure Key Vault" for Master key. Click "Create".

4. Set Key Vault information to master key.

• KEY_STORE_PROVIDER_NAME: AZURE_KEY_VAULT
• Key_Path: Pass to the created key

CREATE COLUMN MASTER KEY [CMK_Auto1]
WITH
(
    KEY_STORE_PROVIDER_NAME = N'AZURE_KEY_VAULT',
    KEY_PATH = N'https://kenakamukv.vault.azure.net/keys/CMK1/4264df67d0154312ad86e3b82c29f898'
)
GO

5. Run following PowerShell script to obtain encrypted value. New-SqlColumnEncryptionKeyEncryptedValue cmdlet calculates the encrypted value by using specified Azure Key Vault key.

$cmkSettings = New-SqlAzureKeyVaultColumnMasterKeySettings -KeyUrl "https://kenakamukv.vault.azure.net/keys/CMK1/4264df67d0154312ad86e3b82c29f898"
$encryptedValue = New-SqlColumnEncryptionKeyEncryptedValue -TargetColumnMasterKeySettings $cmkSettings 
$encryptedValue | Set-Clipboard

6. Paste the value to ENCRYPTED_VALUE. As a result, it should look like below.

Create table with secure column

In database project, add a table to test the key.

1. Add table. I named it as User. Add one column for test and configure ENCRYPTED WITH column definitions. See CREATE TABLE (Transact-SQL) for more detail.

2. Publish from Visual Studio to any database.

Confirm the result

We can run query to confirm how database is configured.

SELECT * FROM sys.column_master_keys
SELECT * FROM sys.column_master_key_definitions
SELECT * FROM sys.column_encryption_keys
SELECT * FROM sys.column_encryption_key_values
SELECT encryption_type_desc FROM sys.all_columns WHERE name = 'SecretValue'

We can also use SSMS to manipulate data if you configure it correctly.

1. Connect to database by enabling "Enable Always Encrypted" from options.

2. Once connected, make sure following option is enabled.

3. Run following query to test.

DECLARE @secretValue NVARCHAR(50) = 'secret'
INSERT INTO [User] (Id, SecretValue) VALUES (1, @secretValue)

SELECT * FROM [User]

Summary

Always Encrypted is great feature to separate management of keys between DBA and Key Admin. There are several more things to consider from security point of view though.

• Should we store encrypted value to source control?
• How we should manage key rotation?

These are important question especially for production.

For key rotation, see Rotate Always Encrypted keys using PowerShell for more detail.