Migrating Encrypted Data

Question

Migrating Encrypted Data

Clint-525719

SSCrazy

Points: 2924
More actions
July 1, 2016 at 2:58 pm

#308836

I am replicating a table with encrypted data (column-level) to a new database on the same server instance. When trying to view the data, it shows as NULL on the subscriber. i have followed all of the steps in this article: https://msdn.microsoft.com/en-us/library/bb326115(v=sql.105).aspx
The only thing I am missing is the KEY_SOURCE parameter value to re-create the symmetric key on the subscriber, which the article states that you must have. Does anyone know if not supplying this value when re-creating a symmetric key in the subscriber will cause the data to show as NULL?
I'm grasping at straws here...
thanks!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 1

How is the symmetric key protected on the publisher? If you supplied a KEY_SOURCE on the publisher, I guess you need it at the subscriber too. If you encrypted the symmetric key with an asymmetric key on the publisher, you need the same key on the subscriber. Etc.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Clint-525719 SSCrazy Points: 2924 More actions · Answer 2

I moved Master key, and all symmetric keys and certs used, and I'm still getting NULLs. I even created a brand new database, and walked through that article, with the sole intent of testing this, and I still get NULLs on the subscriber...

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 3

If the decryption doesn't work, you'll get a NULL. This is poorly documented in BOL, so that's likely your issue.

A couple questions.

1. Are you casting the decryptbykey result to the original type? SQL Server won't do this for you, and if you don't CAST this you will get garbage.

2. You do need the KEY_SOURCE, AFAIK. All the symm key parameters need to be provided.

Test this:

Open the sym key on the publisher in a query window. Run a symmetric encryption of a simple string (use your own key name):

select EncryptByKey(Key_GUID('my_key'), 'This is a string')

Take the result (hex data) and on the subscriber and run the same thing:

select cast( decryptByKey(Key_GUID('my_key'), 0x00aaa) as varchar(200))

You should be able to do this manually and see if you have the same key recreated.

Clint-525719 SSCrazy Points: 2924 More actions · Answer 4

Thanks guys. I figured out my issue. i had two things working against me:

1) I did not have all the original parameters correct when I tried to duplicate the symmetric key on the subscriber

2) I did not alter the Master Keyy to allow it to be automatically opened by the Service Master Key when I was calling the decrypt functions.

I think I've got what I need now.

Thanks again!

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 5

Thanks for the update. Those are definitely two critical things.

If you don't mind, can you mark your update as the correct answer for future readers?