In an era of remote storage and retrieval of data, including the cloud, data security plays a vital role, especially since it’s vulnerable during the transit. Situations like database backup or copy from or to the cloud, there is always a risk of data exposure to outside world lurking around one corner or the other. We have seen a noticeable surge in the technologies around protection and security of data from the world full of unsafe hands. Efforts are being made to protect data at a very granular level of the encryption hierarchy. Protection of business data cannot be stressed upon more.
One way of inching towards the more secure transmission of data is to enable Always Encrypted on the database. We’ll look into the various options we have, including enabling this at granular levels; we’ll look at enabling this at the column level.
The Always Encrypted feature was available only on the Enterprise and Developer editions of SQL Server 2016. Later, this feature was made available on all editions, with SQL Server 2016 SP1. Always Encrypted has the ability to encrypt data even at the column level.
There are several ways to configure the Always Encrypted feature:
- Using the Always Encrypted wizard
- Configuring AE using SSMS
- Create Master Key and Encryption Key using T-SQL and enabling encryption
- Configuring Always Encrypted using PowerShell
Overview of the Always Encrypted Feature
Always Encrypted feature is a handshake mechanism used to encrypt and decrypt data. Encryption here is achieved using certificates, and can be done only by users with access to the relevant certificates. To make a database column Always Encrypted, you must specify the encryption algorithm and the cryptographic keys that are used to protect the data. Always Encrypted needs two keys:
- Column Encryption Key (CEK)
- Column Master Key (CMK)
A Column Encryption Key is used to protect and encrypt data in a column. A Column Master Key is used to protect the (one or more) column encryption keys. The information about the Column Master Key is stored in external key stores like:
- Azure Key Vault: A key vault used to safeguard and manage cryptographic keys and secrets used for encryption and decryption of sensitive data within Microsoft Azure.
- Windows Certificate Store: A certificate container built into Windows that stores and manages the certificates.
- Hardware Security Module (HSM): A hardware device specially designed to securely store sensitive data
Selecting Deterministic or Randomized Encryption
Always Encrypted supports two types of encryption: randomized and deterministic
- Deterministic encryption
- The same encrypted Key for a given value is generated, every time.
- Binary2 sort order collation must be used to setup deterministic encryption on a column.
- Heuristically studying the patterns of the contents of the column could reveal the contents, thereby making it more susceptible to hacking
- Randomized encryption
- This method is more robust and secure, and the patterns are less likely to be predictable due to its random generation of the key for a given value.
- The limitation with this type of encryption is that searching, join, group and, indexing is not possible
In an age of centralized or remote management of data, it is important that the enterprises add an abstraction layer to their data. This way, those who manage the data on a day-to-day basis, such as database administrators are not able to view or use the data. At the same time, those in the enterprise who own the data, have complete access to the data, even though they may not necessarily manage it.
Apart from being the layer of abstraction, Always Encrypted also ensures encryption of data during transit, thereby protecting it from sniffers—typically those involved in attacks such as Man in the Middle.
Configuring Always Encrypted
To set up Always Encrypted, we need to generate the following:
- Key metadata
- Encryption properties of the selected database columns, and/or encrypting the data that may already exist in columns that need to be encrypted.
However, not all of these are supported in T-SQL. Therefore, we need to use client-side tools, such as the SQL Server Management Studio or PowerShell to accomplish these tasks.