History of database encryption is quite old. If we look behind to SQL 2000 era back in the beginning of this century there were no native tools to do so. But several third party tools were always available to encrypt the data at rest. Even another popular choice was to encrypt the entire data disk. Later on, SQL 2005 introduces call level encryption. But a big change came on SQL 2008 which brings the concept of Transparent Data Encryption (TDE). But once again like the third party encryption tool TDE can’t encrypt data in motion. Therefore, data in memory or in the communication channel are still in the risk. Moreover, TDE requires the entire database to be encrypted which increases the size of the database.
Why Database Encryption
- Separation of role between who own data and who manage data
- Protecting sensitive data e.g. Credit Card Number, National ID
- Running database and/or application in the cloud
- Prevent high-privileged users from having access to sensitive data
- Delegation of DBA role
- Regulatory Compliance and Audits
Always Encrypted on SQL 2016
SQL 2016 comes into play with a solution of all the above “Always Encrypted” meaning data is encrypted and will be remain that wherever it resides except from the user who own data. Always encryption provides:
- A transparent end to end solution for sensitive columns
- All encryption and decryption is handled transparently by the driver library on the client
- Allows clients to encrypt sensitive data inside client applications and never reveal the encryption keys to SQL Server
- Data is never in plain text while being stored or accessed while on SQL Server (including while in memory)
How it works
The underlying working principal for always encrypted is quite straight forward. It requires the following to start up:
- A Column Encryption Key (CEK) to encrypt the selected columns.
- A Column Master Key (CMK) is required to encrypt the CEK.
- Selection of the encryption algorithm.
- Selection of the column to be encrypted.
- A certificate store (either windows or Azure) to store the certificate.
- Installing the certificates where the client application runs.
Let’s get into detail of these in the following sections.
Always Encrypted Keys
Always Encrypted uses two types of keys: column master keys and column encryption keys
Column Master Keys (CMK):
- To encrypt column encryption keys
- Encrypted values of the keys along with their location are stored on system catalog view
- SQL Server does not contain the keys needed to decrypt data
- Must be stored in a trusted key store
- Column Master Keys must be deployed on each client machine that needs access to the unencrypted data
Column Encryption Keys (CEK):
- To encrypt sensitive data stored in database column
- A single key can encrypt all values in a column/ table
- Encrypted values of the keys are stored on system catalog view
- Store this key in a secured/ trusted location for backup
- Each CEK can have 2 encrypted values from 2 CMKs to allow master key rotation
Type of Encryption
There are two types of encryption deterministic and randomized.
Deterministic encryption:
- Generate same encrypted value for a given text
- Allows grouping, filtering and joining
- Should be used on column used for primary key, indices or unique constraints
- Better chance of data decryption by unauthorized user by examining the pattern especially when applied to a smaller set of data
Randomized encryption:
- Encrypting data in a less predictable manner
- More secure because different set of data is generated for same plain text
- Prevents equality searches, grouping, indexing and joining
Creating your first Always Encrypted Object
In this step by step example we’ll be creating a very basic HR database with an Employee table consisting of only four column ID, Name, Sex and Salary. We’ll encrypt the salary column using deterministic encryption because salary can be compared for different range of value. As the Sex column will have a very small data set (Male or Female) it is better to use randomized encryption for this data otherwise data can be easily presumed.
Here are the steps:
- Create HR DB
-
Create Table employee( EID INT NOT NULL PRIMARY KEY, EName VARCHAR(10) NOT NULL, Sex VARCHAR(6) NOT NULL, Salary NUMERIC(10,2) NOT NULL)
- Let’s insert few data:
INSERT INTO Employee VALUES (1,'David','Male',10000) INSERT INTO Employee VALUES (2,'Lily','Female',20000)
- Right click on Employee table and select Encrypt Columns…
- The Encryption wizard will appear. Click Next on it
- Select the column you want to encrypt. Here Sex and Salary columns are selected for encryption. Sex column will be encrypted using randomized algorithm and Salary column will be encrypted using deterministic algorithm. An auto generated new Column Encryption Key will be created (CEK_Auto1)
- Click next to create the Column Master Key. Select the auto generated option and save it to Windows Certificate Store in Local Machine.
- Proceed next to continue
- The wizard will execute the scripts.
- Finally press close when it is done.
- That’s all now we have things ready.
- Running a select statement will show encrypted value for the rows inserted earlier.
- The CEK and CMK can be found under Always Encrypted Keys Option of HR DB
- The CMK certificate can be found at Windows Certificate Store of local machine. Run certmgr.msc and select Certificates under Intermediate Certification Authorities. The Always Encryption Auto Certificate1 can be found there.
- You need to install this certificate to the client machine from where your application runs. You can export this certificate by right clicking on the certificate name and select export. The certificate can be imported by just double clicking on any client machine.
Please ensure that the encryption wizard runs on some other machine than the DB server. Because the DBA will have complete access to the DB server and if the key is available on the DB server the DBA can easily have a look to the decrypted value.
Well, so far we are done with creation of the KEY and certificates. Now we want to have a look our original data. If you have the certificate installed on a machine and there you have a SQL Management Studio you can easily view the data by adding additional connection parameter.
Enable the client application to read and write the Encrypted Object
As we’ve already seen that adding the additional connection parameter allowing us to view the encrypted data where the certificate is installed, similarly for client application we just need to add the same string to the connection properties.
If you are running a MVC web application on a Windows 2012 R2 server. You need to install the certificates on this server before you change the web.config.
Controlling the performance of Data Layer
Controlling the performance of data access layer is a major fact to consider while encrypting your data. Here are some rule of thumb.
When most of the queries access encrypted column:
- Enable the encryption at connection string
- SqlCommandColumnEncryptionSetting.Disabled for query that do not access encrypted column
- SqlCommandColumnEncryptionSetting.ResultSet that do not have any parameter requiring encryption but retrieve encrypted column
When most of the queries do not need to access encrypted column:
- Disable the encryption at connection string
- SqlCommandColumnEncryptionSetting.Enabled for query that have encrypted parameters
- SqlCommandColumnEncryptionSetting.ResultSet that do not have any parameter requiring encryption but retrieve encrypted column
Limitation
There are some data types as well as column type which are not supported to be a candidate for always encryption:
- XML
- timestamp/ rowversion
- image
- ntext/ text
- sql_variant
- hierarchyid
- geography/ geometry
- User defined type
- Non Binary2 Collation string data type
- Alias
- Sparse column set
- Partitioning columns
- Columns with default constraints/ check constraints
- Referencing column can’t be encrypted with randomized option (for deterministic option the CEK must be the same)
- Columns that are keys of full text indices
- Columns referenced by computed columns when the expression does unsupported operations
- Columns referenced by statistics
- Table variable columns
Clause that can’t be used:
- FOR XML
- FOR JSON PATH
Features that are not supported:
- Transactional or Merge Replication
- Distributed Queries (linked servers)
Resources
There are other things like key rotation, migrating from legacy system especially for Entity Framework and many more. Here are some useful links which can be a good candidate for starting up.
Always Encrypted (Database Engine):
https://msdn.microsoft.com/en-us/library/mt163865.aspx
https://channel9.msdn.com/events/datadriven/sqlserver2016/alwaysencrypted
Always Encrypted (Client Development):
https://msdn.microsoft.com/en-us/library/mt147923.aspx
Column Master Key Rotation and Cleanup with Always Encrypted:
https://msdn.microsoft.com/en-us/library/mt607048.aspx
Import/Export Windows Cert:
http://windows.microsoft.com/en-us/windows/import-export-certificates-private-keys#1TC=windows-7
Happy Encrypting :)
Sultan's Blog 