Sql-server – How to decrypt data on a replicated MSSQL database

encryptionreplicationsql servervarbinary

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

Best Answer

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.

If the symmetric key you are talking about is one you've manually created in the user database then you'll need to create it with the same initialization vector as the previous one by specifying a Key_Source and the same algorithm. If you haven't done this (or restored the database containing the same key) then that's your issue.

Here is a walk through to show you how to create the same symmetric keys in multiple databases.

Related Question