Creating Custom Key Store Providers for Always Encrypted (Azure Key Vault Example)
Updates:
- The syntax for column master keys have been updated. Please refer to https://blogs.msdn.com/b/sqlsecurity/archive/2015/10/28/new-enhancements-in-always-encrypted.aspx for details on what is new in Always Encrypted.
- We have updated the schema for our sample table to follow best practices. Big thanks to Denny Cherry for his feedback and help on greatly improving our sample schema.
- The Azure Key Vault Provider for Always Encrypted is available as a nuget package @ https://www.nuget.org/packages/Microsoft.SqlServer.Management.AlwaysEncrypted.AzureKeyVaultProvider/ . The sample code in this article can still be used as a sample on how to create a custom Always Encrypted Provider.
Always Encrypted provides an extensibility mechanism that enables storing column master keys in an arbitrary key store. To integrate Always Encrypted with a key store of your choice, you need to implement a custom key store provider that encapsulates your store.
In this article, we demonstrate how to implement custom key store providers by showing an example of a provider for Azure Key Vault, which is an Azure service designed to safeguard cryptographic keys and other secrets used by cloud apps and services.
For general information about the notion of the key store provider and how it is used during query execution on the client side, please refer to our previous article: Always Encrypted Key Metadata.
BTW. The article includes a copy of the project and all the code (except NuGet packages) as an attachment at the end.
Step 1 - Azure Key Vault Setup
a) Creating a Vault
We will not dive in-depth on this topic, instead we would strongly recommend reading the following article: Get started with Azure Key Vault. We will only remark a few key elements here:
Using Azure Powershell create a key vault:
New-AzureKeyVault -VaultName 'AEDemoKeyVault' -ResourceGroupName 'AEDemoResourceGroup' -Location 'West US'
b) Creating a Key
Then we create a key that will act as our column master key:
$key = Add-AzureKeyVaultKey -VaultName 'AEDemoKeyVault' -Name 'AEDemoColumnMasterKey' -Destination 'Software'
Then, we will record the newly created key identity as we will use it for our application. The easiest way to get this identity is by simply looking at the $key value. Alternatively, at any given time after the key creation, you can issue the following command:
Get-AzureKeyVaultKey -VaultName 'AEDemoKeyVault' -Name 'AEDemoColumnMasterKey'
The output will look something like this (highlighted HTTPS URL):
c) Granting permissions to the application
In the next step, we need to create an identity for our database client application, when then we will use to grant the application access to the key, we created in the previous step.
To create an application’s identity in Azure, we use the Azure portal, go to Azure Active Directory and create an application:
And create a new application key for this application. This key will be later used by your custom provider to authenticate and access the key in the key vault that we just created.
When creating the new key, do not worry about granting permissions on this interface, as we will grant permissions using Powershell later. Please notice that the keys can have an expiration date. In this case I created a key that will expire in 1 year.
d) Grant the application permission to access the key
Next, we will proceed to grant permissions to the application on the key vault. For this we will use Azure Powershell once more. The following command will grant the permissions needed to create a new column encryption key (CEK) protected by a column master key (CMK) stored in azure key vault:
Set-AzureKeyVaultAccessPolicy -VaultName 'AEDemoKeyVault' -ServicePrincipalName <insert_application_Client_ID> -PermissionsToKeys get,unwrapKey,wrapKey,verify,sign
Please notice that this command grants the following permissions on the key vault:
- get - this permission is necessary to get the keys in the vault. It will be used when encrypting a CEK (i.e. calling SqlAzureKeyVaultProvider.EncryptColumnEncryptionKey) and data access (column data encryption/decryption).
- unwrapKey - This permission is necessary to recover the CEK in plaintext. It will be used when decrypting a CEK (i.e. calling SqlAzureKeyVaultProvider.EncryptColumnEncryptionKey) and data access (column data encryption/decryption).
- wrapKey - This permission is necessary to encrypt the CEK. It will be used only when encrypting a CEK (i.e. calling SqlAzureKeyVaultProvider.EncryptColumnEncryptionKey)
- verify - This permission allows to verify signatures created by the key in key vault. It will be used when verifying that a CEK has not been tampered (i.e. calling SqlAzureKeyVaultProvider.EncryptColumnEncryptionKey) and data access (column data encryption/decryption).
- Sign - This permission allows to sign using the private key in key vault. It will be used only when signing a CEK (i.e. calling SqlAzureKeyVaultProvider.EncryptColumnEncryptionKey)
As you may have noticed, there are some permissions needed only during the CEK creation and not during normal operations. Assuming you will have separate applications for key management and normal operations, you can choose to grant different permissions to each application. For example:
# Granting permissions to the key management app
Set-AzureKeyVaultAccessPolicy -VaultName 'AEDemoKeyVault' -ServicePrincipalName <insert_key_mngmt_app_Client_ID> -PermissionsToKeys get,unwrapKey,wrapKey,verify,sign
# Granting permissions to the data access app
Set-AzureKeyVaultAccessPolicy -VaultName 'AEDemoKeyVault' -ServicePrincipalName <insert_data_access_app_Client_ID> -PermissionsToKeys get,unwrapKey,verify
Step 2 - Writing the Custom Column Master Key Provider
a) Setup your Visual Studio Project and import relevant packages
For all this clinic, I will be using Visual Studio 2015, which includes the minimum version of the .Net framework we will require (4.6). We will start with a new C# Class Library.The reason why we will have a separate project for the custom key vault provider (library) and the sample application is because the library can be later reused in any number of application as a library, while this sample app is designed to be throw-away code.
Next, go to the solution references, and select “Manage NuGet Packages”, we will need to get the Microsoft Azure Key Vault package:
Search for the latest Microsoft.Azure.Keyvault project and install it to add it to your solution. This should also take care of all the required dependencies:
Next, make sure that the project .Net Framework is set to 4.6 (under properties):
b) Implement the key encryption/decryption code
We will create a new class that will implement SqlColumnEncryptionKeyStoreProvider. To get more details on this class, please visit the Always Encrypted (client development) documentation.
The Azure Key vault code is included as an attachment, and while you can use it as-is, I would strongly recommend reading through the sample code. The team who worked on this provider added a lot of comments to make it easier to understand, and I will describe on detail some of the most important details.
This code is an early version of the Azure Key Vault provider we will ship as part of SQL Server Always Encrypted release, so the sample code for the column master key provider is intended to be a pre-release. For this reason, we will appreciate any feedback on the provider code we include in this demo.
The name we will use for this provider is “AZURE_KEY_VAULT_PROVIDER”, we will use the same string when registering the provider to System.Data.SqlClient.SqlConnection on the application and when creating column master key objects in SQL Server.
public const string ProviderName = "AZURE_KEY_VAULT_PROVIDER";
For the implementation of the EncryptColumnEncryptionKey function, there are a few important aspects we need to describe:
public override byte[] EncryptColumnEncryptionKey(string masterKeyPath, string encryptionAlgorithm, byte[] columnEncryptionKey)
This function will return the protected column encryption key value, which consists in more than just an encrypted symmetric key. The full column encryption key value will consist of the following elements concatenated:
- Version: One byte (0x01). This is just a value indicating the current version of the CEK value. Why do we need it? If in the future we need to change algorithms or any other aspect of the envelope, we want to be able to distinguish older versions of CEK values in order to handle them correctly and avoid data loss.
- Key Path & Key Path Length: These two elements describe the CMK path, which is the metadata needed to identify the column master key. In the case of our custom provider, it will be the Azure Key Vault ID (URL).
- Ciphertext & Ciphertext Length: These two elements are the actual CEK key wrapped by the Azure Key Vault asymmetric key (RSA) using OAEP.
- Signature: This is a signature of all the previous elements (in the right order) also using the Azure Key Vault. The Hashing algorithm we are going to be using is defined for Azure Key Vault as RS256, which is a JWT cryptographic algorithm identifier for “RSA using SHA 256”.
These elements will be concatenated in the following order to create the final column encryption key value:
version + keyPathLength + ciphertextLength + ciphertext + keyPath + signature
The DecryptColumnEncryptionKey function is pretty much the inverse operation.
public override byte[] DecryptColumnEncryptionKey(string masterKeyPath, string encryptionAlgorithm, byte[] encryptedColumnEncryptionKey)
As you would expect, it verifies the version & signature before unwrapping the column encryption key and returning the plaintext key value to the calling component, which typically would be the ADO .Net driver.
The rest of the code is mostly parameter and consistency checks, so for we will skip a detailed analysis on it, but please feel free to ask any question you may have on the comments section.
c) Providing an interface for Azure Key Vault authentication
Finally, let’s take a look at the constructor of the provider class.
/// <summary>
/// Constructor that takes a callback function to authenticate to AAD. This is used by KeyVaultClient at runtime
/// to authenticate to Azure Key Vault.
/// </summary>
/// <param name="authenticationCallback">Callback function used for authenticating to AAD.</param>
public SqlColumnEncryptionAzureKeyVaultProvider(KeyVaultClient.AuthenticationCallback authenticationCallback)
{
if (authenticationCallback == null)
{
throw new ArgumentNullException("authenticationCallback");
}
KeyVaultClient = new KeyVaultClient(authenticationCallback);
}
As you can see we take a KeyVaultClient.AuthenticationCallback delegate. The idea for taking this approach is that the application itself will implement a function to authenticate with Azure Key Vault. That way the provider itself is providing flexibility to the application on how authentication will be performed.
Step 4 – Setting up the keys in the database
a) Write code for the key setup
You may be wondering why do you need to write code for setting up the keys on the database, after all, the keys in plaintext are never available to SQL Server itself. Correct?
The reason is because we need to generate a new column encryption key (CEK) and encrypt it using our custom provider. Unfortunately, SSMS has no ability to use a custom provider, so we will have to write our own code for this purpose.
We will write a separate project (an executable) for this purpose as we will use the same project also for data access on the next step; although in production you may also want to consider the alternative of using Powershell.
What we will do is create a new command line project on our Visual Studio solution.
We will need to make it use .Net Framework 4.6 and install the Microsoft Azure Key Vault NuGet Package to the project references (just like we did on the provider); additionally we will also install the latest Microsoft.IdentityModel.Clients.ActiveDirectory package .
And finally, we will add a reference to the custom column master key provider we are creating:
Add the following using directives to import the following namespaces to the application:
using SqlAzureKeyVaultProvider; // Our custom CMK provider
using Microsoft.IdentityModel.Clients.ActiveDirectory;
using Microsoft.Azure.KeyVault;
using Microsoft.Azure.KeyVault.WebKey;
b) Implement the AuthenticationCallback function
As we mentioned on step 3.c we will need to define in our application is the AuthenticationCallback function that we will use in order to use our application client ID and key for Azure Key Vault authentication.
In our case we want to use the application key that we generated, so we will have to pass the client ID & the application key to our implementation of the callback function:
public async static Task<string> GetToken(string authority, string resource, string scope)
{
var authContext = new AuthenticationContext(authority);
ClientCredential clientCred = new ClientCredential(_applicationClientID, _applicationKey);
AuthenticationResult result = await authContext.AcquireTokenAsync(resource, clientCred);
if (result == null)
throw new InvalidOperationException("Failed to obtain the JWT token");
return result.AccessToken;
}
The arguments for the new ClientCredential(_applicationClientID, _applicationKey); are the application Client ID and the application key that we created in the AAD portal.
c) Register the Provider
Registering the custom provider to System.Data.SqlClient.SqlConnection is necessary in order to enable .Net framework (in this case System.Data.SqlClient) to use the custom provider if the column master key metadata metadata indicates that a particular key is encrypted using this name.
In order to accomplish this task, the string used as a key mapped to our custom provider in the call to SqlConnection.RegisterColumnEncryptionKeyStoreProviders must match exactly the argument KEY_STORE_PROVIDER_NAME in the CREATE COLUMN MASTER KEY DDL in T-SQL (step 4.d).
For this task, the first thing we will do is initialize an instance of our provider, using the AuthenticationCallback function we defined earlier (GetToken) that our provider will use to authenticate to Azure SQL Vault.
SqlColumnEncryptionAzureKeyVaultProvider akvprov = new SqlColumnEncryptionAzureKeyVaultProvider(
new KeyVaultClient.AuthenticationCallback(GetToken));
Next we will add our provider instance to the list of providers in SqlConnection:
Dictionary<string, SqlColumnEncryptionKeyStoreProvider> providers = new Dictionary<string, SqlColumnEncryptionKeyStoreProvider>();
providers.Add(@"AZURE_KEY_VAULT_PROVIDER", akvprov);
SqlConnection.RegisterColumnEncryptionKeyStoreProviders(providers);
Once our custom provider is registered, we can proceed setting up keys in the database, but the same steps will also be needed for the application accessing data.
d) Creating a column master key referencing the key in Azure Key Vault
For this demo, I have included the column master key & the table creation within our code, but these two operations can also be performed using any SQL client.
This step could easily be done using only the following T-SQL:
CREATE COLUMN MASTER KEY [<incert_cmk_name_here>]
WITH ( KEY_STORE_PROVIDER_NAME = 'AZURE_KEY_VAULT_PROVIDER',
KEY_PATH = '<insert_azure_key_vault_key_id_here>'
For example:
CREATE COLUMN MASTER KEY [AKV_CMK1]
WITH ( KEY_STORE_PROVIDER_NAME = 'AZURE_KEY_VAULT_PROVIDER',
KEY_PATH = 'https://aedemokeyvault.vault.azure.net:443/keys/AEDemoColumnMasterKey/22200cef526347eca4998fa39daa63b1'
For simplicity on this demo, we have combined this task with the setup portion of the sample application, calling the same T-SQL statement programmatically constructed within our program:
internal const string _createColumnMasterKeyTemplate = @"
CREATE COLUMN MASTER KEY [{0}]
WITH ( KEY_STORE_PROVIDER_NAME = 'AZURE_KEY_VAULT_PROVIDER',
KEY_PATH = '{1}');";
…
[SuppressMessage("Microsoft.Security", "CA2100", Justification = "The SqlCommand text is issuing a DDL statement that requires to use only literals (no parameterization is possible). The user input is being escaped.", Scope = "method")]
static void CreateColumnMasterKey(SqlConnection sqlcon, string cmkName, string keyID)
{
SqlCommand sqlcmd = sqlcon.CreateCommand();
// Prevent SQL injections by escaping the user-defined tokens
sqlcmd.CommandText = string.Format(_createColumnMasterKeyTemplate,
cmkName.Replace("]", "]]"), keyID.Replace(@"'", @"''"));
sqlcmd.ExecuteNonQuery();
}
Notice that because the operations performed on this code are DDL operations, it is not possible to parameterize the statement, so I have to escape the object names in order to avoid SQL injection.
e) Create a column encryption key
In order to create a column encryption key protected by an Azure Key Vault-based column master key, we will need to generate an encrypted column encryption key value:
// Generate the raw bytes that will be used as a key by using a CSPRNG
byte[] cekRawValue = new byte[32];
RandomNumberGenerator csprng = new RNGCryptoServiceProvider();
csprng.GetBytes(cekRawValue);
byte[] cekEncryptedValue = akvprov.EncryptColumnEncryptionKey(KeyID, @"RSA_OAEP", cekRawValue);
Once we have this value, we will be able to use it in the CREATE COLUMN ENCRYPTION KEY DDL statement as the encrypted value using the following syntax:
CREATE COLUMN ENCRYPTION KEY [<insert_cek_name>]
WITH VALUES
(
COLUMN_MASTER_KEY = [insert_cmk_name],
ALGORITHM = 'RSA_OAEP',
ENCRYPTED_VALUE = <insert_encrypted_cek_value>
)
For example:
CREATE COLUMN ENCRYPTION KEY [AKV_CEK1]
WITH VALUES
(
COLUMN_MASTER_KEY = [AKV_CMK1],
ALGORITHM = 'RSA_OAEP',
ENCRYPTED_VALUE = 0x01CC00...68C60
)
GO
In the case of our sample code, we also issue this DDL statement programmatically. Please notice that the bytes we generated to act as our column encryption key were generated using a cryptographically secure random number generator. Also notice that because this is a DDL statement, the parameters cannot be parameterized and need to be properly escaped.
internal const string _createColumnEncryptionKeyTemplate = @"
CREATE COLUMN ENCRYPTION KEY [{0}]
WITH VALUES
(
COLUMN_MASTER_KEY = [{1}],
ALGORITHM = 'RSA_OAEP',
ENCRYPTED_VALUE = {2}
);";
…
[SuppressMessage("Microsoft.Security", "CA2100", Justification = "The SqlCommand text is issuing a DDL statement that requires to use only literals (no parameterization is possible). The user input is being escaped.", Scope = "method")]
static void CreateColumnEncryptionKey(SqlConnection sqlcon, string cekName, string cmkName, string KeyID, ref SqlColumnEncryptionAzureKeyVaultProvider akvprov)
{
// Generate the raw bytes that will be used as a key by using a CSPRNG
byte[] cekRawValue = new byte[32];
RandomNumberGenerator csprng = new RNGCryptoServiceProvider();
csprng.GetBytes(cekRawValue);
byte[] cekEncryptedValue = akvprov.EncryptColumnEncryptionKey(KeyID, @"RSA_OAEP", cekRawValue);
SqlCommand sqlcmd = sqlcon.CreateCommand();
// Prevent SQL injections by escaping the user-defined tokens
sqlcmd.CommandText = string.Format(_createColumnEncryptionKeyTemplate,
cekName.Replace("]", "]]"), cmkName.Replace("]", "]]"), BytesToHex(cekEncryptedValue));
sqlcmd.ExecuteNonQuery();
}
As you can see, we will use our custom provider instance explicitly to wrap the key by calling EncryptColumnEncryptionKey(keyID, @"RSA_OAEP", cekRawValue); on it. The KeyID parameter is the Azure Key Vault key ID (should look like an HTTPS URL).
f) Create a Schema Using the CEK
Just like the create column master key DDL statement, creating the schema can be done using the same syntax we have used in previous articles about Always encrypted.
For this example, we will use the following syntax:
CREATE TABLE [dbo].[Test](
[PatientId] [int] IDENTITY(1,1) NOT NULL,
[SSN] [char](11) COLLATE Latin1_General_BIN2
ENCRYPTED WITH (
COLUMN_ENCRYPTION_KEY = [AKV_CEK1],
ENCRYPTION_TYPE = Deterministic,
ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
) NOT NULL,
PRIMARY KEY CLUSTERED
(
[PatientId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
In the sample code we included code to generate this statement programmatically for the convenience of this clinic, but feel free to create your own schema to experiment.
Step 5 – Develop the database access application
Typically you would have the key setup and the application as two different binaries, but for simplicity of this clinic, we will reuse the same project.
For the data access part of the code, the pattern should be familiar as it is the same that we have used before for AlwaysEncrypted applications.
The one difference is that you will have to register the custom column master key provider as described in step 4.c.
It is important to notice that on the data access application, there is no indication that we are using a custom column master key provider for this table. The .Net driver will take care of finding the right provider (after it was registered) based on the table & key metadata.
static void InsertData(SqlConnection sqlcon, string ssn)
{
SqlCommand sqlcmd = sqlcon.CreateCommand();
// Prevent SQL injections by escaping the user-defined tokens
sqlcmd.CommandText = @"INSERT INTO [dbo].[Test] VALUES ( @ssn )";
SqlParameter paramSsn = sqlcmd.CreateParameter();
paramSsn.DbType = System.Data.DbType.AnsiStringFixedLength;
paramSsn.Size = 11;
paramSsn.Direction = System.Data.ParameterDirection.Input;
paramSsn.ParameterName = @"@ssn";
paramSsn.SqlValue = ssn;
sqlcmd.Parameters.Add(paramSsn);
sqlcmd.ExecuteNonQuery();
}
Step 6 – Running the tool for key setup
We will start calling our sample application with no arguments to figure out what do we need to do to call the application.
Usage: SqlKeyVaultSampleApp -i <application client ID> -s <application client Key> -c <connection string> -k <Azure Key Vault Key ID> -o <setup|data_access>
Because we want to run setup steps at this point, we will call with the following parameters:
SqlKeyVaultSampleApp.exe -i 200df1c9-87e9-45fb-8ab9-a259e5f17e30 -s 6WfgiYH74deh+u3fy426+38fkl347d3yd623F53h5+A= -c "Data Source=mySqlServer;Initial Catalog=db_test;Integrated Security=True;Column Encryption Setting=Enabled" -k "https://aedemokeyvault.vault.azure.net:443/keys/AEDemoColumnMasterKey/22200cef526347eca4998fa39daa63b1" -o setup
Where:
- 200df1c9-87e9-45fb-8ab9-a259e5f17e30 - Our Application Client ID, we will need this for authenticating to Azure Key Vault
- 6WfgiYH74deh+u3fy426+38fkl347d3yd623F53h5+A= – Our application Key, also needed for authenticating to Azure Key Vault
- "Data Source=mySqlServer;Initial Catalog=db_test;Integrated Security=True;Column Encryption Setting=Enabled"- This is the connection string to connect to the database where we will create the keys and the schema.
- "https://aedemokeyvault.vault.azure.net:443/keys/AEDemoColumnMasterKey/22200cef526347eca4998fa39daa63b1" – This is the Azure Key Vault ID
- setup – Finally, this parameter will simply indicate that this call to the application will only execute the key and schema setup steps.
The end result of this execution will be that the application will first authenticate to Azure Key Vault, then create the column master key:
CREATE COLUMN MASTER KEY [AKV_CMK1]
WITH ( KEY_STORE_PROVIDER_NAME = 'AZURE_KEY_VAULT_PROVIDER',
KEY_PATH = 'https://aedemokeyvault.vault.azure.net:443/keys/AEDemoColumnMasterKey/22200cef526347eca4998fa39daa63b1'
Crate a new random key, encrypt it with the custom provider using the key stored in Azure Key Vault:
// Generate the raw bytes that will be used as a key by using a CSPRNG
byte[] cekRawValue = new byte[32];
RandomNumberGenerator csprng = new RNGCryptoServiceProvider();
csprng.GetBytes(cekRawValue);
// Encrypt the newly created random key using the AKV provider
byte[] cekEncryptedValue = akvprov.EncryptColumnEncryptionKey(KeyID, @"RSA_OAEP", cekRawValue);
The use the encrypted value to create the column encryption key:
CREATE COLUMN ENCRYPTION KEY [AKV_CEK1]
WITH VALUES
(
COLUMN_MASTER_KEY = [AKV_CMK1],
ALGORITHM = 'RSA_OAEP',
ENCRYPTED_VALUE = 0x01CC00...68C60
)
GO
And finally create the schema we will use in our application:
CREATE TABLE [dbo].[Test](
[PatientId] [int] IDENTITY(1,1) NOT NULL,
[SSN] [char](11) COLLATE Latin1_General_BIN2
ENCRYPTED WITH (
COLUMN_ENCRYPTION_KEY = [AKV_CEK1],
ENCRYPTION_TYPE = Deterministic,
ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
) NOT NULL,
PRIMARY KEY CLUSTERED
(
[PatientId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Step 7 – Running the tool for data access
In order to perform DML operations using our application, we will call it again using the same parameters for the application that we used in step 6, expect that this time we will use –o data_access instead of –o setup.
SqlKeyVaultSampleApp.exe -i 200df1c9-87e9-45fb-8ab9-a259e5f17e30 -s 6WfgiYH74deh+u3fy426+38fkl347d3yd623F53h5+A= -c "Data Source=mySqlServer;Initial Catalog=db_test;Integrated Security=True;Column Encryption Setting=Enabled" -k "https://aedemokeyvault.vault.azure.net:443/keys/AEDemoColumnMasterKey/22200cef526347eca4998fa39daa63b1" -o data_access
This will take us to part of our application that handles the data access. Here, before accessing data, we will need to register our instance of the custom provider to SqlClient:
// In case we will access data, we need to register the instance of our custom provider to SqlConnection
//
Dictionary<string, SqlColumnEncryptionKeyStoreProvider> providers = new Dictionary<string, SqlColumnEncryptionKeyStoreProvider>();
// "AZURE_KEY_VAULT_PROVIDER" is the name of the provider. It must match the string we used when we created the column master key
providers.Add(CUSTOM_AKV_PROVIDER_NAME, akvprov);
SqlConnection.RegisterColumnEncryptionKeyStoreProviders(providers);
After this step, the usage of Always Encrypted is exactly the same as it was before; for example, a simple SELECT * query and for inserting data we simply have to use parameterized SQL statements:
static void SelectData(SqlConnection sqlcon)
{
SqlCommand sqlcmd = sqlcon.CreateCommand();
// Prevent SQL injections by escaping the user-defined tokens
sqlcmd.CommandText = @"SELECT * FROM [dbo].[Test]";
SqlDataReader reader = sqlcmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
System.Console.WriteLine(@"{0}, {1}", reader[0], reader[1]);
}
}
}
static void InsertData(SqlConnection sqlcon, string ssn)
{
SqlCommand sqlcmd = sqlcon.CreateCommand();
sqlcmd.CommandText = @"INSERT INTO [dbo].[Test] VALUES ( @ssn )";
SqlParameter paramSsn = sqlcmd.CreateParameter();
paramSsn.DbType = System.Data.DbType.AnsiStringFixedLength;
paramSsn.Size = 11;
paramSsn.Direction = System.Data.ParameterDirection.Input;
paramSsn.ParameterName = @"@ssn";
paramSsn.SqlValue = ssn;
sqlcmd.Parameters.Add(paramSsn);
sqlcmd.ExecuteNonQuery();
}
Conclusion
In this clinic we covered the details on how to create a custom column master key provider to be used for Always Encrypted as well as the steps in order to use it. Additionally, as an attachment we include a working code that can be compiled as-is to be used as a custom provider for storing the column master keys in Azure Key Vault.