Replication from table with column level encryption

szh8866-8371 40 Reputation points
2024-01-24T18:44:42.61+00:00

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


SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,225 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Erland Sommarskog 98,751 Reputation points
    2024-01-24T22:27:48.7833333+00:00

    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
    
    1 person found this answer helpful.

  2. Erland Sommarskog 98,751 Reputation points
    2024-01-26T18:04:42.19+00:00

    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