Always Encrypted. What it is, and why it matters

Always Encrypted is the new encryption feature introduced with Microsoft SQL Server 2016.  Always Encrypted was first introduced in Azure SQL Database V12, the SaaS version of SQL Server that is hosted in Azure. So although Always Encrypted is new, it has already been road-tested by paying customers.

Always Encrypted is in addition to Transparent Data Encryption (TDE), the whole database encryption feature which was introduced with SQL Server 2008. Always Encrypted differs from TDE in a number of ways:

• Always Encrypted encrypts specific columns, in contract to TDE which encrypts the entire database.
• Always Encrypted encrypts data at rest, in motion, and in memory. TDE encrypts the database at rest, but not as the data moves from the server to the client.
• Always Encrypted columns are encrypted when displayed in SQL Server Management Studio (SSMS). TDE displays data in plaintext in SSMS. With Always Encrypted, sensitive data is never displayed in plaintext on the server.
• Always Encrypted data is decrypted on the client, not the server.  Cryptographic keys are held on the client side, making it impossible for a rogue insider to decrypt sensitive data. TDE, in comparison, is encrypted using the Database Master Key (DMK) which is stored on the server.
• Always Encrypted uses authenticated encryption with the AEAD cipher. So not only is the data encrypted, but the cipher provides additional security by authenticating the data source.
• Always Encrypted provides two ways of encrypting data: deterministic or randomized. This means that even though the server only sees encrypted data, joins and equality comparisons can be done on encrypted columns.

So when and why would you use Always Encrypted? And how does it fit with the other security features of SQL Server?

 

Always Encrypted is designed to protect sensitive data such as National Insurance numbers, name and address, date of birth, email address, postcode, health condition, and political views, including trade union membership, etc. It’s the type of data you would want to be is encrypted if the data were ever stolen. It is worth noting that businesses have a responsibility to keep staff data secure, as well as customer data.

 

There are a couple of examples of sensitive data that are best handled in other ways: passwords and credit card numbers. Passwords are best managed using salted hashing, which is a special type of encryption that is never decrypted. Passwords should never be stored in plain text, so if you are not using salted hashing, then passwords should certainly be encrypted. Credit card numbers should not normally be stored within a business system. Very large businesses that have very good security might be an exception, but smaller businesses would be better of thinking about how they can operate efficiently without storing credit card information.

 

For the data you do want to encrypt, Always Encrypted allows you to encrypt specific columns in one of two ways: deterministic or randomized. If you need to use data in a query, use deterministic encryption as it allows equality joins. If you would never use the data in a query, then use randomized.

 

Always Encrypted can be used together with TDE, so it’s not an either/or decision. TDE protects the whole database, whilst still keeping it usable for day to day operations. TDE protects on-premises data in the event of physical media being stolen, but does not protect against rogue insiders, or data in flight. Always Encrypted provides strong encryption for specific personal data, at rest and in motion, and protects data on the server so even administrators cannot see data they are not authorized to see.

Always Encrypted represents a big step forward in keeping private data private, regardless of whether data is stored on-premises or in the cloud. If you are interested in encrypting data within your database, or moving your data to the cloud in a secure way, contact us for a no-obligation chat.