Sample Header Ad - 728x90

How can I decrypt data on a replicated MSSQL database?

0 votes
1 answer
528 views
I have a database which uses a symmetric key to encrypt a credit card field. There is a trigger on the credit_card field which runs every time it is updated, to encrypt the contents, and save it to another field called credit_card_encrypted and then it wipes the contents of credit_card. I have recently setup replication on the table, to another instance of SQL Server. Everything works fine except, I am unable to decrypt the data on the replicated server. I have imported the certificate and the key and I have created a symmetric key however, whenever I try to decrypt the credit cards, it just returns NULL. I have tried to manually add a value to the credit_card field on the replication server, and then I manually encrypted it, and I was actually able to successfully decrypt it. So why can I not decrypt the data that is replicated? Also, I can compare the varbinary data of the encrypted card and the contents looks exactly the same on the main server & the replication server. e.g: 0x000CE24D0120A349PPL29D6BFE70C54E0100000064E3380AA4EC04A4B4E959535798696E81502A063617B21CFD75FAFF93866D47603543A5D6EBECDF5F8C0D23D8CCL982
Asked by cleverpaul (201 rep)
Jul 8, 2019, 01:58 AM
Last activity: Jul 8, 2019, 02:29 PM