Always Encrypted (AE) is a feature Microsoft introduced to SQL Server 2016. This post will provide an overview of AE and talk about how it can be used to bolster SQL Server security.
What is SQL Database Encryption?
Encryption is the process of encoding data to make it unreadable by humans. This protects the information if it is compromised or accessed by unauthorized individuals. The encrypted data then needs to be decrypted using the same keys used to encode it. Encryption obscures the real data values, thereby keeping them secure. This can be critically important when dealing with SQL databases that store valuable and sensitive enterprise data.
Encryption is not a new concept. It’s a proven technique that database administrators (DBAs) employ for protecting enterprise data resources.
Types of SQL Server Encryption
Several types of encryption can be used with SQL Server databases, including:
- Transparent Data Encryption (TDE) – Data is encrypted at the database level at rest but decrypted when in motion from memory to storage processor.
- Column level encryption – Column level encryption allows DBAs to get to the data with SQL Server knowing the keys.
- Dynamic data masking – Does not encrypt data but simply masks values in a specific view.
- Always Encrypted – Data is encrypted everywhere from everyone including the DBAs.
How Does Always Encrypted Work?
The major difference between AE and the other encryption methods available for SQL Server is the inability of DBAs to view the unencrypted data. Only metadata about decryption keys is held in the database, paving the way for a more secure environment.
No code changes are required to implement AE in your SQL Server databases. Data is encrypted when it is sent to an application, an advantage over the TDE method where data is only protected at rest.
AE comes in two flavors that can be implemented on any column in your SQL Server database.
- Deterministic encryption always replaces a specific value with the same encrypted alternative. This allows columns to be indexed and queried. This type of encryption is best used when there are many different values in a column. With a small set of potential values, deterministic encryption may enable the underlying values to be deduced by unauthorized actors.
- Randomized encryption provides a different randomized value for each encryption instance. This type of encryption provides more security but makes it impossible to index and search for data elements.
Each field in a table can be encrypted using either method. DBAs need to balance the need to protect data elements with the ability to use them efficiently in queries.
Encryption is performed at the SQL Server Native Client which acts as the intermediary between a trusted application and the database. A master key and column key are used to find the value requested by the application. The database only reads encrypted data with all encryption and decryption occurring in the .NET client, away from the eyes of the DBA.
Potential Roadblocks to Using Always Encrypted
Some roadblocks need to be worked through before AE can be implemented in your SQL Server database. Some issues that inhibit the ability to use AE include:
- Distributed queries using linked servers;
- No default or check constraints;
- Partition columns or aggregations;
- Computed columns;
- Columns with the IDENTITY property.
In many cases, the roadblocks can be removed by making changes to the structure of your database tables. In other instances, AE will not be appropriate for a particular SQL Server database and other methods such as TDE will need to be used.
Using Always Encrypted in Your Environment
Two kinds of keys are used by AE. A column encryption key (CEK) is used to prevent viewing of the protected data. A column master key (CMK) controls the column keys. Each table can be managed separately with every column assigned a separate column encryption key. To unlock a column key to view data, the master key is also needed. Keys are not stored in the database, only metadata showing where the keys are located.
Keys are stored either in the Windows certificate store on each SQL Server or in an Azure key vault. The key vault lets all servers use the keys and eliminates storing keys in the certificate store on individual servers.
An informative demo illustrating how to set up AE is available on the IDERA website and is highly recommended for viewing by DBAs interested in using this powerful encryption capability.
Understanding Your Data Resources
SQL Secure is a dedicated SQL Server security tool that can be used in conjunction with AE to protect sensitive enterprise data resources. It provides a database team with the information necessary to understand the data assets that need to be protected and who has access to them. With the increase in data breaches linked to internal actors, this information is critically important when attempting to secure sensitive information.
The following features are available with SQL Secure to help DBAs protect their systems:
- Effective rights analysis to identify how and where rights are granted and illustrate where security gaps need to be addressed.
- Viewing database roles permissions to ensure the only the proper personnel have elevated privileges.
- Weak password detection to guard against intrusion attacks.
- Comprehensive security reporting that includes built-in and customized reports to furnish the information recruited for compliance and security auditing.
- Report and manage SQL Server security across your complete environment including physical, virtual, and cloud instances from a central console.
With SQL Secure to protect access to your SQL Servers and AE to encrypt their data, you can achieve a greater level of security for your databases and keep enterprise information assets safe.