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.