{"id":271,"date":"2016-07-27T10:51:57","date_gmt":"2016-07-27T09:51:57","guid":{"rendered":"https:\/\/sqldoubleg.live-website.com\/?p=271"},"modified":"2016-07-30T20:44:19","modified_gmt":"2016-07-30T19:44:19","slug":"sql-server-2016-double-or-nothing-always-encrypted-with-temporal-tables","status":"publish","type":"post","link":"https:\/\/www.sqldoubleg.com\/es\/2016\/07\/27\/sql-server-2016-double-or-nothing-always-encrypted-with-temporal-tables\/","title":{"rendered":"SQL Server 2016, Double or Nothing, Always Encrypted with temporal tables"},"content":{"rendered":"

This week’s post I’ll talk about two new features that don’t seem to get on well. Let’s make them behave and work together as a team  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.<\/p>\n

This feature is, you name it: Always Encrypted.<\/p>\n

So I got to it and downloaded the new sample databases WideWorldImporters <\/a> to start writing some queries, but a bit of background is always useful, rather than blindly try do something you have no idea \ud83d\ude42<\/p>\n

First thing that I found surprising is that there is no T-SQL to encrypt a column or columns<\/a>… 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)<\/p>\n

*Note that BOL is wrong and both, column master keys and column encryption keys can be created using T-SQL.<\/del><\/strong>
\nPlease see comments for details<\/strong><\/p>\n

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.<\/p>\n

\"01_contextual_menu_disabled\"<\/a><\/p>\n

Hm, disabled… let’s try the wizard from Database → Tasks → Encrypt columns <\/p>\n

\"02_database_encrypt_columns\"<\/a><\/p>\n

\"03_wizard_disabled\"<\/a><\/p>\n

Again the wall. There is no way you can choose a temporal table and apply encryption to a column or columns using the wizard.<\/p>\n

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. <\/p>\n

\r\n# Generated by SQL Server Management Studio at 08:37 on 25\/07\/2016\r\n\r\nImport-Module SqlServer\r\n# Load reflected assemblies\r\n\r\n[reflection.assembly]::LoadwithPartialName('System.Data.SqlClient') | Out-Null\r\n[reflection.assembly]::LoadwithPartialName('Microsoft.SQLServer.SMO') | Out-Null\r\n[reflection.assembly]::LoadwithPartialName('Microsoft.SQLServer.ConnectionInfo') | Out-Null\r\n\r\n# Set up connection and database SMO objects\r\n\r\n$sqlConnectionString = 'Data Source=localhost\\MSSQL2016;Integrated Security=True;MultipleActiveResultSets=False;Encrypt=False;TrustServerCertificate=True;Packet Size=4096;Application Name="Microsoft SQL Server Management Studio"'\r\n$sqlConnection = New-Object 'System.Data.SqlClient.SqlConnection' $sqlConnectionString\r\n$serverConnection = New-Object 'Microsoft.SqlServer.Management.Common.ServerConnection' $sqlConnection\r\n$smoServer = New-Object 'Microsoft.SqlServer.Management.Smo.Server' $serverConnection\r\n$smoDatabase = $smoServer.Databases['WideWorldImporters']\r\n\r\n# If your encryption changes involve keys in Azure Key Vault, uncomment one of the lines below in order to authenticate:\r\n#   * Prompt for a username and password:\r\n#Add-SqlAzureAuthenticationContext -Interactive\r\n\r\n#   * Enter a Client ID, Secret, and Tenant ID:\r\n#Add-SqlAzureAuthenticationContext -ClientID '<Client ID>' -Secret '<Secret>' -Tenant '<Tenant ID>'\r\n\r\n# Change encryption schema\r\n\r\n$encryptionChanges = @()\r\n\r\n# Add changes for table [Application].[SystemParameters]\r\n$encryptionChanges += New-SqlColumnEncryptionSettings -ColumnName Application.People.LogonName -EncryptionType Randomized -EncryptionKey CEK_Auto1\r\n\r\nSet-SqlColumnEncryption -ColumnEncryptionSettings $encryptionChanges -InputObject $smoDatabase\r\n\r\n<\/pre>\n
\"04_powershell_error\"<\/a><\/p>\n
System-versioned temporal tables with always encrypted columns are not supported.<\/span>
\nWhat a disappointment! <\/p>\n
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.<\/p>\n
 
\nNot always in SQL A+B = B+A<\/strong><\/p>\n
I know it’s kind of weird, but SQL is not maths and sometimes this happens.<\/p>\n
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.<\/p>\n
\r\nUSE [master]\r\nGO\r\nIF DB_ID('temporal_demo') IS NOT NULL BEGIN \r\n\tALTER DATABASE [temporal_demo] SET SINGLE_USER WITH ROLLBACK IMMEDIATE\r\n\tDROP DATABASE [temporal_demo]\r\nEND\r\nGO\r\nCREATE DATABASE [temporal_demo]\r\nGO\r\n\r\n\r\nUSE [temporal_demo]\r\nGO\r\nCREATE TABLE [dbo].[People](\r\n\t[PersonID] [int] NOT NULL PRIMARY KEY,\r\n\t[FullName] [nvarchar](50) NOT NULL,\r\n\t[LogonName] [nvarchar](50) NULL,\r\n\t[HashedPassword] [nvarchar](50) NULL,\r\n\t[ValidFrom] [datetime2](7) GENERATED ALWAYS AS ROW START NOT NULL,\r\n\t[ValidTo] [datetime2](7) GENERATED ALWAYS AS ROW END NOT NULL,\r\n\tPERIOD FOR SYSTEM_TIME ([ValidFrom], [ValidTo])\r\n) ON [PRIMARY] \r\n\r\nGO\r\n\r\nINSERT INTO dbo.[People] (PersonID, FullName, LogonName, HashedPassword)\r\n\tVALUES (1, 'john', 'jonny', 'J0nny!')\r\n\t\t, (2, 'Edward', 'eddy', '3DDy!')\r\nGO\r\n\r\nSELECT * FROM dbo.People\r\nGO\r\nSELECT name, temporal_type, temporal_type_desc FROM sys.tables \r\nGO\r\n<\/pre>\n
\"05_select_sys_tables\"<\/a><\/p>\n
The table we just created looks very similar to a temporal table, but as you can see, it’s not. <\/p>\n
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.<\/p>\n
Now we can try encrypt a column from our table to see what happens.<\/p>\n
\"06_encript_table\"<\/a><\/p>\n
See how now the option is available, so we can proceed to encrypt our column.<\/p>\n
\"07_encript_table_wizard\"<\/a><\/p>\n
\"08_encript_table_wizard\"<\/a><\/p>\n
\"09_encript_table_wizard\"<\/a><\/p>\n
Just a few clicks later when we have finished the process, we can see how our data is actually encrypted.<\/p>\n
\r\n\r\nSELECT * FROM dbo.People\r\nGO\r\n<\/pre>\n
\"10_select_encrypted\"<\/a><\/p>\n
You can observe how the data in the [HashedPassword] column is now encrypted.<\/p>\n
 
\nMaking the encrypted table a temporal table<\/strong><\/p>\n
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<\/a>. <\/p>\n
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.<\/p>\n
\r\nALTER TABLE [dbo].[People] SET (\r\nSYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.[People_Archive] , DATA_CONSISTENCY_CHECK = ON )\r\n)\r\nGO\r\n\r\nUPDATE dbo.People SET LogonName = 'jonnybegood'\r\nWHERE PersonID = 1\r\nGO\r\n\r\nUPDATE dbo.People SET LogonName = 'eddievanhalen'\r\nWHERE PersonID = 2\r\nGO\r\n\r\nSELECT * FROM dbo.People\r\nFOR SYSTEM_TIME ALL\r\nGO\r\n\r\n<\/pre>\n
\"11_select_encrypted_temporal\"<\/a><\/p>\n
You can see how there is no problems at all and both features work together as we were expecting.<\/p>\n
 
\nCaveat<\/span><\/strong><\/strong><\/p>\n
There is a big caveat though. <\/p>\n
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 \u00abincompatibility\u00bb.<\/p>\n
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.<\/p>\n
 
\nConclusion<\/strong><\/p>\n
Just to wrap up let’s see what is allowed and what is not.<\/p>\n