DEV Community

Kenichiro Nakamura
Kenichiro Nakamura

Posted on

3 2

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.

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"
Enter fullscreen mode Exit fullscreen mode

Confirm a key is added.

Azure Key Vault key

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

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

Create Keys

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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

Column Encryption Key

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.

Create Table

2. Publish from Visual Studio to any database.

Publish result

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'
Enter fullscreen mode Exit fullscreen mode

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

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

Query Options

3. Run following query to test.

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

SELECT * FROM [User]
Enter fullscreen mode Exit fullscreen mode

Query result

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.

Image of Timescale

Timescale – the developer's data platform for modern apps, built on PostgreSQL

Timescale Cloud is PostgreSQL optimized for speed, scale, and performance. Over 3 million IoT, AI, crypto, and dev tool apps are powered by Timescale. Try it free today! No credit card required.

Try free

Top comments (0)

A Workflow Copilot. Tailored to You.

Pieces.app image

Our desktop app, with its intelligent copilot, streamlines coding by generating snippets, extracting code from screenshots, and accelerating problem-solving.

Read the docs

👋 Kindness is contagious

Dive into an ocean of knowledge with this thought-provoking post, revered deeply within the supportive DEV Community. Developers of all levels are welcome to join and enhance our collective intelligence.

Saying a simple "thank you" can brighten someone's day. Share your gratitude in the comments below!

On DEV, sharing ideas eases our path and fortifies our community connections. Found this helpful? Sending a quick thanks to the author can be profoundly valued.

Okay