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