We help IT Professionals succeed at work.

moving encrypted database to secondary server

We have a database with encrypted data using sql 2005 encryption by certificate.  We have made a copy of the database and placed it on a virtual server with sql 2005. I backed up the service master key from the original server and restored the service master key on the virtual server.  Initially, on the original server, we created the database master key and certificate and are encrypting by that certificate.

When I try to run a select statement using DecryptByCert, it is only returning a 1 digit number rather than the 20 digit number it should be.  Is there something else that has to be done to sync up the database master key or cert with the secondary server before the decryption works correctly?  Any help is appreciated.
Comment
Watch Question

The only way to synch service master keys is to backup the key on the primary server and then restore it on the secondary server.

Author

Commented:
That is what I stated I did.

"...I backed up the service master key from the original server and restored the service master key on the virtual server. ..."

Author

Commented:
Funny you should put that link out there.  That is the one that I followed and that didn't work, thus, experts-exchange question.

Author

Commented:
Okay, my problem was actually in my select statement rather than the key movement.  My select statement was something like this:

"select top 100 convert(char(20), DecryptByCert(Cert_ID('MyCert'), encData)) from MyTable"

What I didn't realize was, the data that was encrypted was Unicode.  Once I changed my select statement to reflect that, I got the results I expected.

"select top 100 convert(nchar(20), DecryptByCert(Cert_ID('MyCert'), encData)) from MyTable"
 

Thanks for your help.  The link that you posted is actually a very good blog and very pertinent to this subject, so I am going to give you the points for that.  

Thanks again!

Explore More ContentExplore courses, solutions, and other research materials related to this topic.