Secure data easily with Azure SQL Database and Azure Key Vault

Today, it’s a common practice not to store passwords in plain text in your database. Hashing and salting are relatively simple techniques that help you protect the passwords of your users. But with all the privacy and security regulations, you’re responsible for giving your clients the best possible protection.
Today is pretty standard that you have sensitive data in your database like telephone numbers, email addresses, sometimes credit card numbers or even social security numbers.
You can’t leave those plain text in your database. You want those hidden/encrypted for every database user. You want those encrypted. Also in case of a security breach during an attack you want your data to be protected. You cannot let these values fall into the wrong hands. Luckily Azure SQL has a nice feature called Always Encrypted. Let’s check it out!

For this article, I have created a simple application that registers a user with a credit card number and email address. I also have a SQL Server on Azure.
In order to use Always Encrypted you have to make sure that your database server uses the optimized hardware for confidential computing. (yeah, I know its quite expensive)

Once you have correctly set up your database server and database let’s assume now you also have a user table with columns for credit card numbers and telephone.

In case of a security breach, the hackers would be able to access your database and its data, all the sensitive data would be visible to them…. if we would not have encrypted our database.

So let’s start encrypting our credit card number column with Always encrypted.

In order to start using Always Encrypted you have to download and use SQL Management Studio. You can download it here. Download SQL Server Management Studio (SSMS) – SQL Server Management Studio (SSMS) | Microsoft Docs

In SSMS you can select the column you want to encrypt and start the Always Encryption On wizard.

In the first page you can select the Encryption Type.

For the purpose of this demo, I choose Deterministic, but normally I would recommend Randomized for production purposes.
The next screen requires you to choose a location for the encryption keys. Choose Azure Key Vault!
Make sure that your user has the correct access policies on the Azure Key Vault

Click next to start the encryption process. Be careful, this can take a while!

When it’s finished the database values are successfully encrypted, and if we look with SSMS we can see the result.

Now we have our values encrypted inside our database, but our applications need to be able to decrypt this information if the user wants to access its data. Fortunately, this is quite simple. First, make sure that you adjust your connection string and add the following

Column Encryption Setting=enabled; //add this line
Encrypt=True; //add this line... Its very likely that you already have this

Next, we need to add code that:
– will tell our DbContext that we use SQL Encrypted Columns
– access our Azure Key Vault for the right keys for decryption

public class DbSqlContext : DbContext
   {
       public DbSet<User> Users { get; set; }
 
       public DbSqlContext(DbContextOptions<DbSqlContext> options)
       : base(options)
       {
 
           var credential = new DefaultAzureCredential(
               new DefaultAzureCredentialOptions { 
               ManagedIdentityClientId = "<<CLIENT ID OF YOUR MANAGED IDENTITY>>"
               });
 
           var azureKeyVaultProvider = new SqlColumnEncryptionAzureKeyVaultProvider(credential);
           var providers = new Dictionary<string, SqlColumnEncryptionKeyStoreProvider>();
           providers.Add(SqlColumnEncryptionAzureKeyVaultProvider.ProviderName, azureKeyVaultProvider);
            
           SqlConnection.RegisterColumnEncryptionKeyStoreProviders(providers);
 
       }
   }