0

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

Improve this question
1
  • Can you include the code you're using in your trigger to encrypt the data and your SELECT query to decrypt it?
    – HandyD
    Jul 8, 2019 at 5:50

1 Answer 1

Reset to default
4

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.

Improve this answer
2
  • This is what I ran, in both instances: CREATE SYMMETRIC KEY KEY_NAME WITH ALGORITHM = AES_128 ENCRYPTION BY CERTIFICATE cert_name;
    – cleverpaul
    Jul 8, 2019 at 2:54
  • 1
    @cleverpaul Right, so you have two different symmetric keys at this point. See my answer above, you'll need to backup and restore the certificate from the original but you can't do that with symmetric keys so the initialization on them must be the same, as a I've stated.
    – Sean Gallardy
    Jul 8, 2019 at 9:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.