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.
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.
Several types of encryption can be used with SQL Server databases, including:
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.
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.
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:
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.
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.
SQL Secure is a dedicated SQL Server security tool that can be used in conjunction with Always Encrypted 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:
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.
Try SQL Secure for free!
Powered by IDERA