How To View Always Encrypted Data in SQL Server Management Studio

One of the shiny new features in SQL Server 2016 is Always Encrypted. I think Always Encrypted is a great addition to SQL Server (and Azure SQL Database) and a step in the right direction for data security. The last data security feature added to SQL Server was Transparent Data Encryption (TDE) and that was just about ten years ago. So, we were due for some new features.

Configuring a column for encryption is easy enough (providing it meets the long list of supported columns listed here), and here’s an example of what the result looks like:

The NationalIDNumber is encrypted, and shows only the ciphertext in the results window inside SQL Server Management Studio (SSMS). Note that I am only seeing the ciphertext even though I am logged in as a member of the system administrators role. The reason for this is because I need to connect to the database server with the ‘Column Encryption Setting = Enabled’ parameter.

Well, that’s easy enough to do, I can just change the connection and select the ‘Options’ in the lower right:

Doing so will reveal a new screen with three tabs. Click on the ‘Additional Connection Parameters’ and type in the parameter:

And, if we run the query again, we see the unencrypted data:

The reason I can see this data is because the column master key exists on the database server. You can see the one that was created by me for this example:

That certificate wasn’t there before. And, it’s worth noting that the name says ‘Always Encrypted’. If an attacker got access to the server they *may* be able to search for that string and try to export the certificates. But I don’t want to you to think that this is likely, because if a person really wants access to the data they are going to get it from Jim down the hall who is only to happy to run reports for people when asked. But I digress…

The real point here is that without that master column key, I will not be able to see the data. This is how Always Encrypted works. You export the column master key certificate to the client(s) and then the client connects using the connection string parameter and you are done (assuming the necessary database permissions to query the data).

But look at what happens when I query the AdventureWorks2016CTP3.Sales.CustomerPII table using the same connection:

I get the following error message:

Msg 0, Level 11, State 0, Line 0
Failed to decrypt column 'SSN'.
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: 'F7-B0-8B-E6-79-EA-D4-E5-7A-D7'.
Msg 0, Level 11, State 0, Line 0
Certificate with thumbprint '8C5AE6DCC176752931B33BFE03B7E4EA3A73572C' 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

I don’t have the column master key for this data, so I won’t be able to view it. Ever.

I can’t decrypt it either, also because I don’t have the column master key.

And unless the person at Microsoft that deployed AdventureWorks2016CTP3 gives me the key, I never will.

So, I’d say that ‘Always Encrypted’ is a perfect description for this new feature in SQL Server 2016. Unlike TDE which will allow for me to migrate encrypted data without having the key, Always Encrypted won’t allow anyone to see the data without having the certificate installed.