With the introduction of SQL Server 2016 in June (Official Final Release), Microsoft had introduced few new and very useful features into the SQL Server. One such feature is the ‘Always Encrypted’.
‘Always Encrypted’ is the ability to perform SQL operations (there are restrictions) on your data as it were normal (non encrypted), while keeping them encrypted all the time. This means SQL Server will always get encrypted data to be stored into the tables. This will put an extra layer of protection on to your data making sure that even onsite DBAs or Developers cannot see the plain text value behind the encrypted data using their level of access. (Users with ‘SysAdmin’ access won’t be able to see these details without the Key). Therefore ‘Always Encryption’ provides a separation between those who own the data (and can view it) and those who manage the data (but should have no access).
Why Always Encrypted ?
There are many benefits using Always Encrypted feature:
- It provides a clear separation between the data owners and people who manage it
- Unless proper access is provided via encryption keys, even DBAs or SysAdmin users cannot access the data in plain text
Ultimately, the aforementioned points will provide an unparalleled protection against data breaches and help to protect sensitive information such as credit card numbers, personal details, etc. Also, this will broaden the boundaries where such sensitive information can be kept.
How Always Encrypted Works?
This is a client-side encryption technology which the SQL Server Client Driver plays a key role.
- The data is transparently encrypted inside a client driver.
- Client manages the encryption key. SQL Server doesn’t have any information regarding the encryption key.
SQL Server can query and perform certain computations on the encryption data, such as equality comparison, equality joins, group by, etc.
Always Encrypted Demonstration
We will see how Always Encrypted can be implemented and used. In order to illustrate, we will use a table which contains employee information.
CREATE TABLE Employee(
Id INT
,FirstName VARCHAR(100)
,LastName VARCHAR(100)
,DOB DATE
,SSN INT
,[Address] VARCHAR(255)
,PostalCode INT
)
INSERT INTO Employee (
[Id],[FirstName],[LastName],[DOB],[SSN],[Address],[PostalCode])
VALUES
(1,'James','Rubin','20-Jul-1986',173456858,'10585 N 600 E',46310)
,(2,'Austin','Pyatt','24-Dec-1985',138868248,'100 BENTBROOK CT',27519)
,(3,'Stacey','Munoz','23-Dec-1988',185682639,'1 WOODSIDE DR',4976)
,(4,'James','Tweed','03-Jan-1987',133890886,'1 AUNNEK CT',95023)
,(5,'James','Robles','11-Sep-1989',154135505,'101 FISHTRAP RD',35504)
,(6,'Ebony','Lewis','17-Jul-1988',120488337,'101 N OAKS DR',35180)
,(7,'Marian','Caro','20-Nov-1985',115281829,'1017 FISK ST SE',49507)
,(8,'Lynne','Martinez','22-Apr-1985',157900240,'103 UNITY CT',78214)
,(9,'Elsa','Cole','25-Apr-1990',150631885,'1001 E FERN AVE APT 201',78501)
,(10,'Kiley','Caldwell','03-Jan-1988',131368172,'103 NOB HILL LN APT 5',40206)
,(11,'Michael','Soluri','17-Jun-1985',173245124,'10770 S KILBOURN AVE',60453)
,(12,'Gregory','Emmons','06-Sep-1988',137693229,'10 LOUISA PL APT 2F',7086)
,(13,'Jessica','Barr','04-Feb-1989',155895863,'1 FAWNRIDGE DR',94945)
,(14,'Daniel','Mccabe','06-Sep-1985',148236776,'1 CALLE MARGINAL GARCIA',674)
,(15,'Sharon','Schwartz','06-Sep-1987',117569460,'1 KRITTER CT',8050)
,(16,'Dorthy','Wear','13-Dec-1988',170517705,'1 CLARK RD',35747)
,(17,'Betsy','Blansett','17-Jun-1990',182202498,'10 CALLE 1 DE FLORIDA',612)
,(18,'Margaret','Payne','25-Jul-1985',157359609,'1003 BLOOMFIELD AVE',7006)
,(19,'James','Walker','26-Jan-1989',142829150,'100 CONGLETON HOLLOW SPUR RD',40447)
,(20,'Sarah','Reeves','22-Jun-1990',146171169,'1 BLUEBERRY LN',1832)
I have a small MVC Web Application which has a page to list out the aforementioned details from the SQL Server. The MVC Controller will load the details to a list of Employee records and pass it to the HTML view which will be displayed as follows:
In the MVC application, I have the following data model to load details from the SQL Database Table.
public class Employee {
public int Id { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public DateTime DOB { get; set; }
public int SSN { get; set; }
public string Address { get; set; }
public int PostalCode { get; set; }
public Employee() {
}
}
And I am using the following connection string in order to connect to the SQL Server Database.
const string zConnectionString =
@"Server=.\SQL2K16; Network Library=DBMSSOCN;Database=SQLTraining;
Trusted_Connection=True;";
There are few steps to be followed on both SQL Server and application side (Client Applications) in order to implement and use this feature.
From the SQL Server side, there are few ways to enable the Always Encrypted feature. We will look at more details how to use these features using the wizard.
- Right click the table which you want to encrypt details and select ‘Encrypt Columns’. This will take you to the wizard.
- You will get the introduction screen which contains few details about what ‘Always Encrypted’ is all about. Click next and proceed to the next screen.
This is the column selection screen, which allows you to select which columns you want to encrypt and using which Encryption Type. There are two Encryption Types available in SQL Server 2016.
This advice has been included in Microsoft Documentation: Use deterministic encryption for columns that will be used as search or grouping parameters, for example, a government ID number. Use randomized encryption, for data such as confidential investigation comments, which are not grouped with other records and are not used to join tables.
So in our example, we will choose DOB & SSN columns for encryption. For DOB, we will choose Randomized and for SSN we will choose Deterministic.
Once the encryption type is chosen, the wizard should be similar to the screen shown below:
If you look closely, you will be able to see that the Encryption Key combo is disabled. The reason for this is the fact that we haven’t created any Column encryption keys so far. If the keys are created prior to the column selection, then you will have the option to choose whether to use an existing key or to generate a new key.
In this illustration, we will use the option which will create a new column encryption key. Click next to proceed to the next step.
- Deterministic –> Deterministic encryption always generates the same encrypted value for any given plain text value. Using deterministic encryption allows point lookups, equality joins, grouping and indexing on encrypted columns. However, it may also allow unauthorized users to guess information about encrypted values by examining patterns in the encrypted column, especially if there is a small set of possible encrypted values, such as True/False, or North/South/East/West region. Deterministic encryption must use a column collation with a binary2 sort order for character columns.
- Randomized –> Randomized encryption uses a method that encrypts data in a less predictable manner. Randomized encryption is more secure, but prevents searching, grouping, indexing, and joining on encrypted columns.
- The next step is the Column Master Key Configuration. A Column Master Key will be used to encrypt and protect the Column Encryption Key, which is used to encrypt the data. We will use the option ‘Auto generated column master key’, which the wizard will generate the key for us. When we are creating a new Master Key, there are two options available, where to store the newly generated key. Clicking on the small info button beside each option will give further details about each option.
- Click next to move to the next step. In this step, you can decide whether you require a PowerShellscript to be generated for the encryption process or to proceed with the encryption immediately. In this example, we will select the second option and click on the next button.
In this step, you will be presented with the steps which will be followed during the data encryption
Click finish to complete the encryption process. Once process is completed, click close button.
Now if you check the details on SQL Table, you can see that data in SSN and DOB columns are encrypted.
SELECT * FROM dbo.Employee
If you see the Table creation script for the Employee table now, you could see few changes which have been done by the SQL Server after we enabled the encryption for those two columns.
CREATE TABLE [dbo].[Employee](
[Id] [INT] NULL,
[FirstName] [VARCHAR](100) NULL,
[LastName] [VARCHAR](100) NULL,
[DOB] [DATE] ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1],
ENCRYPTION_TYPE = RANDOMIZED,
ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
[SSN] [INT] ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1],
ENCRYPTION_TYPE = DETERMINISTIC,
ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
[Address] [VARCHAR](255) NULL,
[PostalCode] [INT] NULL
) ON [PRIMARY]
You can see that it had added the ENCRYPTED WITH clause for those two columns. ENCRYPTED WITH clause consists of 3 attributes which are:
If you inspect the Always Encrypted keys in the object explorer in SSMS, you could see the following meta data for the Master and the Column Encrypted Keys.
Column Encrypted Key – CEK_Auto1
For further information, please refer to the following URL: https://blogs.msdn.microsoft.com/sqlsecurity/2015/07/06/always-encrypted-key-metadata/
Column Master Key - CMK_Auto1
For further information, please refer to the following URL: https://blogs.msdn.microsoft.com/sqlsecurity/2015/07/06/always-encrypted-key-metadata/
Now if we try to fetch details without doing anything on the sample .NET Application, you will get a similar error like shown below:
Now we will look into the things that we required to change on our application side (Business) in order to retrieve the required information.
1. Make sure that the target framework is version 4.6 or higher.
2. In the Connection String include ‘Column Encryption Setting=enabled’
And I am using the following connection string in order to connect to the SQL Server Database.
const string zConnectionString = @"Server=.\SQL2K16; Network Library=DBMSSOCN;
Database=SQLTraining;Trusted_Connection=True;Column Encryption Setting=enabled;";
Now if we check the details from our application, we can see that DOB and SSN values are fetched as plain text, even though the values are encrypted in the SQL Server.
Hope this will help you to understand the ‘Always Encrypted’ feature in SQL Server 2016 and how to integrate it to an existing application.
COLUMN_ENCRYPTION_KEY –> CEK_Auto1 since we have chosen the option for SQL to generate a new key.ENCRYPTION_TYPE –> Can be either RANDOMIZED or DETERMINISTICALGORITHM –> This is always AES_256COLUMN_MASTER_KEY –> Name of the column master key protecting the value of the column encryption key.ALGORITHM –> Algorithm used to generate the encrypted value of the column encryption key (RSA_OAEP).ENCRYPTED_VALUE –> Encrypted value of the column encryption key. The encrypted value is assumed to be produced by encrypting the plaintext of the column encryption key using the specified column master key and the specified algorithm.KEY_STORE_PROVIDER_NAME –> Name of a provider for the key store that holds the column master key.KEY_PATH –> Key path specifying the location of the column master key in the key store.
