Configuring Always Encrypted by using Azure Key Vault
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.
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:
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;
Reference: Microsoft Documentation