Always Encrypted in SQL Server

Requirement: SQL server 2016 or newer

SQL Server always encrypted is a way to encrypt the table column(s) to restrict the unauthenticated access of sensitive/secured/special data.

In order to encrypt any column using always encrypt, A database must have a column master key and a column encryption key.

Column Master key: At least one column master key is required before creating column encryption key as it protects the encryption key.

Column Encryption Key: This key is required to encrypt the column

Let’s create these keys first

Create Column Master Key

To create Column Master key, Connect the database and navigate to Database->Security->Always Encrypted Keys->Column Master key


Right click on Column Master key and select new column master key. It will open below window

Now provide a name. For key store, you may either choose current user or local machine. I am using local machine but if you are using current user then you have to click on generate certificate to generate self-signed certificate. click ok

Column master key is created.

Create Column Encryption Key

To create column Encryption Key navigate to Database->Security->Always Encrypted Keys->Column Encryption key then right click on Column Encryption key and select new column encryption key. It will open below window

 

Provide a name and select column master key from drop down list.

Click ok.

Column Encryption Key is created now.

Our database is ready to encrypt the sensitive column(s) after creating both keys. To encrypt the column, navigate to database table, Expand the table and then Expand the columns.

Now, Right click on column name and select encrypt column. It will open below window

Check the column, select the encryption type from Deterministic or Randomized and select the encryption key from drop down list and click next. On next page click next again

Difference between Deterministic or Randomized is documented in Microsoft docs here

In run setting, you may generate PowerShell script to run later or proceed to finish. Select proceed to finish and click next then finish. The column will be encrypted after successful completion.

Let’s check the column value in the table.

As you can see, the phone numbers are encrypted. To see the un-encrypted phone number, you have to include below string in connection string or connect the database server again with additional connection parameter.

Column Encryption Setting = enabled;

Let me reconnect the database server with additional connection parameter.

Run the query again and it will open a window to enable the always parameterization for T-SQL.

Click on Enable and you can see the actual phone numbers in result now.

Follow me:

Blog: Sqldepository.com

YouTube : Sql depository

Facebook: https://www.facebook.com/sqldepository/

Author: Sauras Pandey

Sauras Pandey is an MCP, SQL Server DBA/Architect with 9+ years of experience working in a variety of environments

1 thought on “Always Encrypted in SQL Server

  1. Hey! This post couldn’t be written any better! Reading through this post reminds me of my previous room mate!
    He always kept talking about this. I will forward this
    page to him. Pretty sure he will have a good read.

    Thanks for sharing!

Leave a Reply

Your email address will not be published.