Always encrypted on Temporal tables

always-encryptedazure-sql-databasetemporal-tables

Is it now possible to apply Always encrypted to some columns on existing Azure SQL database temporal tables with existing data ? I know it was not possible a few months ago thru SSMS / powershell.

Best Answer

At least it is now possible have encrypted columns on temporal tables:

CREATE TABLE DepartmentHistory   
(    
     DeptID int NOT NULL  
   , DeptName varchar(50) NOT NULL  
   , SysStartTime datetime2 NOT NULL  
    ,[Customer_credit_card_asym] [varbinary](max) NULL
   , SysEndTime datetime2 NOT NULL   
);   
GO   

CREATE NONCLUSTERED INDEX IX_DepartmentHistory_ID_PERIOD_COLUMNS   
   ON DepartmentHistory (SysEndTime, SysStartTime, DeptID);   
GO   
CREATE TABLE Department   
(    
    DeptID int NOT NULL PRIMARY KEY CLUSTERED  
   , DeptName varchar(50) NOT NULL  
   , SysStartTime datetime2  NOT NULL  
    ,Customer_credit_card_asym [varbinary](max) NULL
   , SysEndTime datetime2 NOT NULL           
) ;

CREATE ASYMMETRIC KEY dboAsymKey AUTHORIZATION dbo
WITH ALGORITHM = RSA_2048
ENCRYPTION BY PASSWORD =N'MorilloP@zzwurd';

DECLARE @credit_card1 VARCHAR(100) = '111-1111111-1'

INSERT into Department (DeptId, DeptName, SysStartTime,
Customer_credit_card_asym, SysEndTime)
VALUES
(1, 'Perez' , '2/2/2018 00:00:00.000', EncryptByAsymKey(AsymKey_ID('dboAsymKey'),@credit_card1),
'9999-12-31 23:59:59.9999999')


ALTER TABLE dbo.Department   
   ADD PERIOD FOR SYSTEM_TIME ([SysStartTime], [SysEndTime])   
ALTER TABLE dbo.Department      
   SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DepartmentHistory, DATA_CONSISTENCY_CHECK = ON));

A few years back Msg 13579 was received.

Related Question