This blog is a continuation of the Azure SQL Security series. We have covered various security requirement scenarios in the previous blogs, discussed Dynamic Data Masking, and Transparent Data Encryption in detail. In this blog, we will focus on the Always Encrypted feature.

Business use case 

Consider a scenario where you need to protect sensitive data in specific columns from even database administrators or other users who do not have a business need to use that data. Though Dynamic Data Masking allows you to protect your column data information, it does not restrict administrators or high privileged users. Always Encrypted encryption mechanism allows implementing this. It decrypts data only when a client application having access to the encryption key needs it. 

Note: You cannot data mask an already encrypted column using the Always Encrypted mechanism or vice versa. But you can use both Always Encrypted and Dynamic Data Masking in an Azure SQL table for different columns.  

Always Encrypted (encryption in use) 

 Always Encrypted is a feature to provide additional protection for specific sensitive columns like SSN, credit card numbers, and to limit database administrator/ other super users access data not needed by them for business purposes. The data is always in the encrypted form unless any client application with proper access to encryption keys access it. It is available in all editions of Azure SQL Database, from SQL Server 2016 (13.x). Always Encrypted provides client-side encryption – a database driver present inside a client application encrypts data before sending it to the database. Likewise, it decrypts the encrypted data retrieved in the query result set. The keys used are of two types: 

• Column encryption keys– used to encrypt data in the database columns. You can store that in the database in an encrypted state. 
• Column master keys -used to encrypt column encryption keys. You can store that in an external key store outside the database, such as Windows certificate store, Azure Key Vault, or hardware security modules. It stores the location of the key in the database. The client application has access to the Azure Key Vault keys, but not the database, unlike transparent data encryption. 

 It supports two types of encryption: 

• Randomized encryption – encrypts data less predictably. It is more secure than deterministic, but it does not support operations like searching, grouping, etc. 
• Deterministic encryption -it always generates the same encrypted value for any column plain text value. It allows point lookups, indexing, equality joins, and grouping on columns encrypted. But it is less secure since the brute force technique can guess patterns. 

To implement this feature using the Always Encrypted wizard in SQL Server Management Studio: 

1. Select a particular table, and right-click, and choose the Encrypt Columns option.

2. Select the columns to encrypt, the encryption type, and key. Select SSN and credit card columns. As discussed before, dynamic data masked columns are not available for encryption. 

3. Select the key store provider for the master key. Here, choose the Azure Key Vault option with a key under the subscription. You need to have create, list, get, unwrapKey, wrapKey, sign, and verify permissions to use it. You can also choose the Windows certificate store based on your requirement. 

4. On clicking finish, the results appear in the next window screen and is successful. 

Now on querying the table, you can see the below output. You can see the credit card and SSN columns are in the encrypted state. 

When you connect to the database with the Always Encrypted option enabled, you can see the decrypted value of columns based on Key Vault access availability. 

You need to use the parameterization for performing SQL operations on the credit card column (deterministically encrypted) like below – update operation. 

When you use any client application, you need to enable the Always Encrypted feature while signing in using any SQL connectors and use parameterization for query operations like above. To know in detail about database permissions needed to query/view Always Encrypted columns refer to this. 

 As discussed so far in the series, you can understand that Transparent Data Encryption is for encrypting entire data at rest to avoid malicious offline access. Database administrators implement this. To limit access to database administrators/ high privileged users, protect specific sensitive column data at rest, and use the client applications, you can use the Always Encrypted feature. If you need to prevent unauthorized access of column data at query execution time, adopt Dynamic Data Masking. These three features serve different purposes, and you can use them together based on your needs. 

Consider the banking domain, where you must protect entire data using Transparent Data Encryption as the first level of protection. You can then use Always Encrypted to encrypt some sensitive columns such as bank account details, debit/credit card details that need to be encrypted for the database administrators. Additionally, you can use Dynamic Data Masking to mask some other important columns like phone numbers and tax numbers, which need protection.  

Learn more about Visual BI’s Microsoft Azure offerings here.