Encrypted Database transfer problem

Hi,

I have encrypted some columns of a table in a database. Following is the method which i applied for encryption.

I created a master key with a password and it is also encrypted by service master key. Now i created a certificate without password, so it is only encrypted by master key of the database. Now i created a symmetric key encrypted by the above certificate. The data is encrypted by this symmetric key.

To decrypt data i use DecryptByKeyAutoCert.

On my server this encryption & decryption is working perfectly.

But when i take this database to another server, it is not working.

What is the solution for this, should i drop service master key to encrypt master key or is there any soln.

Thank you.

Pls give me soln. i am worried abt it.

Gaurav

See last paragraph in: http://blogs.msdn.com/lcris/archive/2005/09/30/475822.aspx.

After you restore your database, you need to readd the service master key encryption of the database master key.

Thanks
Laurentiu

|||

I have attached a database created with master key, certificate and symmetric key.

Now i have attached this database to another server but no master key, certificate or symmetric key is present there in the new server database.

Do i need to backup master key and certificate and then restore them to the new server database? Then what abt symmetric key. there is no option for symmetric key to be backedup.

what is the soln for symmetric key? and taking backup of master key and certificate, then restoring it is the only soln?

Thanks

Gaurav

|||

You don't need to do anything else when moving a database from one server to another, other than what I mentioned before: restore the SMK encryption of the DbMK, if such an encryption existed on the source server.

How did you verify whether the keys are present in the database after you reattached it? Did you look in the catalogs (sys.symmetric_keys, sys.certificates)?

Thanks
Laurentiu