Column Level Encryption using Always Encrypted in SQL Server 2016

ByHariharan Rajendran

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_256COLUMN_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 = MyColumnKeyNOT NULL,

[FirstName] nvarchar(50) NOT NULL,

[LastName] nvarchar(50NULL,

[Designation] nvarchar(50NULL,

[Email] nvarchar(50NOT NULL,

[Phone Number] nvarchar(10NOT NULL,

[Salary] decimal(10,2

ENCRYPTED WITH (ENCRYPTION_TYPE = RANDOMIZEDALGORITHM ‘AEAD_AES_256_CBC_HMAC_SHA_256’,

COLUMN_ENCRYPTION_KEY = MyColumnKeyNOT NULL

PRIMARY KEY CLUSTERED ([UserID] ASCON [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.

 

About the Author

Hariharan Rajendran author

Hariharan Rajendran is a Microsoft Certified Trainer and Lead BI Solutions Consultant with 8+ years of experience in Database, BI and Azure platforms. Hariharan is also an active community leader, speaker & organizer and leads the Microsoft PUG (Power BI User Group – Chennai), SQLPASS Power BI Local Group – Chennai and an active speaker in SQL Server Chennai User Group and also a leader in Data Awareness Program worldwide events. Hariharan also frequently blogs (www.dataap.org/blog), provides virtual training (on ad-hoc basis) on Microsoft Azure, Database Administration, Power BI and database development to worldwide clients/audience.

Comments Are Closed!!!