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.
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.
To use Always Encrypted, we need two keys.
- Column Master Key (Store in Key Vault)
- Column Encryption Key (Store in SQL database)
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.
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.
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.
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]
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.