I have two databases that need to share encrypted data. I created a master key in both databases (both using the same password), then created a certificate in both (same name). When I copy encrypted data from one database into the other, the other is unable to decrypt the data.
I know that I can decrypt the data from one before putting it into the other, and then run the encryption in the new database, but I would rather not have to decrypt the data before sending it to the new database.
Does anyone know of a way to make the databases share the same master key and/or certificate so I don't have to play games while copying data? Right now I'm using only SQL Server certificates, not third party certificates. I'd rather not have to use a third-party cert if I can help it.