Server1 running SQL Server 2012 with Service Master Key A, db1 with Database Master Key 1, symmetric key and certificate available. Password used to protect the DBMK 1 is unknown, encryption is enabled by SMK A.
Server2 running SQL Server 2012 with SMK B, db2 with DBMK 2, symmetric key and certificate available. Password used to protect DBMK 2 is unknown, SMK B used for encryption.
A restore of db2 to Server1 is required. There's one encrypted column in a table that stores passwords for an internal ASP.NET application. There are other applications accessing encrypted data in db1 on Server1 .
Best option to restore db2 to Server1 and access the encrypted data without causing issues to db1 ?
A simple restore will bring the db2 data, symmetric key and certificate to Server1, but SMK A can't be used to decrypt the data: "Please create a master key in the database or open the master key in the session before performing this operation." Restored SMK B on a test server and all was good. Can't use this solution on Server1, as I believe that restoring SMK B to Server1 (overwriting SMK A) will disable my access to the encrypted data in db1. Don't want that to happen.
Any advice ? Thank you.