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
Last activity: Jul 8, 2019, 02:29 PM