So, your manager wants you to figure out how to encrypt sensitive Data? Well, Microsoft has introduced a fairly easy way to configure feature called Always Encrypted.
What is it?
Always Encrypted was introduced in SQL Server 2016 and is now featured in Azure. It is an encryption feature that is intended to protect select sensitive data such as credit card numbers and social security numbers. It allows clients to encrypt important data inside the application and never expose the encryption keys to the SQL Database (or Azure) engine. This creates a type of separation between those who actually owns the data and those who are stewards to the data. This ensures that this sensitive data cannot be accessed by on site database administrators (or cloud administrators) and any user who is not authorized to access it. This gives a great deal of comfort to the end users to store this type of information.
Internals overview
Always encrypted is granular in that you can set it up on individual columns within a table that contains sensitive data. Encryption algorithms and keys must be specified. Always encrypted utilizes two types of keys, column encryption keys and column master keys. The column encryption key is used to encrypt the actual data within a column. The column master key is used to encrypt one more column encryption keys. The column master key is stored in a trusted key store externally (think Windows Certificate store on a client machine, or Azure Key Vault if you want to get fancy!).
Once the keys are taken care of, the database engine stores the encryption configuration of the columns within the metadata of the database. Within this metadata, the encrypted values of the column encryption keys can be found (so the keys aren’t kept in plain text either, follow closely now…) along with the location of the column master keys.
How do we view the encrypted data?
In order to view the data in an encrypted column in plain text, the front-end application must use a special driver. This is referred to as an Always Encrypted enabled driver. The way this works is that when the application invokes a stored procedure that accepts parameters which reference encrypted columns the driver works with the database engine to obtain the necessary information in order to return the value. This is where the metadata comes in, the driver can obtain the encryption algorithm information as well as the location of the column encryption key.
Once it finds the location of the encryption key, the driver will contact the key store so it can use the column master key to decrypt the encrypted column encryption key. Once it decrypts the column encryption key, it will cache the plain text result of the key (so it doesn’t have to go through this entire process for each piece of data returned). The driver is then able to take the values of the parameters in the stored procedure and substitute what was input with the encrypted values and sends it to the query processor.
Once the server id finished grabbing the result set, including encrypted columns, the driver uses the encryption metadata for the corresponding columns with the relative encryption algorithm and the keys. The driver will then utilize the encryption key stored in cache (if it can’t find it, it will make another trip to the certificate store as noted in the above paragraph). The driver will then begin to decrypt the results and presents the plain text information to the application.
When should I NOT use it?
Always Encrypted is not recommended for encrypting an entire database. Range scan queries will not work as SQL Server can’t do a string search inside the contents of an encrypted column and full text indexes are not supported. A better option would be TDE (Transparent Data Encryption). The drawbacks of TDE are that it only encrypts the data at rest (and the backup files are also encrypted by default). The data is not encrypted in transit or “in flight”.
You should also avoid this feature if you’re not utilizing the latest. Net libraries. This is a very new feature and you must ensure proper compatibility with your application and drivers. I urge you to do plenty of research and testing on your own as details regarding the libraries and drivers are beyond the scope of this article.
How to set it up:
Utilizing the Always Encrypted Wizard is probably the best way to get started with the process.
Open up SSMS, right click a database and select tasks -> Encrypt Columns
Go past the introduction screen:
On the next screen, expand the tables and select the column(s) that you want to encrypt:
After selecting the column, select the encryption type and the encryption key. (If the encryption key has not yet been created, just leave it as Auto).
The encryption type can be either Deterministic or Randomized. What’s the difference?
Deterministic encryptions always generate the same encrypted value for any given plaintext value. This is less secure but more functional in that it allows lookups, equality joins, grouping, indexing on encrypted columns. It likely creates “friendlier” execution plans.
Randomized encryption is the opposite of deterministic in that it doesn’t use the same encrypted value for a plain text value. It is less predictable and thus far more secure. You will suffer a performance hit, however, in that you will not be able to utilize searching, grouping, indexing and joining on encrypted columns.
Next, choose the options for the master key. Autogenerate in this case, and I’m storing it in the Windows Certificate store (though you can use Azure as well).
Choose to run now or save as a PowerShell script (not a bad idea to hold on to the configuration!).
Watch the progress and wait for completion.
Once it’s completed successfully, do a quick select from the table and see that all the social security numbers are now encrypted.
I hope you found this overview of Always Encrypted useful. It can be very powerful and provide great value to your organization if you’re in need of encrypting sensitive data at rest and in transit.
See also
Interested in an enterprise-level auditing and compliance solution for GDPR, HIPAA, PCI and more, including tamper-proof repository, fail-over/fault tolerant auditing, sophisticated filters, alerting and reports? Consider ApexSQL Audit for SQL Server.
He started his IT career in helpdesk world and eventually moved into the networking/systems administrator side of things. After some time, he developed an affection for working with Databases (mainly SQL Server) and has focused his career on that for the past eight years. He has worked for various industries in both large and small environments all with different needs.
SQL Shack has provided him with an opportunity to contribute to a community that has given him so much throughout the years.
View all posts by Aamir Syed
- Is SQL Server Always Encrypted, for sensitive data encryption, right for your environment - July 27, 2018
- Use cases for Query Store in SQL Server - July 18, 2018
- When to Use SQL Temp Tables vs. Table Variables - February 21, 2017