Replication from table with column level encryption

Question

We create same certificate and symmetric key for Server A and B by running the scripts as

USE myDB;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MyPassword';
CREATE CERTIFICATE MyCertificate
  WITH SUBJECT = 'My Column Encryption Certificate';
CREATE SYMMETRIC KEY MySymmetricKey WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE MyCertificate

Encrypt the columns on server A. table MySensitiveData is replicated to AnotherDB on server B, when the columns were decrypted, the data becomed Null. I tried to restore the Master Key from server A to server B. decryption still didn't work

--Encrypt columns on server A
USE myDB 
OPEN SYMMETRIC KEY MySymmetricKey
  DECRYPTION BY MyCertificate;

UPDATE MySensitiveData
SET EncryptedSocialSecurityNumber = EncryptByKey(Key_GUID('MySymmetricKey'), SocialSecurityNumber),
    EncryptedCreditCardNumber = EncryptByKey(Key_GUID('MySymmetricKey'), CreditCardNumber);
CLOSE SYMMETRIC KEY MySymmetricKey

--Backup Master Key on server A
USE myDB 
BACKUP MASTER KEY TO FILE = 'C:\temp\myDBMasterKey.Key'     
ENCRYPTION BY PASSWORD = 'MyPassword'

--Decrypt the columns on serverB
USE anotherDB
OPEN SYMMETRIC KEY MySymmetricKey   DECRYPTION BY CERTIFICATE MyCertificate  
SELECT ID, FirstName, LastName,   CONVERT(NVARCHAR(50), DecryptByKey(EncryptedSocialSecurityNumber)) AS SocialSecurityNumber,   CONVERT(NVARCHAR(50), DecryptByKey(EncryptedCreditCardNumber)) AS CreditCardNumber 
FROM MySensitiveData 
CLOSE SYMMETRIC KEY MySymmetricKey

--Restore master key on server B
Use anotherDB 
RESTORE MASTER KEY FROM FILE = 'C:\temp\myDBMasterKey.Key'     
DECRYPTION BY PASSWORD =  'MyPassword'    
ENCRYPTION BY PASSWORD =  'MyPassword'    --FORCE

Answer 1

There was a similar question recently, and I posted this working example. On my first server, I ran:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MinHemlis!'

CREATE CERTIFICATE dscert WITH SUBJECT = 'Test of symmetric key'

CREATE SYMMETRIC KEY symkey WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE dscert

CREATE TABLE slasktbl (id int NOT NULL PRIMARY KEY, hemligtdata varbinary(80) )

OPEN SYMMETRIC KEY symkey DECRYPTION BY CERTIFICATE dscert

INSERT slasktbl(id, hemligtdata)
   VALUES(2,  EncryptByKey(Key_GUID('symkey'),'Detta är hemliga data'))

BACKUP DATABASE SillyDB TO DISK = 'C:\temp\SillyDB.bak' WITH COMPRESSION, INIT

CLOSE SYMMETRIC KEY symkey

BACKUP MASTER KEY TO FILE = 'C:\temp\SillyDB.Key' 
   ENCRYPTION BY PASSWORD = 'Mazarinexperten'

SELECT  ID, convert(varchar(40), DecryptByKey(hemligtdata))
FROM slasktbl

I copied the backup and the key file to the other server. The key file required some changes of ownership in Windows. I ran this on the other server:

RESTORE DATABASE SillyDB FROM DISK = 'C:\temp\SillyDB.bak'

RESTORE MASTER KEY FROM FILE = 'C:\temp\SillyDB.key'
   DECRYPTION BY PASSWORD = 'Mazarinexperten'
   ENCRYPTION BY PASSWORD = 'EnAnnanHemlis!'
   FORCE

OPEN SYMMETRIC KEY symkey DECRYPTION BY CERTIFICATE dscert

SELECT  ID, convert(varchar(40), DecryptByKey(hemligtdata))
FROM slasktbl

Answer 2

Here is a solution. The trick is that you need give a KEY_SOURCE and an IDENTITY_VALUE, this will produce in identical keys. See the script below. This is two databases on the same instance, but it should work in a replicated scenario as well. If you already have encrypted data, you will need to decrypt and re-encrypt it.

On SQL 2022 it's easier since you can backup and restore a symmetric key, so you then can copy it between servers.

USE tempdb

USE tempdb
go
DROP DATABASE IF EXISTS CryptoDB
DROP DATABASE IF EXISTS CryptoDB2
go
CREATE DATABASE CryptoDB
go
USE CryptoDB
go
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MinHemlis!'

CREATE CERTIFICATE dscert WITH SUBJECT = 'Test of symmetric key'

CREATE SYMMETRIC KEY symkey 
WITH ALGORITHM = AES_256, 
     KEY_SOURCE = 'More SQL to the people!',
     IDENTITY_VALUE = 'Even more SQL to the people!'
ENCRYPTION BY CERTIFICATE dscert

CREATE TABLE slasktbl (id int NOT NULL PRIMARY KEY, hemligtdata varbinary(810) )

OPEN SYMMETRIC KEY symkey DECRYPTION BY CERTIFICATE dscert


INSERT slasktbl(id, hemligtdata)
   VALUES(2,  EncryptByKey(Key_GUID('symkey'),'Detta är hemligt data'))

CLOSE SYMMETRIC KEY symkey

BACKUP MASTER KEY TO FILE = 'C:\temp\CryptoDB.Key' 
   ENCRYPTION BY PASSWORD = 'Mazarinexperten'

SELECT  id, convert(varchar(40), DecryptByKey(hemligtdata)), hemligtdata
FROM slasktbl
go
CREATE DATABASE CryptoDB2
go
USE CryptoDB2
go
RESTORE MASTER KEY FROM FILE = 'C:\temp\CryptoDB.Key'           
   DECRYPTION BY PASSWORD = 'Mazarinexperten'
   ENCRYPTION BY PASSWORD = 'EnAnnanHemlis!'
   FORCE
go
OPEN MASTER KEY DECRYPTION BY PASSWORD =   'EnAnnanHemlis!'

CREATE CERTIFICATE dscert2 WITH SUBJECT = 'Test of symmetric key'

CREATE SYMMETRIC KEY symkey 
WITH ALGORITHM = AES_256, 
     KEY_SOURCE = 'More SQL to the people!',
     IDENTITY_VALUE = 'Even more SQL to the people!'
ENCRYPTION BY CERTIFICATE dscert2

CREATE TABLE slasktbl (id int NOT NULL PRIMARY KEY, hemligtdata varbinary(810) )


INSERT slasktbl (id, hemligtdata)
   SELECT id, hemligtdata
   FROM   CryptoDB.dbo.slasktbl

OPEN SYMMETRIC KEY symkey DECRYPTION BY CERTIFICATE dscert2
 

SELECT  id, convert(varchar(40), DecryptByKey(hemligtdata)), hemligtdata
FROM slasktbl