locked
Column Level Encryption question with T-Replication RRS feed

  • 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,


    Thursday, May 11, 2017 5:42 PM

All replies

  • 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

    Thursday, May 11, 2017 5:52 PM
    Answerer
  • 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.


    Thursday, May 11, 2017 6:39 PM
  • This should be working - what version of sql are you running on your publisher and subscriber?
    Thursday, May 11, 2017 7:25 PM
    Answerer
  • 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

               
    Thursday, May 11, 2017 7:57 PM
  • 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.

    Thursday, May 11, 2017 7:59 PM
    Answerer
  • How did you created the same keys on the other side (export to file->import/create from file)? I doubt they are the same.

    Thursday, May 11, 2017 9:29 PM
  • 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)







     


    Friday, May 12, 2017 1:50 AM
  • 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


    Friday, May 12, 2017 6:59 AM
  • 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

    Friday, May 12, 2017 2:15 PM
  • 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

    Friday, May 12, 2017 2:44 PM
  • This works for me.

    Friday, May 12, 2017 2:51 PM
    Answerer
  • 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
     

    Friday, May 12, 2017 2:51 PM
    Answerer
  • 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
    

    Friday, May 12, 2017 2:52 PM
    Answerer
  • 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.

    Friday, May 12, 2017 3:04 PM
    Answerer
  • 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


    Friday, May 12, 2017 4:15 PM
  • 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.

    Friday, May 12, 2017 4:53 PM
    Answerer
  • 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

    Friday, May 12, 2017 5:18 PM
  • 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.

    Friday, May 12, 2017 6:03 PM
    Answerer
  • 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

    Friday, May 12, 2017 6:16 PM
  • 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 

    Friday, May 12, 2017 6:58 PM
  • 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

    Friday, May 12, 2017 7:13 PM
  • 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.



    Friday, May 12, 2017 10:07 PM
  • 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 MSDNFSF@microsoft.com.

    Friday, May 19, 2017 1:25 AM