Moving/Restoing DB's with Native encryption on columns

[DavidPike]

I am using native asymmetric encryption with SQL Server Express. When I backup the DB on one server and move it to another I can access my encrypted data columns but only if I open the Master Key with the password every session. This does not occur if I restore to the original server, the columns are available without opening the master key by password. If I attempt to read encrypted data without opening the master key, after only opening the symmetric key, I get the following error: "Msg 15581, Level 16, State 3, Line 4 Please create a master key in the database or open the master key in the session before performing this operation." How do I get the restored DB (on a different sever) to give me access to the encrypted columns without opening the master key every session?

 

[yelworcm]

Typically, if you are an administrator, the key is automatically opened for you. If you are not an admin on the destination box, this is likely what happened.

If you are an admin, you may need to drop the encryption by the (old) service master key, and add encryption by the new service master key. I am a little sketchy on the details, so you will want to try this out on a dummy database first, otherwise, you could easily lose the encrypted data entirely:

on the source server;
1) open the database master key
2) Add encryption by password (ALTER MASTER KEY ADD ENCRYPTION BY PASSWORD = 'strong password'
3) Close the database master key.

copy the database to the destination server
on the destination server
1) Open the database master key by the password (you may want to decrypt something, just to make sure it worked, too)
2) Drop the old encryption by service key (ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY)
3) Add the encryption by the (new) service master key (ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY)

At this point, you should be able to close the key, and decrypt data normally without the explicit opening of the key.

Again, this is really some risky stuff, as you don't want to munge the encryption, or you will never get the key, or the data back. backups, and test databases, are the way to go here.