SQL Server 2016’s “Always Encrypted” Feature Changes the Game for Database Security
August 9, 2016 | Technology trends
Greg Ota Manager, Database Engineering
Law professor and “professional privacy paranoid” Jeffrey Rosen says it well:
Privacy is not for the passive.
Sure, developers have an array of encryption and hashing techniques at their disposal to mitigate data security threats. But it’s a tedious process and is typically deployed only for an organization’s most sensitive data, including encryption keys or passwords. Perhaps that’s why high-profile corporate and government agency breaches continue to occur with startling regularity.
Enter Microsoft’s SQL Server 2016. Among other things, the new Always Encrypted feature allows database administrators to encrypt sensitive data inside an application—without having to reveal the encryption keys to the SQL database or server.
Transparent Data Encryption vs. Always Encrypted
In my opinion, this is a sorely needed additional step to Transparent Data Encryption (TDE). In fact, some within the Microsoft community are calling it a game-changer. The release appears to be in line with Microsoft’s marketing of Azure SQL databases and Stretch databases, and attempting to increase confidence in the security of the data stored in cloud environments.
Whereas TDE encrypts an entire database while at rest, Always Encrypted encrypts at the column level but with several additional benefits. Always Encrypted provides transparent encryption from the database to client applications. This improves upon TDE by providing encryption of sensitive data in memory and in transit, as well as at rest. The Always Encrypted-enabled driver performs the encryption and decryption for the application. The information owner can then control any potential leakage to database administrators by maintaining the decryption keys so that administrators don’t have incidental access to sensitive data. By contrast, the database administrator has access to the encryption keys with TDE.
The Limitations of Always Encrypted
However, there are some limitations for the implementation of Always Encrypted.
Modification of existing applications may be required.
Some encrypted data types require a “_bin2” collation, which may require DDL changes.
Your application will need to be compatible with .NET 4.6, and the application administrator will need to fully understand the encryption keys to ensure that they are protected—both from the database administrators and other unintended audiences.
The encryption keys will also need to be backed up for disaster recovery.
Adding encryption may increase your database size and CPU usage (especially for database writes), and adding encryption may also prevent any deduplication algorithms. Features such as replication are not currently supported and are only available in the more costly Enterprise Edition, which may require an upgrade.
The Always Encrypted feature provides a welcome additional level of security for sensitive data that may allow for reduction in administrator costs. Yet, the requirements tend to lean toward new application development rather than retrofitting existing systems.