• Skip to main content
  • Skip to primary sidebar

DBA Diaries

Thoughts and experiences of a DBA working with SQL Server and MySQL

SQL Server 2016 New Features – Always Encrypted

May 18, 2016 by Andy Hayes Leave a Comment

always encrypted

In my previous post, I listed a number of the SQL Server 2016 new features. Let’s look at the first one in that list – Always Encrypted.
 
With Always Encrypted the key to unlocking the encryption resides with the application. The encyption key is never passed to SQL Server.

God like administrators cannot view the data on the server in its unencrypted form. This provides a clear separation between the data managers and the data owners as to who can actually see the sensitive data. In this case, the data owners can view the data as they own the applications.

There are two types of encryption  :

  • Deterministic Encryption – where the same plain text value is stored in the column, the encrypted value will be the same. This allows grouping, filtering and joining by encrypted values but can expose patterns in the encryption which could open up opportunities for guessing what the values are beneath. The probability of successfully guessing values increases when the number of unique values is small.
  • Randomized Encryption – the opposite. Randomized encryption values makes the data more secure from pattern guessing however grouping, filtering and joining become impossible to do with any accuracy.

I’ve got the key, I’ve got the secret…..

The Always Encrypted Keys used by the applications come in two types :

  • Column Encryption Keys – these keys are used to encrypt sensitive data in columns and all values in a column can use the same key. Encrypted values of those keys are stored in system catalogs and it’s advised to have a secure backup of those keys too.
  • Column Master Keys – these keys protect the column encryption keys. They have to be stored in a trusted key store. Information, including location of the master keys is stored in system catalog views.

Who’s driving?

The Always Encrypted Driver does the work between the client and the database to either encrypt or decrypt the data accordingly. This happens transparently without any additional code needed to be added to the application.

How to enable Always Encrypted?

1/ Create a new database and a new table with some columns that you are wanting to encrypt
2/ You can then right mouse click on the database and choose “Tasks->Encrypt Columns”
3/ You’ll be presented with the Always Encrypted Wizard introduction screen, click “Next”
4/ Here you can choose which columns to encrypt, which encryption type and whether to create a new encryption key or use an existing one.

always encrypted encrypt columns wizard

5/ You’ll be presented with the Master Key configuration page where you can choose which column master key to use, where to store it (in a certificate store or in an Azure key vault) and the master key source.

always encrypted encrypt columns master key configuration

6/ The next screen gives you the option whether to proceed immediately or to create a Powershell script to run it later. Click through and it will do it’s bit and should come back that it has completed.

Before I encrypted the columns, I added some data to both the “Deterministic” and “Randomized” columns of my table. 10 rows were added with exactly the same data. The only difference being that the strings were different for each column.

So the data looked like this, added 10 times :

INSERT INTO AETest(Deterministic, Randomized)
VALUES('This is deterministic','This is randomized');

After the encryption, I queried the data to view the encrypted patterns. As you can see the two types of encryption are clearly visible with deterministic returning the same pattern for all 10 rows and random behaving, well, randomly. 🙂

always encrypted encrypt columns sample data

Related Posts:

  • sql grouping sets
    Using SQL GROUPING SETS for Multiple GROUP BY…
  • How to fix "conversion failed when converting date and/or time from character string"
    How to fix "conversion failed when converting date…

Filed Under: All Articles, SQL Server Administration Tagged With: security, sql server, sql server 2016

About Andy Hayes

Andy Hayes is a DBA working with SQL Server since version 7.0. He has a wonderful wife and two beautiful children. He loves database technology, playing cricket, and blogging. He is passionate about sharing his experiences as a DBA and learning more to further his understanding and knowledge. You can follow me on Twitter, check out my Facebook page or follow me on Google+

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar

Categories

  • All Articles (84)
  • Career Development (8)
  • MySQL Administration (18)
  • MySQL Performance (2)
  • SQL Server Administration (24)
  • SQL Server News (3)
  • SQL Server Performance (14)
  • SQL Server Security (3)
  • SQL Tips and Tricks (21)

Top 10 Popular Posts

  • Using sp_change_users_login to fix SQL Server orphaned users
  • MySQL SHOW USERS? – How to List All MySQL Users and Privileges
  • How to shrink tempdb
  • How to Transfer Logins to Another SQL Server or Instance
  • How to Delete Millions of Rows using T-SQL with Reduced Impact
  • T-SQL – How to Select Top N Rows for Each Group Using ROW_NUMBER()
  • New T-SQL features in SQL Server 2012 – OFFSET and FETCH
  • How to Kill All MySQL Processes For a Specific User
  • Using exec sp_who2 to help with SQL Server troubleshooting
  • How to fix “conversion failed when converting date and/or time from character string”

Recent Posts

  • How to fix “conversion failed when converting date and/or time from character string”
  • Using SQL GROUPING SETS for Multiple GROUP BY Queries in a Single Query
  • How to Setup MySQL Master Master Replication
  • How To Use SQL to Convert a STRING to an INT
  • How to set up MySQL Replication Tutorial

Search

Connect

  • Twitter
  • Facebook
  • RSS

About

  • Cookie Policy
  • Disclaimer
  • About
Copyright ©