Jul
27
2016

SQL Server 2016, Double or Nothing, Always Encrypted with temporal tables

Among the overwhelming amount of new features available for SQL Server 2016, there was one I really wanted to try, maybe because I have never worked with encryption further than hashing passwords for a website.

This feature is, you name it: Always Encrypted.

So I got to it and downloaded the new sample databases WideWorldImporters to start writing some queries, but a bit of background is always useful, rather than blindly try do something you have no idea 🙂

First thing that I found surprising is that there is no T-SQL to encrypt a column or columns… great! but there is GUI and powershell, which can be also generated from the GUI, so it’s not too bad time to learn some powershell (or not)

*Note that BOL is wrong and both, column master keys and column encryption keys can be created using T-SQL.
Please see comments for details

The easiest way to configure Always Encrypted is using the wizard so for the shake of simplicity, that is my first shot, but with such bad luck that I tried with a temporal table.

01_contextual_menu_disabled

Hm, disabled… let’s try the wizard from Database → Tasks → Encrypt columns

02_database_encrypt_columns

03_wizard_disabled

Again the wall. There is no way you can choose a temporal table and apply encryption to a column or columns using the wizard.

I tried then using the powershell (after manually creating the keys) as this is true the only way to encrypt existing columns, just in case.

# Generated by SQL Server Management Studio at 08:37 on 25/07/2016

Import-Module SqlServer
# Load reflected assemblies

[reflection.assembly]::LoadwithPartialName('System.Data.SqlClient') | Out-Null
[reflection.assembly]::LoadwithPartialName('Microsoft.SQLServer.SMO') | Out-Null
[reflection.assembly]::LoadwithPartialName('Microsoft.SQLServer.ConnectionInfo') | Out-Null

# Set up connection and database SMO objects

$sqlConnectionString = 'Data Source=localhost\MSSQL2016;Integrated Security=True;MultipleActiveResultSets=False;Encrypt=False;TrustServerCertificate=True;Packet Size=4096;Application Name="Microsoft SQL Server Management Studio"'
$sqlConnection = New-Object 'System.Data.SqlClient.SqlConnection' $sqlConnectionString
$serverConnection = New-Object 'Microsoft.SqlServer.Management.Common.ServerConnection' $sqlConnection
$smoServer = New-Object 'Microsoft.SqlServer.Management.Smo.Server' $serverConnection
$smoDatabase = $smoServer.Databases['WideWorldImporters']

# If your encryption changes involve keys in Azure Key Vault, uncomment one of the lines below in order to authenticate:
#   * Prompt for a username and password:
#Add-SqlAzureAuthenticationContext -Interactive

#   * Enter a Client ID, Secret, and Tenant ID:
#Add-SqlAzureAuthenticationContext -ClientID '<Client ID>' -Secret '<Secret>' -Tenant '<Tenant ID>'

# Change encryption schema

$encryptionChanges = @()

# Add changes for table [Application].[SystemParameters]
$encryptionChanges += New-SqlColumnEncryptionSettings -ColumnName Application.People.LogonName -EncryptionType Randomized -EncryptionKey CEK_Auto1

Set-SqlColumnEncryption -ColumnEncryptionSettings $encryptionChanges -InputObject $smoDatabase

04_powershell_error

System-versioned temporal tables with always encrypted columns are not supported.
What a disappointment!

Now it’s when my brain started boiling trying to find a way to make it work, because how nice would it be to be able to track changes regardless the data is encrypted or not.

 
Not always in SQL A+B = B+A

I know it’s kind of weird, but SQL is not maths and sometimes this happens.

If I cannot encrypt columns in a temporal table, can I make temporal a table that contains encrypted columns? Let’s see… First I’ll create a table and put some data, then we’ll encrypt a column.

USE [master]
GO
IF DB_ID('temporal_demo') IS NOT NULL BEGIN 
	ALTER DATABASE [temporal_demo] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
	DROP DATABASE [temporal_demo]
END
GO
CREATE DATABASE [temporal_demo]
GO


USE [temporal_demo]
GO
CREATE TABLE [dbo].[People](
	[PersonID] [int] NOT NULL PRIMARY KEY,
	[FullName] [nvarchar](50) NOT NULL,
	[LogonName] [nvarchar](50) NULL,
	[HashedPassword] [nvarchar](50) NULL,
	[ValidFrom] [datetime2](7) GENERATED ALWAYS AS ROW START NOT NULL,
	[ValidTo] [datetime2](7) GENERATED ALWAYS AS ROW END NOT NULL,
	PERIOD FOR SYSTEM_TIME ([ValidFrom], [ValidTo])
) ON [PRIMARY] 

GO

INSERT INTO dbo.[People] (PersonID, FullName, LogonName, HashedPassword)
	VALUES (1, 'john', 'jonny', 'J0nny!')
		, (2, 'Edward', 'eddy', '3DDy!')
GO

SELECT * FROM dbo.People
GO
SELECT name, temporal_type, temporal_type_desc FROM sys.tables 
GO

05_select_sys_tables

The table we just created looks very similar to a temporal table, but as you can see, it’s not.

This point is important, because we need to create our table with a similar structure as it was temporal to be able to convert it later on.

Now we can try encrypt a column from our table to see what happens.

06_encript_table

See how now the option is available, so we can proceed to encrypt our column.

07_encript_table_wizard

08_encript_table_wizard

09_encript_table_wizard

Just a few clicks later when we have finished the process, we can see how our data is actually encrypted.


SELECT * FROM dbo.People
GO

10_select_encrypted

You can observe how the data in the [HashedPassword] column is now encrypted.

 
Making the encrypted table a temporal table

The next step is to get the table with the encrypted column to hold historical changes as temporal tables do, so we have to ALTER TABLE as explained in BOL.

Remember we have the table ready with ROW START, ROW END and PERIOD FOR SYSTEM TIME, so the only we need to do now is to convert it.

ALTER TABLE [dbo].[People] SET (
SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.[People_Archive] , DATA_CONSISTENCY_CHECK = ON )
)
GO

UPDATE dbo.People SET LogonName = 'jonnybegood'
WHERE PersonID = 1
GO

UPDATE dbo.People SET LogonName = 'eddievanhalen'
WHERE PersonID = 2
GO

SELECT * FROM dbo.People
FOR SYSTEM_TIME ALL
GO

11_select_encrypted_temporal

You can see how there is no problems at all and both features work together as we were expecting.

 
Caveat

There is a big caveat though.

Once you have started this way, you won’t be able to encrypt any pre existing data as it will fail due to these two features “incompatibility”.

You can create new tables with encrypted columns and dump your existing data there, and it will work, but existing data won’t be encrypted.

 
Conclusion

Just to wrap up let’s see what is allowed and what is not.

  • Encrypt temporal table → Not allowed (by design)
  • Make temporal a table with encrypted columns → Allowed
  • Create temporal table with encrypted columns → Allowed
  • Encrypt existing columns if you have any temporal table with encrypted columns → Not Allowed

 
I can imagine why this two features work together when you build temporal on top of encrypted and not vice versa, but I’m happy I found the way to workaround it.

So far I found these caveats explained above, but if you see any other, please feel free to tell me and I’ll update this post.

I really look forward to your comments and keep investigating more about these awesome features.

Thanks for reading.

 

4 comments
  1. Jakub Szymaszek says:

    Thanks for the article and bringing this issue up. I’m part of the SQL Server product team. The database engine in SQL Server 2016 supports Always Encrypted in temporal tables, but SQL tools (SSMS, PowerShell, DAC Framework) do not currently support encrypting/decrypting columns in temporal tables. We are actively working on enabling that in one of the upcoming monthly releases of SQL tools.

    Also, on your comment that “column master keys and column encryption keys can be created using T-SQL”, I’d like to clarify that T-SQL only supports creating key metadata. The full process of provisioning the keys also involves creating the actual (physical) keys, which is not supported in T-SQL that is what we meant in the document. We will try to make it clearer. Thanks for your feedback.

    • Raul says:

      Thanks Jakub for your comment, it does shed some light there.

      Since these 2 are new features for 2016, I guess it’s just a matter of planning and applying both in the order that now works (in new developments) but it’s great to know you’re working on it!

      Thanks again and cheers for the SQL team[s]!

  2. Gustavo Maia says:

    Great post!

    Did you try to run a temporal query against this table to see if the decryption works properly? I suppose it should work…

    This two new features are really awesome! Thanks for sharing!

    • Raul says:

      Thanks for your comment. Yes querying the temporal table will decrypt both, the current data plus the archived data.
      You can see the last query uses FOR SYSTEM_TIME ALL works but shows obviously encrypted values.
      But if you modify your connection string to use “Column Encryption Setting=enabled” you’ll see how all values are decrypted.

Leave a Reply

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