BLOGS

Test Drive on Always Encrypted in SQL Server 2016

To test the always encrypted feature in SQL Server 2016, Follow the below test steps,

1. Create a table with encrypted column, refer here to set up.
2. Insert a data into the table
   1. Use client application to insert a data, refer here.
   2. Export and import from another table, refer here.
3. Check the table result

Tables used in my test,

1. Employees – Always Encrypted on TaxID & Salary fields
2. Employees_NoEncrypt

 

Column Level Encryption using Always Encrypted in SQL Server 2016

Always encrypted feature in SQL Server 2016 is the best option who wants to encrypt the certain column values in a table.

Steps to implement the always encrypted option,

1. Set up column encryption key with the help of column master key – Go through column master and encryption article here to set up.
2. Create a table with column level encryption setting
   1. Column encryption key
   2. Algorithm – Currently only one algorithm is supported which is AEAD_AES_256_CBC_HMAC_SHA_256
   3. Type of encryption – DETERMINISTIC or RANDOMIZED. Visit here to know detailed information

Syntax to apply encryption on a column,

CREATE TABLE <tablename>

 (<column name> <data type>

 ENCRYPTED WITH (ENCRYPTION_TYPE = <Type of Encryption>,

 ALGORITHM = ‘AEAD_AES_256_CBC_HMAC_SHA_256’, COLUMN_ENCRYPTION_KEY =<column encryption key name>)

 )

In this below table, I want to encrypt employee Tax ID and Salary fields,

CREATE TABLE [dbo].[Employees]

( [UserID] int IDENTITY(1,1),

[TaxID] varchar(11)

COLLATE Latin1_General_BIN2 ENCRYPTED WITH (ENCRYPTION_TYPE = DETERMINISTIC,

ALGORITHM = ‘AEAD_AES_256_CBC_HMAC_SHA_256’, COLUMN_ENCRYPTION_KEY = MyColumnKey) NOT NULL,

[FirstName] nvarchar(50) NOT NULL,

[LastName] nvarchar(50) NULL,

[Designation] nvarchar(50) NULL,

[Email] nvarchar(50) NOT NULL,

[Phone Number] nvarchar(10) NOT NULL,

[Salary] decimal(10,2) 

ENCRYPTED WITH (ENCRYPTION_TYPE = RANDOMIZED, ALGORITHM = ‘AEAD_AES_256_CBC_HMAC_SHA_256’,

COLUMN_ENCRYPTION_KEY = MyColumnKey) NOT NULL

PRIMARY KEY CLUSTERED ([UserID] ASC) ON [PRIMARY] )

 

By using the above script, we can create a table with encrypted columns.

If you try to insert data into the table using insert statement then you will get an error message like below,

Msg 206, Level 16, State 2, Line 16

Operand type clash: varchar is incompatible with varchar(8000) encrypted with (encryption_type = ‘DETERMINISTIC’, encryption_algorithm_name = ‘AEAD_AES_256_CBC_HMAC_SHA_256’, column_encryption_key_name = ‘MyColumnKey’, column_encryption_key_database_name = ‘Databrain’) collation_name = ‘SQL_Latin1_General_CP1_CI_AS’

It will not allow you to insert a data directly because of applied encryption. This can be done through a client application. Refer Microsoft article here for the detailed explanation.

 

[Step by Step] Column Master & Encryption Keys in SQL Server 2016

Following my previous article, this article will help you to understand how to create encryption keys which are the prerequisites for encrypting the table columns.

Steps to create Column Master Key

1. Open SQL Server Management Studio
2. Choose the database where you want to apply encryption for tables
3. Expand the (+) sign
4. Go to Security > Always Encrypted Keys > Column Master Key Definition
5. Right click Column Master Key Definition folder and choose New Column Master Key Definition
6. Provide the name
7. Select the Key Definition Source from drop down (if no value then refresh)
8. Select your machine
9. Click Ok.

Screenshots for your reference,

    

 

Steps to create Column Encryption Key

1. Open SQL Server Management Studio
2. Choose the database where you want to apply encryption for tables
3. Expand the (+) sign
4. Go to Security > Always Encrypted Keys > Column Encryption Keys
5. Right click Column Encryption Keys folder and choose New Column Encryption Key…
6. Provide the name
7. Choose the Column Master Key Definition
8. Click Ok.

Screenshots for your reference,

Always Encrypted in SQL Server 2016

SQL Server 2016 introduces a new feature called “Always Encrypted”. It allows to encrypt the data in storage and also in motion (when reading a data). This helps us to secure the data.

There are several concepts involved in Always Encrypted,

1. Column Master Key
   1. This is an encryption key that protects the column encryption keys.
   2. At least one master key should be available before encrypting any columns.
2. Column Encryption Key
   1. This is the encryption key that actually protects the encrypted columns.
   2. This will make use of column master key.
3. Column-Level Encryption setting
   1. Column must be set to encrypted using
      1. Column encryption key
      2. Algorithm
      3. Type of encryption

Encryption Types.

1. Deterministic
   1. It is always encrypted to the same cyphertext (The result of encryption performed on plaintext using an algorithm).
   2. It can be used for operations like lookup (join), distinct, group by.
   3. It can be indexed.
2. Randomized
   1. It is more secure.
   2. It cannot be used for operations like deterministic.
   3. Write and Read only possible
   4. It cannot be indexed.