Implement end-to-end Encryption in Azure SQL to meet HIPAA compliance

Implement end-to-end Encryption in Azure SQL to meet HIPAA compliance

With modern transaction-driven applications, data matters to drive the business. With the amount of data growth, it is most important to make sure the data securely capture, transferred, and stored. Patient health data and data protection are critical to any healthcare applications to meet compliance and security standards  in the health care industry.

There are several ways to secure the data in the database. And in this article, we are going to look at data encryption. Specifically, a method called “Always Encrypted” in Azure SQL Database using Azure Key Vault.

What is Always Encrypted?

        Always encrypted is a feature designed to protect sensitive data in the database offered by Microsoft from SQL Server 2016 onwards and in Azure SQL Database.

The main advantage of Always Encrypted is that we can encrypt the data at the column level. That is the main difference from Transparent Data Encryption. Another advantage is that it provides a separation between those who own the data and view it, those who manage the data but have no access. So, this protects the data from high-privileged unauthorized users like database administrators, cloud database operators, and other DBA staff.

With Always Encrypted, all actual data encryption and decryption is handled by the client application, which runs outside the SQL server environment. Initially, to implement the Always Encrypted on a column, you must generate the below two keys inside of the database.

1. Column Master Key – This encrypts the Column Encryption Key. The database engine stores only metadata that points to the key’s location. The actual master key gets saved to a trusted external source such as the Azure Key Vault and Windows certificate store.

2. Column Encryption Key – This key encrypts the column data. The column encryption key is stored on the SQL server instance by the database engine where Always Encrypted is implemented.

Now let’s see the step-by-step configuration to implement the Always Encrypted in Azure SQL database using Azure Key Vault.
        Before we get started, the prerequisites to configure Always Encrypt is the Azure SQL database. If you want to know how to create an Azure SQL database, click here and go through this link to create the Azure SQL database.

Step: 1
Log in to the Azure portal. Click "more services", you will find the Key vaults under the Security option in the navigation pane.

Step: 2
On clicking the Key vault, you will move into the Key vault screen. Click Create button and fill in the information to create the Key Vault.

Step: 3
By clicking the Key Vaults menu, you can see the created key vault in this section.

Step: 4
Open the Key vault that is created, click the Access Policies and assign the cryptographic operations under the Key Permissions section. Save it.

Step: 5
In the SQL Object Explorer, select the table that you want to encrypt. Then right-click to choose the Encrypt Columns option.

Step: 6
Always Encrypted Dialog box will open like the below image. Click Next.

Step: 7
Select the columns by clicking the checkbox that you want to encrypt and select the Encryption Type. The system will automatically generate a common encryption key for the selected columns. Click Next.

Always Encrypted supports two types of encryption:

• Deterministic: Deterministic encryption consistently generates a same encrypted value for some random plain text value. Utilizing deterministic encryption permits filtering by equality, grouping, and joining tables dependent on encrypted values yet could likewise enable unauthorised users to figure information about encoded values by examining patterns in the encrypted column.
• Randomized: Randomized encryption produces an alternate encrypted value for the equivalent plaintext each time. Randomized encryption is safer. But prevents equality, grouping, searches, and joining on encrypted columns

Step: 8
Under the master key drop-down, make sure the Auto-generate column master key option is selected. So, the wizard can automatically generate the Column Master Key. Select the key store provider as the Azure Key Vault option and sign in with your Microsoft account. Select the Azure Key Vault that you created from the drop-down. Click Next.

Step: 9
Now, you are in the Run Settings section. There are two options to encrypt the data, which may be using a wizard or Power Shell script. Select Proceed to finish now. Click next. You will move into the summary section, verify the details and click Finish.

Step: 10
After the process is complete, close the dialog box. If you go back to Management Studio, you can see the encrypted data in the table.