Column Level Encryption question with T-Replication

Question

I have Database T-replication  from publisher server 1-My db to subscriber server 2 -my db, replication was working good and no issues.

We recently added column level encryption to one of our replicated table-Accountnumber column on server -1 Publisher my db to server 2-mydb subscriber...

use mydb

go
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongPassword;
GO
CREATE CERTIFICATE myCertificate  
WITH SUBJECT = 'Certificate Data Encryption';
GO

CREATE SYMMETRIC KEY   SecretKey 
     WITH ALGORITHM   = AES_256 
     ENCRYPTION BY   CERTIFICATE myCertificate  
GO

here at publisher data is encrypted in accountnumber column and gets replicated to subscriber....

I created  same master key,certificate, secret key on subscriber server....

i was able to decrypt the data at publisher easily , but when i go to subscriber and use the same query to decrypt i am getting NULL values....

use mydb
go
open symmetric key SecretKey 
decryption by certificate myCertificate  

SELECT DataSecretID, D.Value,CONVERT(nVARCHAR(MAX),DECRYPTBYKEY(Value)) AS AccountNumber 
 FROM Tablename D

close symmetric key SecretKey 

I get 

DataSecretID,  D.Value,              AccountNumber 

1111                binarydata          NULL 

1112              binarydata          NULL 

1113               binarydata          NULL 

I dropped  master key,certificate, secret key on subscriber server....and created again..

but no lock 

https://msdn.microsoft.com/en-us/library/bb326115(v=sql.100).aspx

followed that and no luck

select 
    name,cast(key_guid as varbinary(max)) as key_guid
from sys.symmetric_keys

Publisher:

name        key_guid

SecretKey  0x004AB681AB260845AEFEAACD3A136CE2

Subscriber:

name        key_guid

SecretKey  0x00C32FDE2542AB48AE8D3882C560ED7E

key_guid mismatch between publisher and subscriber , but i wa not able to update at subscriber( thought that could help)

Plz help on how to get read the data at subscriber

Thanks,

Answer 1

did you look at this?

You have to use the key_source and identity_value properties.

https://technet.microsoft.com/en-us/library/bb326115%28v=sql.110%29.aspx?f=255&MSPPError=-2147217396

Answer 2

Thanks Hilary,

Fyi..

1. Without key_source and identity_value  it worked on publisher

2, i dropped and re created on Subscriber with key_source and identity_value but same issue 

all NULLS.

Answer 3

This should be working - what version of sql are you running on your publisher and subscriber?

Answer 4

I tried couple of times but didn't work

Publisher:Microsoft SQL Server 2014 - 12.0.4100.1 (X64) 
Standard Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )


Subscriber:Microsoft SQL Server 2012 - 11.0.5343.0 (X64) 
Standard Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)

do you think the versions matter with column level encryption?

Thanks Hilary

           

Answer 5

It should not matter, but I am going to try to repro it using your same version and editions to see if I get the same problem.

Answer 6

How did you created the same keys on the other side (export to file->import/create from file)? I doubt they are the same.

Answer 7

Hey Vedran, like i said above,I created with T sql, Run below commands on publisher and subscriber...Should i have to back up at publisher and restore on Subscriber?
use mydb
go
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongPassword;
GO
CREATE CERTIFICATE myCertificate  
WITH SUBJECT = 'Certificate Data Encryption';
GO
CREATE SYMMETRIC KEY   SecretKey 
     WITH ALGORITHM   = AES_256 
     ENCRYPTION BY   CERTIFICATE myCertificate  
GO




How can i find they are same or not? is there  away to find?
I tried below 
select 
    name,cast(key_guid as varbinary(max)) as key_guid
from sys.symmetric_keys

Publisher:

name        key_guid

SecretKey  0x004AB681AB260845AEFEAACD3A136CE2


Subscriber:

name        key_guid

SecretKey  0x00C32FDE2542AB48AE8D3882C560ED7E

key_guid mismatch between publisher and subscriber , but i wa not able to update at subscriber( thought that could help)







 

Answer 8

If one could copy a certificate just by creating a new one with the same name on the other server, hacker's life would be super-easy :) Yes, transfer the certificate by backing it up to a file on one server, and then restore (create from file) on the other server.

For the symmetric key, here is a good description on have to transfer it to other server:

http://stackoverflow.com/questions/2568913/how-can-i-duplicate-a-sql-server-symmetric-key

Compare the thumbprints to make sure they are the same keys and certificates:

select name, cert_serial_number, thumbprint from sys.certificates
select name, key_thumbprint from sys.symmetric_keys

Answer 9

Thanks a lot vedran for all the input.

1. I created Symmetric keys without IDENTITY_VALUE, and KEY_SOURCE on Publisher and data is encrypted with my symmetric keys already and i am very afraid to change that is a huge risk for me..

2. I took backup of keys on publisher and restored on Subscriber ( as shown below on subscriber)

   1.CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'strongpassword;

2.create certificate myCertificate  
 from file = 'G:\test\Certificate_may12.dat'
with private key(file = 'G:\test\CertificateKey_may12.dat',
decryption by password = 'strongpassword')
go
3.CREATE SYMMETRIC KEY   SecretKey  
     WITH ALGORITHM   = AES_256  
     ENCRYPTION BY   CERTIFICATE myCertificate;
GO

3. o/p below looks exact same ( except Key_Thumbprint null on both servers)

Select name, cert_serial_number, thumbprint from sys.certificates
select name, key_thumbprint from sys.symmetric_keys

4. But, still the o/p is null.

use mydb
go
open symmetric key SecretKey 
decryption by certificate myCertificate  

SELECT DataSecretID, D.Value,CONVERT(nVARCHAR(MAX),DECRYPTBYKEY(Value)) AS AccountNumber 
 FROM Tablename D

close symmetric key SecretKey 

Plz advice

Answer 10

i found differences in symmetric_key_id and key_guid between publisher and subscriber and i know we cannot update sys.symmetric_keys... any idea what could be done to solve my problem..

select name, symmetric_key_id,key_guid from sys.symmetric_keys

Answer 11

This works for me.

Answer 12

Publisher code:

create database mydb
GO
use mydb
go
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongPassword'
GO
CREATE CERTIFICATE myCertificate  
WITH SUBJECT = 'Certificate Data Encryption';
GO
CREATE SYMMETRIC KEY   SecretKey 
WITH KEY_SOURCE = 'My key generation bits. This is a shared secret!',
ALGORITHM = AES_256, 
IDENTITY_VALUE = 'Key Identity generation bits. Also a shared secret'
ENCRYPTION BY   CERTIFICATE myCertificate  
GO 
 
OPEN SYMMETRIC KEY SecretKey 
DECRYPTION BY CERTIFICATE myCertificate  ;  
create table encryptMe(DataSecretID int identity primary key, AccountNumberEncrypted varbinary(256),
AccountNumber varchar(20))
GO
declare @counter int =0
while @counter<100
begin
insert into encryptMe(AccountNumber, AccountNumberEncrypted) 
values(@counter+1000000,EncryptByKey(Key_GUID('SecretKey')  
    , convert(varchar(20),@counter+1000000)))
select @counter=@counter+1
end
select DataSecretID, AccountNumber, AccountNumberEncrypted, convert(varchar(200),DecryptByKey(AccountNumberEncrypted) ) From encryptMe
GO
close symmetric key SecretKey 
GO
exec sp_replicationdboption mydb, publish, true
GO
exec sp_addpublication mydb, @status='active'
GO
exec sp_addarticle mydb, encryptme, @source_object=encryptme
GO
exec sp_addpublication_snapshot mydb
GO
exec sp_addsubscription mydb,'ALL', 'Publisher\SQL2012',mydb
GO
exec sp_startpublication_snapshot mydb
GO
 

Answer 13

Subscriber code:

use mydb
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongPassword'
GO
CREATE CERTIFICATE myCertificate  
WITH SUBJECT = 'Certificate Data Encryption';
GO
CREATE SYMMETRIC KEY   SecretKey 
WITH KEY_SOURCE = 'My key generation bits. This is a shared secret!',
ALGORITHM = AES_256, 
IDENTITY_VALUE = 'Key Identity generation bits. Also a shared secret'
ENCRYPTION BY   CERTIFICATE myCertificate  
GO 
 
OPEN SYMMETRIC KEY SecretKey 
DECRYPTION BY CERTIFICATE myCertificate  ;  
GO
select DataSecretID, AccountNumber, 
AccountNumberEncrypted, 
convert(varchar(200),DecryptByKey(AccountNumberEncrypted) ) From encryptMe
GO

Answer 14

They are identical in my publisher and subscriber.

All you need to do is to drop the keys.

DROP SYMMETRIC KEY symmetric_key_name

Then recreate it.

Answer 15

Thanks a TON Hilary...
DROP SYMMETRIC KEY symmetric_key_name
is it safe to
drop  and create at creating at Publisher? i dont want to mess up already encrypted data at publisher

Answer 16

Drop it at the subscriber and recreate it there.

As a test I did drop it at the publisher as well and then recreated it and it still worked.

I would feel nervous about doing this in production however.

Answer 17

in stage Environment:

DRopped the Symmetric Keys @subscriber, recreated and that didn't help..
DRopped the Symmetric Keys @Publisher and, recreated and that didn't help either and i lost the decryption here now.

Took backup of master and certificate @ publisher:

Dropped : Master key, certificate, symmetric keys @ subscriber and restored once and also created from scratch didn't help..

Dropped at publisher same and did the same 

now i cant view the data at publisher now..( i ' m totally scared now )..Please help

Answer 18

Is this production? I sincerely hope not.

If so you may need to restore the last good backup and then decrypt your data in the restored database and then move it back into production.

Answer 19

I did it Stage..not in production...

I am going to restore from yesterday's backup and try to see if that helps and let you know.

one thing i read online is to never drop symmetric keys, my bad didn't do enough research...

Thanks for the help Hilary

Answer 20

I was able to restore and encrypt/decrypt back to normal on publisher..

probably i need to drop subscriber database, create new, copy the keys and then enable replication..

hope that helps 

Answer 21

looks like if i miss below 2 values while creating symmetric key at publisher there is no way we can have the same keys at subscriber and decryption wont work at subscriber...................................

KEY_SOURCE = 'My key generation bits. This is a shared secret!',

IDENTITY_VALUE = 'Key Identity generation bits. Also a shared secret'

https://docs.microsoft.com/en-us/sql/relational-databases/replication/security/replicate-data-in-encrypted-columns-sql-server-management-studio

should be :
CREATE SYMMETRIC KEY   SecretKey 
WITH KEY_SOURCE = 'My key generation bits. This is a shared secret!',
ALGORITHM = AES_256, 
IDENTITY_VALUE = 'Key Identity generation bits. Also a shared secret'
ENCRYPTION BY   CERTIFICATE myCertificate  
GO 
I have done:
CREATE SYMMETRIC KEY   SecretKey 
WITH  ALGORITHM = AES_256,  
ENCRYPTION BY   CERTIFICATE myCertificate  
GO 

Answer 22

Yep, KEY_SOURCE, IDENTITY and ALGORITHM need to be specified when creating original symmetric key in order to be able to create the same one on the other side.

DO NOT drop existing key! If you do, you will lose the encrypted data.

You have to decrypt the data first using that existing key. Create a new key (that has key_source and identity specified), and encrypt data with that key. Then you are able to create the same key on the other side, using same key_source and identity. If everything works and noone uses the old key anymore, you can drop it as the last step of the action.

Decrypt-encrypt might be challenging, depending on allowed downtime. You can build a new table with recrypted data next to original one, then switch by rename. If data is changing, and downtime is allowed, you can have original table X locked during the operation. If downtime is not allowed, you can use Change Tracking to pick-up the intermediate changes, X locking the table before syncing rows changes in the meantime - a very small downtime (short X lock). Test first, of course.

Answer 23

Hi Nick1990_K,

Any update on the issue, was the issue resolved? If the issue has been resolved, please mark the corresponding replies as answer as it would benefit others when they are reading this thread. If not, could you please provide more information so we can have a better understanding about the issue?

If you have any other questions, please let me know.

Regards,
Davy

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].