Encryption: Restoring Certificates Between Servers (Standard v2047

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

K. Lindner - 04 Mar 2008 15:56 GMT

I'm new to the subject but I'm working on implementing column encryption and
want to make sure I understand exactly how everything fits together. I've
created a master key and certificate. I've backed them up to files and then
restored on a secondary server.

To make sure my restore is working properly, I take data encrypted via
symmetric key from Server1 and try to decrypt on Server2 (or vice versa). The
result is always null. Did I miss something or is this by design?

The link below is the sequence of events on each server that I think will
better illustrate what I'm attempting.
http://www.apcdirect.com/temp/certificates.png

My expectation is that servers with "synchronized" certificates and keys
would be able to decrypt each other's data. Am I misunderstanding something
or did I miss a step?

Note: In the image I've reduced a long varbinary string for easier
readability.

Any suggestions are appreciated.

Reply to this Message

Mike C# - 05 Mar 2008 02:20 GMT

When you create the symmetric key are you creating it using the same
KEY_SOURCE on both servers?

> I'm new to the subject but I'm working on implementing column encryption
> and
[quoted text clipped - 21 lines]
>
> Any suggestions are appreciated.

Reply to this Message

K. Lindner - 07 Mar 2008 16:21 GMT

> When you create the symmetric key are you creating it using the same
> KEY_SOURCE on both servers?

Hi Mike,

I replied to your post but it didn't show. So sorry if this is duplicated:

I tried your suggestion but it didn't have any effect. Still NULL when
trying to decrypt on a different server. I was not using KEY_SOURCE,
previously.

BTW - I'm always dropping all keys and certificates between test iterations
so that I'm starting with a clean slate to the best of my knowledge.

Thanks again!

Reply to this Message

Mike C# - 07 Mar 2008 21:32 GMT

>> When you create the symmetric key are you creating it using the same
>> KEY_SOURCE on both servers?
[quoted text clipped - 12 lines]
>
> Thanks again!

Hmm. I assume you're creating a backup of your certificate and restoring it
on the second server, then creating the symmetric key encrypted by
certificate, with the same KEY_SOURCE? I believe that should work, but
there may be some other settings affecting your results. I'll check BOL to
see if there are any special settings for encryption and post what I find
later.

Reply to this Message

K. Lindner - 12 Mar 2008 17:07 GMT

> Hmm. I assume you're creating a backup of your certificate and restoring it
> on the second server, then creating the symmetric key encrypted by
> certificate, with the same KEY_SOURCE?

Yes, that's correct. I will package up a script to reproduce the problem and
post it here.

> I believe that should work, but
> there may be some other settings affecting your results. I'll check BOL to
> see if there are any special settings for encryption and post what I find
> later.

I've been in BOL quite a bit so I'll be disappointed if you discover
something I've missed, but I agree!

I believe I've covered everything but I'm convinced there's something I'm
missing.

I can't imagine the capability isn't there. If so, it would make replication
interesting.

Thanks for your help.

Reply to this Message

Mike C# - 23 Mar 2008 19:12 GMT

Are you still having a problem? I may have found a solution for you.

>> Hmm. I assume you're creating a backup of your certificate and restoring
>> it
[quoted text clipped - 22 lines]
>
> Thanks for your help.

Reply to this Message