Configuring Always Encrypted by using Azure Key Vault

  1. Home
  2. Configuring Always Encrypted by using Azure Key Vault

Go back to AZ-500 Tutorials

We will study and understand how to configure Always encrypted using the Azure key vault in this tutorial. Using the Always Encrypted wizard in SQL Server Management Studio, we’ll learn how to safeguard sensitive data in an Azure SQL Database database (SSMS).

Always Encrypted is a data encryption solution that helps safeguard sensitive data on the server while it is in transit. Furthermore, it ensures that sensitive information is never stored in plaintext within the database system. Plaintext data can only be accessed by client programs or app servers that have access to the keys once data encryption has been configured.

After setting the database to use Always Encrypted, you’ll use Visual Studio to construct a client application in C# to deal with the encrypted data.

Enable client application access

You must first activate your client application by creating an Azure Active Directory (Azure AD) application before you can access your database in SQL Database. Following that, copy the Application ID and key you’ll need to authenticate your app.

Creating a key vault to store your keys

It’s time to construct a key vault and define its access policy for accessing the vault’s secrets after creating the client app and obtaining the application ID. To generate a new column master key and set up encryption with SQL Server Management Studio, you’ll need the create, get, list, sign, verify, wrapKey, and unwrapKey permissions.

PowerShell

$subscriptionName = ‘<subscriptionName>’

$userPrincipalName = ‘<[email protected]>’

$applicationId = ‘<applicationId from AAD application>’

$resourceGroupName = ‘<resourceGroupName>’ # use the same resource group name when creating your SQL Database below

$location = ‘<datacenterLocation>’

$vaultName = ‘<vaultName>’

Connect-AzAccount

$subscriptionId = (Get-AzSubscription -SubscriptionName $subscriptionName).Id

Set-AzContext -SubscriptionId $subscriptionId

New-AzResourceGroup -Name $resourceGroupName -Location $location

New-AzKeyVault -VaultName $vaultName -ResourceGroupName $resourceGroupName -Location $location

Set-AzKeyVaultAccessPolicy -VaultName $vaultName -ResourceGroupName $resourceGroupName -PermissionsToKeys create,get,wrapKey,unwrapKey,sign,verify,list -UserPrincipalName $userPrincipalName

Set-AzKeyVaultAccessPolicy  -VaultName $vaultName  -ResourceGroupName $resourceGroupName -ServicePrincipalName $applicationId -PermissionsToKeys get,wrapKey,unwrapKey,sign,verify,list

Connecting with SSMS

To begin, launch SQL Server Management Studio (SSMS) and connect to the server or database.

  • Firstly, open SSMS and go to Connect  and click database Engine to open the Connect to Server window.
  • Then, enter your server name or instance name and credentials.

And, if the New Firewall Rule window opens, sign in to Azure and let SSMS create a new firewall rule for you.

AZ-500 Practice tests

Configuring Always Encrypted

Always Encrypted may be configured with the aid of SSMS by setting up the column master key, column encryption key, and encrypted columns.

  • Firstly, expand Databases > Clinic > Tables.
  • Then, right-click the Patients table and select Encrypt Columns to open the Always Encrypted wizard:
configuring always encrypted
Image Source: Microsoft

Creating client application that works with the encrypted data

You can create an application that performs inserts and selects on the encrypted columns after you’ve set up Always Encrypted.

  • Firstly, open Visual Studio and create a new C# Console Application (Visual Studio 2015 and earlier) or Console App (.NET Framework) (Visual Studio 2017 and later). 
  • Secondly, name the project AlwaysEncryptedConsoleAKVApp and click OK.
  • Lastly, install the following NuGet packages by going to Tools > NuGet Package Manager > Package Manager Console.

After that, run the below two lines of code in the Package Manager Console:

PowerShell

Install-Package Microsoft.SqlServer.Management.AlwaysEncrypted.AzureKeyVaultProvider

Install-Package Microsoft.IdentityModel.Clients.ActiveDirectory

Modifying your connection string to enable Always Encrypted

Add the Column Encryption Setting keyword to your connection string and set it to Enabled to enable Always Encrypted.

However, you may specify this either directly in the connection string or through SqlConnectionStringBuilder.

Enabling Always Encrypted in the connection string

Add the following keyword to your connection string.

Column Encryption Setting=Enabled

Registering the Azure Key Vault provider

The code below shows how to register the Azure Key Vault provider with the ADO.NET driver.

C#

private static ClientCredential _clientCredential;

static void InitializeAzureKeyVaultProvider() {

    _clientCredential = new ClientCredential(applicationId, clientKey);

    SqlColumnEncryptionAzureKeyVaultProvider azureKeyVaultProvider = new SqlColumnEncryptionAzureKeyVaultProvider(GetToken);

    Dictionary<string, SqlColumnEncryptionKeyStoreProvider> providers = new Dictionary<string, SqlColumnEncryptionKeyStoreProvider>();

    providers.Add(SqlColumnEncryptionAzureKeyVaultProvider.ProviderName, azureKeyVaultProvider);

    SqlConnection.RegisterColumnEncryptionKeyStoreProviders(providers);

}

Always Encrypted sample console application

This sample demonstrates how to:

  • Firstly, modify your connection string to enable Always Encrypted.
  • Secondly, register Azure Key Vault as the application’s key store provider.
  • Thirdly, insert data into the encrypted columns.
  • Lastly, select a record by filtering for a specific value in an encrypted column.

Verify that the data is encrypted

You can easily review that the actual data on the server is encrypted by querying the Patients data with SSMS.

Run the following query on the Clinic database.

SQL

SELECT FirstName, LastName, SSN, BirthDate FROM Patients;

To utilize SSMS to access unencrypted data, first make sure the user has the following Azure Key Vault permissions: obtain, unwrapKey, and verify.

After that, add the Column Encryption Setting=enabled parameter during your connection.

  • Firstly, in SSMS, right-click your server in Object Explorer and choose Disconnect.
  • Secondly, click Connect > Database Engine to open the Connect to Server window and click Options.
  • Then, click Additional Connection Parameters and type Column Encryption Setting=enabled.
  • Lastly, run the following query on the Clinic database.

SQL

SELECT FirstName, LastName, SSN, BirthDate FROM Patients;

Az-500 online course

Reference: Microsoft Documentation

Go back to AZ-500 Tutorials

Menu