Viewing Always Encrypted Data in SSMS


One of the many new features introduced in SQL Server 2016 is Always Encrypted. In the words of Microsoft: “Always Encrypted provides a separation between those who own the data (and can view it) and those who manage the data (but should have no access).”

If there is PII in a table or database, all Database Administrators (or anyone tasked with administering that database that have the enough permissions) can view that data. Generally, those who are hired to manage that database should be trusted to not go digging through personal information; however, with Always Encrypted, you can completely eliminate having to even think about who may or may not have access to the sensitive data. Because having access to an encryption certificate is needed to view the data, it becomes very easy to control who has access.

As a DBA, I can say that not even having to worry about what data I may or may not see is a huge relief.

In this post, I will cover:
1. How to encrypt a column with sensitive data using the GUI in SSMS.
2. How to view Always Encrypted Data from SSMS on the machine where the Always Encrypted certificate was generated.
3. How to export/import the Always Encrypted certificate so that Always Encrypted data can be viewed from multiple machines.

Encrypting a Column

I will be using the trusty AdventureWorks database for this demonstration (I know, I know. WorldWideImporters is sooo much better), and I will be looking at the Sales.CreditCard table.

Here is what the data in that CreditCard table looks like. Our goal will be to encrypt the CardNumber column.

Use AdventureWorks
SELECT TOP 5 * FROM [Sales].[CreditCard];


First, let’s create a quick copy the CreditCard table. We’ll copy it over to a new table: CreditCard_Encrypted.

--create copy of the CreditCard table
DROP TABLE IF EXISTS Sales.CreditCard_Encrypted

Select * 
INTO Sales.CreditCard_Encrypted
from Sales.CreditCard


Since I am a lazy millennial, I’m just going to use the GUI to encrypt the CardNumber column.

First, right-click on the table and choose Encrypt Columns…


Next, select the column(s) you want to encrypt and choose the Encryption Type (Deterministic or Randomized) and the Encryption Key you want to use. I’m just going to be generating a new key for this demonstration.


Choose the Column Master Key. Once again, I’m generating a new master key and storing it in the Windows Certificate store.


Finish navigating through the next few screens and finish the Encryption tasks:


Once that is complete, we can try querying the CreditCard_Encrypted table:

Use AdventureWorks
SELECT TOP 5 * FROM [Sales].[CreditCard_Encrypted];


Our CardNumber column is now encrypted! That wasn’t too difficult.

We can also see the Master Key and Encryption Key that were created:


We can also script out the DDL for those keys…

--Master Key
	KEY_PATH = N'CurrentUser/my/F7D16243AF2D3AA32FAF06EC759F435AB44CB919'

--Encryption Key
	ENCRYPTED_VALUE = 0x016E000001630075007200720065006E00740075007300650072002F006D0079002F0066003700640031003600320034003300610066003200640033006100610033003200660061006600300036006500630037003500390066003400330035006100620034003400630062003900310039004CDE3BDBF014EAD0C066A97E5E93CCFFF36BE6F8A9B5EBB7F790659AD3CA626C48F0514D77C05052C4235A58BDF6908B054DF9DF9F01C2690AD89C5E71F5987A0054F913F161547DF740E7E9A3DF3695A8D0027BC5CAB779D1AB8CDDBED76649F56DC1EE107ACAC5A09BD4CCE8075707F0CCA3F00EF4376D428E56C35B12AEBF924978C8CA460F89F004AC1B9E0F58DBB3CE2B455DDC3F1C39526488A63AB7417CF0D5CD0D75E9F76F08736CF35DFF435BC1363C38C0FF3A0FB0C21D3CB4E0E3153F8C7928895E08F849274E3A16C146A48A4A9A0B574565D89E5727FA56ADFFFA77F50F6468FF21EBB03A3A7C432E18644D8EAFA91471E059729FEB9D6682A33E23A395B7915A425E24C75B1477302232C9657C1AE4F7ADF4439EC62E355944BFC9393458A319DF0191B2531E51965AA191D0EA1B4089DC5B426FB0511DD089ACE81F73A3AC3A45D11CA7F8EB5EC9026266DC596AB1E98A4F30194909BC464CF89978F0ABF6BF6BED36A0B5AC3ADC4B25C7AFCE3DC9AD0F0B20EE42871FA9713474C1D344879B6F0120B4FEDC6208296663C36823CDBA71AFBE1EDDBE87BCE451ED5A2ABD0275D203AA05310FAE4738FB7EEAF99CDB36C73BC3844224F96EDD9BFC5415575A42C54986775B36994C0B1C1F3B5C9EC51DF7D3F50B8CA74385A67E5549EF17DF010AAB0C7C529C5F82BC3C8837F0472C5CFF452CD2029AB46964

…and we can script out the CREATE statement for the table to see what the Always Encrypted syntax looks like:

CREATE TABLE [Sales].[CreditCard_Encrypted](
	[CreditCardID] [int] IDENTITY(1,1) NOT NULL,
	[CardType] [nvarchar](50) NOT NULL,
	[CardNumber] [nvarchar](25) COLLATE Latin1_General_BIN2 ENCRYPTED WITH 
	[ExpMonth] [tinyint] NOT NULL,
	[ExpYear] [smallint] NOT NULL,
	[ModifiedDate] [datetime] NOT NULL


So what do we do if we actually want to view the encrypted data in SSMS?

We’ll need to pass in this connection parameter in the SSMS connection options: Column Encryption Setting = Enabled


Now I’ll reconnect and try to select from the Encrypted table once again:

Use AdventureWorks
SELECT TOP 5 * FROM [Sales].[CreditCard];


I am now able to view the encrypted data. Why is this?

Because I am connecting to the database from the database server, I have access to the Encryption Certificate that was generated. I can verify this by opening certmgr.msc and browsing to Personal -> Certificates:


Without access to that certificate, I would not be able to see the encrypted data.

We can test this by attempting to connect to the database with the encrypted table from another machine and passing in the “Column Encryption Setting = Enabled” connection option in SSMS.

When trying to do a select on CreditCard_Encrypted, we receive this error message:

Msg 0, Level 11, State 0, Line 0
Failed to decrypt column 'CardNumber'.
Msg 0, Level 11, State 0, Line 0
Failed to decrypt a column encryption key using key store provider: 'MSSQL_CERTIFICATE_STORE'. The last 10 bytes of the encrypted column encryption key are: '36-B6-E0-8F-9F-54-AC-CF-9B-CB'.
Msg 0, Level 11, State 0, Line 0
Certificate with thumbprint 'BA85A469AE80E4001B1FD9E71D89165593DE9009' not found in certificate store 'My' in certificate location 'CurrentUser'. Verify the certificate path in the column master key definition in the database is correct, and the certificate has been imported correctly into the certificate location/store.
Parameter name: masterKeyPath

What about viewing the encrypted data from SSMS on another machine?

So how can we get a copy of the Always Encrypted certificate over to the other machine?

We must go back to the original machine or server where the certificate was generated. Re-open certmgr.msc, browse to the Always Encrypted Certificate, right-click -> All Tasks -> Export…


I’ve had success selecting these options:


Give the certificate a password if you wish:


Choose a file name for the exported certificate:


Finish the export wizard and browse to the destination folder. We now have a copy of the Always Encrypted certificate.


The certificate can now be copied to the secondary machine that needs access to the encrypted data. Open ‘Manage User Certificates’ (AKA certmgr.msc). Browse to Personal -> Certificates. Right click on the Certificates folder -> All Tasks -> Import…


Browse to and select the Always Encrypted certificate that was copied from the database server:


Enter the password if one was used. Also, check Include all extended properties.


Place the certificate in the Personal store.


Step through the final screens and finish the import process.

After the import is finished, open certmgr.msc and ensure the Always Encrypted certificate shows up under Personal -> Certificates.

Now that the certificate has been imported, we can try to query the Encrypted table again.


BOOM! We can now query the encrypted data on a different machine with SSMS.



In this blog post, I’ve shown how to easily encrypt a column using Always Encrypted. I’ve also shown how to enable users using SSMS to view that encrypted data.

I’m pretty excited about Always Encrypted and think that it could potentially be a great solution for encrypting sensitive data.


Free Tools for the SQL Server DBA

Let me preface this by saying that this is my first ever blog post of any kind. I decided to make this blog to document “interesting” experiences or situations that may arise during my day-to-day work as a DBA. This blog is as much for me as it is for anyone who decides to read it.

I decided to make my very first post about free tools that I use to help solve various problems, whether it be performance tuning or routine maintenance tasks. These tools are completely free – so you have no excuse to not be using them.

I will try to maintain this list on this page, but without the details.



DBA’s need to pinpoint exactly what is running slow on a database and sometimes do it very quickly. Developers love the “Hey why is my app running slow?” or “OMGWTH – Why haven’t you fixed that POS database yet?” questions. sp_WhoIsActive lets you see exactly what queries are running, how long they have been running, and what they are waiting on currently. WhoIsActive also has a lot of extra parameters you can pass to it to find even more info. Some of my favorites are @Get_plans, @find_block_leaders, and @get_avg_time.

I could write an entire post just about using WhoIsActive, so I’ll point you to some much better blogs. Brent Ozar did a very good post on WhoIsActive and Adam Mechanic (the creator of WhoIsActive) did a whole blog series on using the procedure.

Pro Tip: Install WhoIsActive on all servers and create a Keyboard Query Shortcut in SSMS (I use Ctrl+F1) to execute sp_WhoIsActive in any open query window.


SQLQueryStress is a very handy tool, and coincidentally, it was also created by Adam Mechanic. Let’s say you are trying to tune a particular query, whether it be by re-writing the query or by adding indexes. SQLQueryStress provides a very quick look at how the query is performing by showing seconds per iteration, total logical reads per iteration, and CPU seconds per iteration. This removes the step of having to add up the STATISTICS IO output to figure out the total number of reads the query is doing.

It can also do some very cool things with parameter substitution. It is very important when tuning a query to make sure the query is faster with many different parameters, not just a single set of parameters, and SQLQueryStress will help automate that process for you.


SQL Load Generator:

Every need to generate a query load on your SQL Server? SQL Load Generator provides a quick and easy way of doing this. It’ll let you run many queries simultaneously with different logins and different application names. Enough said.


SQL Sentry Plan Explorer:

After using Plan Explorer, it is the only way that I will analyze execution plans anymore. SSMS is clunky and can make it challenging to analyze more complex execution plans. Plan explorer helps to immediately identify the most expensive operators or subtrees. Bottom line: Plan Explorer makes query analysis much more efficient and it looks so much better than viewing a plan in SSMS.

Aaron Bertrand did a great post on using Plan Explorer here.


Ever need to quickly analyze a trace file? ClearTrace will import a SQL Server trace file into a database of your choosing, summarize the data, and graphically display performance information in an easy-to-digest format. ClearTrace can sort by whichever column you want, making it really easy to find either the most I/O or CPU intensive queries.


Redgate SQL Search:

SQL Search is a SSMS add-in that does exactly what it sounds like: it will search through objects (tables, views, stored procedures, functions, etc.) in a database for a snippet of text. This is helpful when trying to find everything that may reference a table or any other given object.



NimbleText is great if you write a lot of SQL statements. If you do any type of repetitive typing or text editing, NimbleText can probably help.


StatisticsIOParser (developed by Richie Rump) is a great way to deal with the obnoxious output of SET STATISTICS IO. It formats and sums up the totals of the output. Very simple and very helpful.


PoorSQL is an online SQL formatter. It makes ugly SQL look pretty. (Very similar affect as that third cocktail you drank at the bar the other night – only you probably weren’t looking at SQL)

Pro Tip: Download the SSMS plugin to format SQL in your own query windows.PoorSQL

Did I forget a free tool or website? Is there a great free tool I’m not using, and definitely should be? Let me know in the comments.