Configuring Azure SQL database to use Key Vault for AlwaysEncrypted columns

Azure SQL v12 databases have the Always Encrypted feature with ability to store cryptographic materials in Azure Key Vault.

In this Blog I'll show how to create a Key Vault and configure an Azure SQL database to use the Key Vault for Always Encrypted.

The following are the requirements to succeed:

• Azure subscription with access to the Classic Azure Portal (CSP type subscription doesn't work at the time of the writing)
• an Azure SQL v12 database (you can create it from PowerShell but I have it already created)
• Resource Group (you can create it from PowerShell but I also have it already created)

Step 1 - log into Azure using the Resource Model

$cred = Get-Credential

# sign in using the ARM model
Login-AzureRmAccount -Credential $cred

# Login-AzureRmAccount returns this details:

# Environment           : <name>
# Account               : <your email>
# TenantId              : 4f..................................
# SubscriptionId        : fc..................................
# SubscriptionName      : <your subscription name>
# CurrentStorageAccount :


Step 2 - Create a new Key Vault in a resource group

# set your variables
$rg = 'rg-BVI' # resource group name
$location='East US' # loction where Key Vault to be created
$vaultName = 'bvi-poc' #vault name
$vaultKeyName = "poc-master-key" #Key name
$SubscriptionId = "fc.........................."

# Sets the context for the below cmdlets to the specified subscription.
$azureCtx = Set-AzureRMConteXt -SubscriptionId $SubscriptionId 

# create Key Vault
New-AzureRmKeyVault -VaultName $vaultName `
 -ResourceGroupName $rg -Location $location

The last cmdlet returns a number of properties but the one we need to remember is

Vault URI                        : https://bvi-poc.vault.azure.net

this value should show up in SQL master key metadata (if everything is configured propely)


Step 3 - Create your master encryption key in the Key Vault

# Grant access to the vault to your account with all permisisons

Set-AzureRmKeyVaultAccessPolicy `
-VaultName $vaultName `
-ResourceGroupName $rg `
-PermissionsToKeys get, create, delete, list, update, `
import, backup, restore, wrapKey,unwrapKey, sign, `
verify -UserPrincipalName $azureCtx.Account

# create your key (to be used as Master Encryption Key for Always Encrypted) in the vault. 
# If you need to be a FIPS compliant than change the destionation from Software to Hardware

$VaultKey = Add-AzureKeyVaultKey -VaultName $vaultName `
-Name $vaultKeyName `
-Destination "Software"

Step 4 - Configure Azure SQL DB to use the Key Vault created earlier

# Import the SqlServer module.

Import-Module "SqlServer"

$serverName = "<your server name>.database.windows.net"

$databaseName = "PoC01"  # your test DB name

$connStr = "Server = " + $serverName + "; Database = " + $databaseName + "; User ID=<user>;Password=<password>"

$connection = New-Object Microsoft.SqlServer.Management.Common.ServerConnection

$connection.ConnectionString = $connStr

$connection.Connect()

$server = New-Object Microsoft.SqlServer.Management.Smo.Server($connection)

$database = $server.Databases[$databaseName] 

# Create a SqlColumnMasterKeySettings object for your column master key. 

$cmkSettings = New-SqlAzureKeyVaultColumnMasterKeySettings -KeyURL $VaultKey.ID

# Create column master key metadata in the database.

$cmkName = "PoC01-CMK"

New-SqlColumnMasterKey -Name $cmkName -InputObject $database -ColumnMasterKeySettings $cmkSettings

To verify that the master encryption key metadata configured properly execute this query against your database

select * from sys.column_master_keys

it should return one row per master key and these columns must be:

name                        PoC01-CMK

key_store_provider_name     AZURE_KEY_VAULT

key_path                    https://bvi-poc.vault.azure.net/...

The key_path value should match to the value of the 'Vault URI' property returned by the last cmdlet in step 2.

Step 5- create column encryption key in the Azure database

# Authenticate to Azure 

Add-SqlAzureAuthenticationContext -Interactive

# use the same credentials as in Step 1

# Generate a column encryption key, encrypt it with the column master key and create column encryption key metadata in the database.

$cekName = "PoC01-CEK"

New-SqlColumnEncryptionKey -Name $cekName `

-InputObject $database -ColumnMasterKey $cmkName

Step 6 - validate the Key Vault configuration from SSMS

In SSMS connect to your test database and create this table

CREATE TABLE [dbo].[Person](

[PersonId] [uniqueidentifier] NOT NULL PRIMARY KEY,

[Firstname] [nvarchar](32) COLLATE  Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [PoC01-CEK],  

        ENCRYPTION_TYPE = RANDOMIZED,  

        ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'),

[Lastname] [nvarchar](32) COLLATE  Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [PoC01-CEK],  

        ENCRYPTION_TYPE = RANDOMIZED,  

        ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'),

[DateOfBirth] [datetime2](7)  ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [PoC01-CEK],  

        ENCRYPTION_TYPE = RANDOMIZED,  

        ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'),

[AdditionalInfo] [nvarchar](250) NULL

)

GO

If in SSMS you right-click on the dbo.person table and select 'Encrypt Columns' in the dialog window you should see that the Firstname, Lastname and DateOfBirth columns already marked as encrypted and the PoC01-CEK is used as a column encryption key

Step 7 - create Azure Application in the Classic Portal

For a client application to access the Key Vault a permissions need to be granted to a security principal (and a secret key needs to be used by the security principal when connecting to the Key Vault).

The security principal gets created when an application is created in Azure AD.

At the time of the writing creating an Application in the new Azure Portal has a bug (it doesn't create a security principal when registering an application in Azure AD). That's why an application need to be registered in the classic Azure Portal.

In the classic portal go to Active Directory -> Applications

and click "ADD" at the bottom of the screen. Then click 'Add an application my organization is developing'

Give the application a name on the next screen

Enter arbitrary sing-on and App ID URI (they can be changed later if needed)

When the application been registered we need to get its client ID and a secret.

Go to the Active Directory -> Applications -> TestKeyVault -> Configuration

scroll down to the client ID and the keys sections.

Copy client Id and under the keys select 1-year or 2-year duration

The secret will be displayed when the configuration is saved (and only for that moment. Do not miss it!)

Step 8 - grant the Client ID permissions on the Key Vault

Now to grant the permissions just execute this cmdlet

$clientid='e9f0f3ec-be3d-4bb0-9a29-eec6bc7cc330'  # appId

Set-AzureRmKeyVaultAccessPolicy  `

 -VaultName $vaultName `

 -ResourceGroupName $rg `

 -ServicePrincipalName `$clientid `

 -PermissionsToKeys get,wrapKey,unwrapKey,sign,verify

And now the client component needs to be developed in accordance with the patterns presented here.

Good luck!