Always Encrypted in Azure SQL and .NET

Introduction

Always Encrypted is a feature in SQL Server and Azure SQL designed to protect sensitive information in individual database columns. This feature makes encryption transparent in a .NET application when configured correctly and when using the SqlClient for database operations. Using an Azure Key Vault is optional, but highly recommended if the application and database are hosted in Azure. For the purpose of this article, we have a .NET 8 application, an Azure SQL database, and an Azure Key Vault.

Application Setup

Your application will need to be hosted in Azure as a web app. Setting up a web app is beyond the scope of this article, but we will take a look at the one requirement in the web app settings that is required for working with encrypted data in a .NET application using an Azure Key Vault – the application identity. In the Azure portal, go to your web app page and select Settings, then Identity. In the System assigned tab, click on the On button under Status and then the Save button. This will create an application identity in Azure that will be necessary for giving the application rights to encrypt and decrypt the SQL Server data.

Azure Key Vault

Setting up an Azure Key Vault is beyond the scope of this article, but my colleague Jeff Achesinski has a great blog post on setting one up. If you are following best practices and storing your SQL Server connection string in the Key Vault, you’ll need to go to Objects, then Secrets, select your connection string secret, and create a new one (you cannot modify an existing secret) with Column Encryption Setting=enabled appended to the end of your connection string. This is key to letting SQL Server know you have encrypted columns in your database.

Now we need to give our application the rights to encrypt and decrypt the data. If you are already using Key Vault, you will be modifying your existing application access policy, but for the purpose of this article, we’ll demonstrate how to set up a new application. In the Azure portal, go to your Key Vault and select Access policies, then click the Create button. None of the existing templates will work for this scenario, so don’t use one; instead, manually select the permissions as shown below. The key point is to make sure you have selected all under Cryptographic Operations.

In the next step, type in your application’s name. By turning on identity for the application in the previous step, we have allowed the application to be discoverable by other Azure features. Click the Next button, then Next again. Then click on the Create button to create the access policy.

SQL Server Setup

Microsoft provides a great article on getting started with Always Encrypted. I won’t go into the details here, but I’ll just point out a few things. When encrypting the column, your choice of the encryption type should be based on if you are going to be querying on that column. Choose deterministic if you are, otherwise choose randomized. When you get to the Master Key Configuration section, you’ll want to choose Azure Key Vault and you’ll have to sign in. One thing that can trip you up here is that you have to make sure your Azure account has been set up in the Key Vault under Access policies for both Key Management Operations and Cryptographic Operations, otherwise SQL Server won’t be able to store the column master key in the Key Vault.

SQL Server Connection String

Your SQL Server connection string must have Column Encryption Setting=enabled to query and update encrypted columns. Either update the connection string in your appsettings file or, if you are following best practices, update it in the Azure Key Vault. It’s not a bad idea to query your encrypted column at this point to make sure it’s been set up correctly. If you query the encrypted column in SQL Server, you should see byte code in the column instead of text.

If you want SQL Server to decrypt the column, check the Enable Always Encrypted (column encryption) option in the Always Encrypted tab in the connect dialog.

Once you’ve checked that option and connected to the database, query the table with the encrypted column and make sure you are seeing plain text.

.NET Configuration

Now that we have Azure Key Vault and SQL Server configured, we need to configure the .NET application to support Always Encrypted. The first step is to install the Always Encrypted Azure Key Vault Provider NuGet package. This will let the application use Always Encrypted with the .NET Framework data provider for SQL Server. Note that Microsoft.Data.SqlClient is still an option for accessing encrypted data and would require a different Key Vault Provider NuGet package, but it is considered legacy at this point and should not be used. The next step is to configure the Azure Key Vault provider for SQL Server in the Program.cs file as shown below. This will allow the .NET application to identify the Azure Key Vault as the encryption store for encrypting and decrypting the SQL Server columns.

// setup AKV provider for SQL Server Encrypted Columns
var akvProvider = new SqlColumnEncryptionAzureKeyVaultProvider(credential);
SqlConnection.RegisterColumnEncryptionKeyStoreProviders(customProviders: new Dictionary<string,
    SqlColumnEncryptionKeyStoreProvider>(capacity: 1, comparer: StringComparer.OrdinalIgnoreCase)
{
    { SqlColumnEncryptionAzureKeyVaultProvider.ProviderName, akvProvider}
});

Querying and Updating Encrypted Data

We’re still not quite done at this point as Entity Framework does not support Always Encrypted, so we will have to create custom code using the .NET Framework Data Provider for SQL Server to access and update the encrypted data. Microsoft provides some good documentation on this as well with the article Using Always Encrypted with the Microsoft .NET Data Provider for SQL Server.  Some points to take away here are that you can use Always Encrypted with pretty much any version of .NET Framework greater than 4.6 and .NET Core greater than 2.1, so you don’t have to be running the latest version of .NET to take advantage of encrypted data. Another thing to note is that the user account that is set up in your connection string must have a couple of SQL Server permissions in order to get access to the keys needed to encrypt and decrypt the column: view any column encryption key definition and view any column master key definition.

For either querying or updating data, first we will need to get the SQL Server connection string from the Key Vault using this helper method.

private string GetSqlServerConnectionStringFromAzureKeyVault(string KeyVaultUri, string keyVaultConnectionStringSecretName)
{
    // get the SQL server connection string from Azure KeyVault
    var connectionString = "";
    var credential = new DefaultAzureCredential(new DefaultAzureCredentialOptions());            
    var secretClient = new SecretClient(new Uri(KeyVaultUri), credential);
    var azureResponse = secretClient.GetSecret(keyVaultConnectionStringSecretName);
    if (azureResponse.GetRawResponse().Status == 200) // OK
    {
        var keyVaultSecret = azureResponse.Value;
        connectionString = keyVaultSecret.Value ?? "";
    }           
    if (string.IsNullOrEmpty(connectionString))
        throw new Exception("Cannot get SQL Server connection string from Azure KeyVault.");        
    return connectionString;
}

To query encrypted data, we’ll use a SqlDataReader as show below. We open the connection, create a SQL command, then use the SqlDataReader to read the results into a Plain Old CLR Object (POCO) that will contain the unencrypted data, which we can use however we need to in the application.

using (var connection = new SqlConnection(connectionString))
{
    // open the connection
    connection.Open();
    
    // create a SQL Command
    using var cmd = connection.CreateCommand();
    
    // create an input parameter for the query
    var param1 = cmd.CreateParameter();
    param1.ParameterName = @"@StateId";
    param1.DbType = DbType.Int64;
    param1.Direction = ParameterDirection.Input;
    param1.Value = stateId;
    cmd.Parameters.Add(param1);
    cmd.CommandText = sql;                
    
    // run the query and save to a POCO
    var reader = cmd.ExecuteReader();
    while (reader.Read())
    {
        var vr = new VendorRequestList
        {
            VendorRequestId = reader.GetInt64(0),
            InstallerId = reader.GetInt64(2),
            StateId = reader.GetInt64(3),
            VendorRequestTypeId = reader.GetInt64(5),
            Notes = reader.GetString(6),
            CreatedBy = reader.GetString(7),
            CreatedOnUtc = reader.GetDateTime(8),
            CompanyName = reader.GetString(9),
            City = reader.GetString(11),
            Address1 = reader.GetString(12),
            Address2 = reader.IsDBNull(13) ? string.Empty : reader.GetString(13),
            Zipcode = reader.GetString(14)
        };
        vendorRequest.Add(vr);
    }
}

To add or update data in an encrypted column, we will use a SqlCommand. There are a couple things to note when using a SqlCommand. The first is to make sure that the columnEncryptionSetting option is set to Enabled. The other thing that tripped me up was one of my encrypted columns was set up as varchar(MAX) in SQL Server. Setting that up as a SqlCommand parameter caused me a ton of heartburn as it kept trying to set the length of the parameter equal to whatever string I was passing in. That, of course, lead to an operand type clash exception when SQL Server tried to save a varchar(23) column parameter to a varchar(MAX) column in the database. The solution I finally found was to set up the SqlParameter explicitly as a SqlDbType.VarChar with a length of -1.

I created a helper method to create the Command object for both my update and insert methods as shown below.

private SqlCommand CreateSqlCommandForVendorRequest(string sql, SqlConnection sqlConnection,
    SqlTransaction sqlTransaction, VendorRequest vr, string loggedInUserName, long stateId)
{
    var sqlCommand = new SqlCommand(sql, connection: sqlConnection, transaction: sqlTransaction,
                            columnEncryptionSetting: SqlCommandColumnEncryptionSetting.Enabled);
    if (vr.VendorRequestId != null)
        sqlCommand.Parameters.AddWithValue(@"VendorRequestId", vr.VendorRequestId);
    sqlCommand.Parameters.AddWithValue(@"InstallerId", vr.InstallerId);
    sqlCommand.Parameters.AddWithValue(@"VendorRequestTypeId", vr.VendorRequestTypeId);
    sqlCommand.Parameters.AddWithValue(@"StateId", stateId);
    sqlCommand.Parameters.AddWithValue(@"CreatedBy", loggedInUserName);
    sqlCommand.Parameters.AddWithValue(@"CreatedOnUtc", DateTime.UtcNow);
    // notes column is varchar(max) so the parameter has to be specified with type otherwise
    // SQL will think it is a nvarchar (length of the string input) and will throw an Operand type clash exception
    sqlCommand.Parameters.Add(new SqlParameter("Notes", SqlDbType.VarChar, -1) { Value = vr.Notes });            
    return sqlCommand;
}

Using that helper method, I construct the T-SQL UPDATE statement and execute it using the SqlCommand.ExecuteNonQuery method.

public async Task UpdateVendorRequest(VendorRequest vr, string loggedInUserName, long stateId,
    string KeyVaultUri, string keyVaultConnectionStringSecretName)
{
    // create update statement, have to use a SqlCommand because notes column is encrypted
    var connectionString = GetSqlServerConnectionStringFromAzureKeyVault(KeyVaultUri, keyVaultConnectionStringSecretName);
    var sql = "UPDATE [dbo].[VendorRequest]" +
                " SET InstallerId = @InstallerId," +
                " VendorRequestTypeId = @VendorRequestTypeId," +
                " StateId = @StateId," +
                " Notes = @Notes," +
                " CreatedBy = @CreatedBy," +
                " CreatedOnUtc = @CreatedOnUtc" +
                " WHERE VendorRequestId = @VendorRequestId;";
    await using var sqlConnection = new SqlConnection(connectionString);
    sqlConnection.Open();
    await using var sqlTransaction = sqlConnection.BeginTransaction();
    var sqlCommand = CreateSqlCommandForVendorRequest(sql, sqlConnection, sqlTransaction, vr, loggedInUserName, stateId);
    sqlCommand.ExecuteNonQuery();
    sqlTransaction.Commit();
}

For inserting data, I construct a T-SQL INSERT statement and execute it using the SqlCommand.ExecuteScalar method because, typically, when inserting a new record, we need the identity value of the record we just inserted. ExecuteScalar will return an integer value based on our query. Note that the T-SQL command OUTPUT INSERTED.VendorRequestId will return that value to the vrId variable after the INSERT was successful.

Conclusion

Sensitive information should always be protected as much as possible when stored in an application’s database. The decision on what to protect and how much to protect is up to the developer. A general rule of thumb is that any personally identifiable information (PII) should get a high level of protection; Always Encrypted is a great choice for this task, especially if you are already leveraging Microsoft Azure. The ability to encrypt individual columns rather than the entire database helps keep the database performance high while protecting that data.