SQL Server 2016

Viewing Always Encrypted Data in SSMS

Intro

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
GO
SELECT TOP 5 * FROM [Sales].[CreditCard];

top5initialresults

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

GO

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…

rightclick_encrypt

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.

columnselection

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

mkconfig

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

results

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

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

top5encryptedresults

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

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

keys

We can also script out the DDL for those keys…

--Master Key
CREATE COLUMN MASTER KEY [CMK_Auto1]
WITH
(
	KEY_STORE_PROVIDER_NAME = N'MSSQL_CERTIFICATE_STORE',
	KEY_PATH = N'CurrentUser/my/F7D16243AF2D3AA32FAF06EC759F435AB44CB919'
)
GO

--Encryption Key
CREATE COLUMN ENCRYPTION KEY [CEK_Auto1]
WITH VALUES
(
	COLUMN_MASTER_KEY = [CMK_Auto1],
	ALGORITHM = 'RSA_OAEP',
	ENCRYPTED_VALUE = 0x016E000001630075007200720065006E00740075007300650072002F006D0079002F0066003700640031003600320034003300610066003200640033006100610033003200660061006600300036006500630037003500390066003400330035006100620034003400630062003900310039004CDE3BDBF014EAD0C066A97E5E93CCFFF36BE6F8A9B5EBB7F790659AD3CA626C48F0514D77C05052C4235A58BDF6908B054DF9DF9F01C2690AD89C5E71F5987A0054F913F161547DF740E7E9A3DF3695A8D0027BC5CAB779D1AB8CDDBED76649F56DC1EE107ACAC5A09BD4CCE8075707F0CCA3F00EF4376D428E56C35B12AEBF924978C8CA460F89F004AC1B9E0F58DBB3CE2B455DDC3F1C39526488A63AB7417CF0D5CD0D75E9F76F08736CF35DFF435BC1363C38C0FF3A0FB0C21D3CB4E0E3153F8C7928895E08F849274E3A16C146A48A4A9A0B574565D89E5727FA56ADFFFA77F50F6468FF21EBB03A3A7C432E18644D8EAFA91471E059729FEB9D6682A33E23A395B7915A425E24C75B1477302232C9657C1AE4F7ADF4439EC62E355944BFC9393458A319DF0191B2531E51965AA191D0EA1B4089DC5B426FB0511DD089ACE81F73A3AC3A45D11CA7F8EB5EC9026266DC596AB1E98A4F30194909BC464CF89978F0ABF6BF6BED36A0B5AC3ADC4B25C7AFCE3DC9AD0F0B20EE42871FA9713474C1D344879B6F0120B4FEDC6208296663C36823CDBA71AFBE1EDDBE87BCE451ED5A2ABD0275D203AA05310FAE4738FB7EEAF99CDB36C73BC3844224F96EDD9BFC5415575A42C54986775B36994C0B1C1F3B5C9EC51DF7D3F50B8CA74385A67E5549EF17DF010AAB0C7C529C5F82BC3C8837F0472C5CFF452CD2029AB46964
)
GO

…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 
		  (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Randomized, 
		  ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL,
	[ExpMonth] [tinyint] NOT NULL,
	[ExpYear] [smallint] NOT NULL,
	[ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

GO

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

connectoptions

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

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

top5initialresults

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:
manage-user-certificates

certmgr

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…

certexport1

I’ve had success selecting these options:

certexport2

Give the certificate a password if you wish:

certexport3

Choose a file name for the exported certificate:

certexport4

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

cert_file

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…

importcert1

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

importcert2

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

importcert3

Place the certificate in the Personal store.

importcert4

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.

finalresults

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

BOOM

Conclusion

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.

Advertisements

Use the Query Store In SQL Server 2016 To Improve HammerDB Performance (T-SQL Tuesday #79)


Improving HammerDB Performance with the New 2014 Cardinality Estimator


TSQL2SDAY-150x150

T-SQL Tuesday #79 is being hosted by Michael J. Swart this month. His prompt was to check out what’s new with SQL Server 2016. I figured I would play around with the Query Store and our old friend HammerDB.

This post is a follow-up of sorts to my post HAMMERDB AND THE NEW SQL SERVER 2014 CARDINALITY ESTIMATOR. Read that first to get a basic overview of the problem we are trying to solve. Come back once you are finished.

Basic synopsis of that article if you don’t want to read it for yourself:

  • With the new cardinality estimator introduced in SQL Server 2014, performance of the SLEV stored procedure used by HammerDB seriously regresses.
  • Adding Trace Flag 9481 to the statement that regressed in the stored procedure brings the performance back to the same levels as SQL Server 2012.

The Workload on SQL 2016

First, we need to figure out if the HammerDB workload performs similarly to when we first upgraded to SQL 2014. I’m going to follow the same steps that I took when upgrading the database to 2014, except I will be changing the compatibility level to 130 (the new 2016 level) and at the end I will be enabling the query store on the database. (For my specific HammerDB build instructions, check out that original 2014 post).

/*
Using this blog post from Thomas LaRock as a guideline for upgrading to 2014:
thomaslarock.com/2014/06/upgrading-to-sql-server-2014-a-dozen-things-to-check/
*/
USE [master]
GO

--Both data and log file on my C drive - not being fancy here
RESTORE DATABASE [tpcc]
FROM DISK = N'C:\2012INSTANCES\tpcc_blogtesting_10WH.bak'
WITH FILE = 1
,MOVE N'tpcc' TO N'C:\2016INSTANCES\DATA\tpcc.mdf'
,MOVE N'tpcc_log' TO N'C:\2016INSTANCES\LOGS\tpcc_log.ldf'
,NOUNLOAD
,STATS = 5
,REPLACE
GO

--Be sure to set the database compatibililty_level up to the newest 2016 version!
ALTER DATABASE [tpcc] SET COMPATIBILITY_LEVEL = 130;

USE [tpcc]
GO

DBCC CHECKDB WITH DATA_PURITY;

DBCC UPDATEUSAGE(tpcc);

USE [tpcc]
GO
EXEC sp_MSforeachtable @command1='UPDATE STATISTICS ? WITH FULLSCAN';

USE [tpcc]
GO
--Enable Query Store on tpcc database
ALTER DATABASE CURRENT SET QUERY_STORE = ON;

After the database is restored and upgraded to 2016, we can run the TPC-C workload. Here are the results and the perfmon graph from the TPC-C workload running on my local 2016 instance:

HammerDB Results: 32,497 Transactions Per Minute;  7,171 New Orders Per Minute

CPU Average: 76%; Batch Requests / Sec average: ~ 527

2016_defaultPerfMon

Here are the results from the same workload on SQL 2012, 2014, and 2016 all in one table. You can see that we have the same issue on SQL 2016 that we had when first upgrading to SQL 2014.

Workload      TPM NOPM %CPU Avg BatchRequests/Sec Avg
2012 (baseline) 114,901 25,035 35% 1,750
2014 (Pre TF Fix) 31,542 6,894 74% 492
2016 32,497 7,171 76% 527

Sure enough, when we check the plan cache for the most expensive queries based on total_worker_time, we see the same query we would expect rise to the top. It is also using the same plan as it was on SQL 2014.

orig_plancache

orig_plan

Can we use the new Query Store to force the ‘good 2012 plan’ for the query without modifying the stored procedure?

Let’s say, for whatever reason, that we don’t want to modify the stored procedure and manually insert the trace flag option onto the query. Is there still a way to force the ‘good plan’? Well, let’s find out.

First, I’m going to pull up the Top Resource Consuming Queries window in the Query Store.

query_store_top_queries

You can see that our problem query is incredibly easy to find in the top left window based on total duration. Also notice that in the top right Plan summary window, there is currently only one available plan for the query (plan_id 49).

We need to figure out how we can get our ‘good plan’ using Trace Flag 9481 as an available plan that we can force using the Query Store.

The Query Store Fix

Here is the code we can use to manually execute the SLEV stored procedure using TF9481 at the session level. This will get us back to the ‘good plan’ and hopefully the query store will recognize the ‘good plan’ as an available plan that we can force. (IMPORTANT: Set Options will need to be identical to the options the existing query in cache used. You can find these pretty easily by looking at the XML of the original plan in cache.)

 USE [tpcc]
GO

--SET OPTIONS MUST BE IDENTICAL TO THE EXISTING PLAN IN CACHE!!
    --(otherwise our 'good plan' won't show as available for the existing query)

SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET NUMERIC_ROUNDABORT OFF
SET QUOTED_IDENTIFIER ON


--clear the plan of the bad query using the plan_handle
DBCC FREEPROCCACHE(0x05000500CE07091B409F80E73401000001000000000000000000000000000000000000000000000000000000)


--enable TF 9481 at the session level to revert to the legacy 2012 Cardinality Estimator
DBCC TRACEON(9481)

BEGIN TRAN
EXEC [dbo].[SLEV]
  @st_w_id = 8,
  @st_d_id = 9,
  @threshold = 10
ROLLBACK TRAN --we can even rollback the proc to prevent any actual data modification

GO 10 --execute 10 times just for fun

--disable the TF
DBCC TRACEOFF(9481) 

Now let’s check the Query Store Top Resource Consumers window again:
query_store_top_queries_fixedPlan

Sure enough, the ‘good plan’ is listed as part of the Plan Summary for our problem query (plan_id 74). Now we can easily force this plan using the GUI or t-sql:

--For query_id 49, force plan_id 74
EXEC sys.sp_query_store_force_plan @query_id = 49, @plan_id = 74;

Results

Just so that we can get a cool perfmon graph, I’m going to start the HammerDB workload before forcing the ‘good plan’. After about 30 seconds or so, I’ll force the ‘good plan’ using the query store and we can see what impact it has on the workload performance.

2016_perfMon_PlanForce

You can see the plan was forced right around the 3:55:20 mark. It quickly brought batches / second up to around 1800 and close to the SQL Server 2012 benchmark that was in the original post.

The query is back down to around 700 reads per execution…

query_stats_fixed

…and here are the stats after running a full workload with the Query Store fix in place. We still aren’t all the way back to the 2012 baseline levels, but we are much closer.

Workload TPM NOPM %CPU Avg BatchRequests/Sec Avg
Query Store Fix 98,177 21,324 32% 1,633
2012 (baseline) 114,901 25,035 35% 1,750

Conclusion

With new versions of SQL Server come new tools, and the Query Store is a very powerful tool that can be useful when you need to force a plan based on a trace flag but don’t want to necessarily hard code the trace flag into the query or stored procedure. I can’t wait to start using the Query Store on production databases.

Once again, always be sure to test workloads and applications before upgrading a production database to a newer version of SQL Server – especially when going from 2012 or older to 2014 and newer.