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.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
edfreelsAuthor 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. ..."
edfreelsAuthor 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.
edfreelsAuthor 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!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.