1

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.

1

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.

  • agree :) but not answering my question :) – ben Apr 3 '18 at 14:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.