[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 178
  • Last Modified:

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.
0
edfreels
Asked:
edfreels
  • 3
  • 2
1 Solution
 
SQL_SERVER_DBACommented:
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.
0
 
SQL_SERVER_DBACommented:
0
 
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. ..."
0
 
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.
0
 
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!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now