Visual Studio Database Project and Always Encrypted
Tags: sql, security, visualstudio
Published: 2022-08-07 10:04:50

We can use [Always Encrypted](https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine?view=azuresqldb-current) 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](https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/configure-always-encrypted-using-sql-server-management-studio?view=azuresqldb-current)
- [Configure Always Encrypted using PowerShell](https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/configure-always-encrypted-using-powershell?view=azuresqldb-current)

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](https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/configure-always-encrypted-keys-using-powershell?view=azuresqldb-current#azure-key-vault-without-role-separation-example) 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.

```powershell
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.

![Azure Key Vault key](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/f6hj3ekj78rlasob9nme.png)

### 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".

![Column Encryption Key](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/kl9n2khbit086jx4qs5u.png)

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

![Create Keys](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/x68lz8ej2w7xibxvu09v.png)
 
4\. Set Key Vault information to master key.

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

```sql
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.

```powershell
$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.

![Column Encryption Key](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qzyv7bq79ds158646mew.png)
 
## 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)](https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql?view=azuresqldb-current) for more detail.

![Create Table](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/9xb8ljdorx137bkjc4lu.png)

2\. Publish from Visual Studio to any database.

![Publish result](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/okvqr3sz99h12bpwzmh4.png) 
 
## Confirm the result

We can run query to confirm how database is configured.

```sql
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. 

![Enable Always Encrypted](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/yns6cy77l1f0cdxz0fve.png)

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

![Query Options](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/k72d4cbq1wa0pb8jrb0u.png)
 
3\. Run following query to test.

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

SELECT * FROM [User]
```
![Query result](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/jdrq7m00nzw6z3k72kl4.png)

## 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](https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/rotate-always-encrypted-keys-using-powershell?view=azuresqldb-current) for more detail.