By Amir Eliav,Senior DBA Consultant at DBA Services Ltd
Security has always been a great issue, and a good security policy means that you get access only to the data the helps you perform your job well.
For example, a HR person who works for a product company doesn't need access to the shipping data of the product. This hasn’t been the case with SQL server…
Yes, I am talking about us - the System DBA's.
No matter If you’re a DBA who works in a company or an outsource DBA who manages SQL server for other companies. As long as you have the sysadmin role, the entire organization's data is exposed to you even though your job is only to manage the data and it's integrity, up until now..
Introducing Always Encrypted – from MSDN:
"Always Encrypted is a feature designed to protect sensitive data, such as credit card numbers or national identification numbers. Always Encrypted allows clients to encrypt sensitive data inside client applications and never reveal the encryption keys to the Database Engine."
A few advantages using Always encrypted:
• Transparent, end-to-end encryption handled by the client application
• The data is encrypted in the client application side – holds against the "man (or DBA) in the middle attack".
• Provides separation between the owner of the data and those who manage it
• Helps Reduce regulations about where the data is stored (on premise vs cloud) and who (expensive security clearance DBA vs third party DBA) manages the data
• Can be combined with Transparent Data Encryption for full encryption (Data and files)
You can configure Always Encrypted at the column level, when you define a column for encryption you have to specify a few things:
1. Column encryption key – the key that is used to encrypt the data in the column. The Column encryption key is stored encrypted in the database.
2. Column Master key – protecting key used to encrypt the Column encryption Keys – Stored in a trusted key store (Azure key vault, Windows Certificate store or a hardware security module).
3. Encryption type - there are two types of encryptions, Randomized and Deterministic:
• Randomized encryption – encrypts data in a less predictable manner therefore it is more secured. On the other hand you cannot use randomized encryption for columns you want to perform joins, equality filters, grouping or indexing
• Deterministic encryption – injective encryption method allows you to perform joins, equality filters, grouping and indexing. Deterministic encryption is less secured than the randomized encryption especially when used to encrypt a small set of possible values (columns such as, Gender, True/False etc)
Sounds great right? Before you start implementing, here are a few limitations and advices:
• Always encrypted is not supported for the following data types:
XML, timestamp/Rowversion, image, Ntext, text, SQL_variant, hierarchyid, geography, geometry, alias, user defined-types.
• It cannot be used on tables with change data capture or change tracking enabled
• Transactional or merge replications and distributed queries do not work on encrypted columns
• You cannot use "like" or other non-equality operations
• Troubleshooting is more complicated
• Test! – As always test the solution before implementing on a production environment. Record and compare CPU, running times and other performance values against a base line to make sure your system is able to handle the new requirements
For conclusion, Always Encrypted can help your organization secure your data at the client side easily and reduce regulation limitations concerning where and by who your data is handled.