SQL Server 2016’s “Always Encrypted” Feature Changes the Game for Database Security
Greg Ota
Manager, Database Engineering
Law professor and “professional privacy paranoid” Jeffrey Rosen says it well:
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.
With systems hosted at Ensono or managed in a cloud environment, our clients can feel confident in the knowledge that their sensitive, mission critical data is more secure than ever. Information such as PII and credit card data will only be available to your company.
Database security should never be a passive activity. Ensono’s database team will continue to consider the Always Encrypted feature for all future builds that contain sensitive data.
Social Share
Don't miss the latest from Ensono
Keep up with Ensono
Innovation never stops, and we support you at every stage. From infrastructure-as-a-service advances to upcoming webinars, explore our news here.
Blog Post | September 27, 2024 | Best practices
Ensuring Secure Identity Architecture with Trusted Directories
Blog Post | September 9, 2024 | Best practices
Why QA Matters in Light of the CrowdStrike Incident
Blog Post | September 5, 2024 | Technology trends