I have an encrypted database in SQL AlwaysOn availability group. This issue is pretty simple, I have the following error when the database fails over: Please create a master key in the database or open the master key in the session before performing this operation.
I have been reading a lot about encryption, but I can get it straight. I understand the secondary server is not able to decrypt the data, but I can't find the solution. My temporary solution is to add the service key to the database master key:
OPEN MASTER KEY DECRYPTION BY PASSWORD = ‘<enter the password>’
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
All my research leads me to a backup/restore of certificates for TDE, but in my case I am not using TDE, but instead have a table inside the database that is encrypted using a symmetric key protected by a certificate. Those certificates are in the user database rather than master database. I can find 2 certificates when I run the following command:
USE MYDATABASE
SELECT name, subject, start_date, expiry_date
FROM sys.certificates
I have 3 symmetric keys when I run the following command. One for each certificate and one DMK.
USE MYDATABASE
SELECT name, key_length, algorithm_desc, create_date, modify_date
FROM sys.symmetric_keys;
So it seems that my issue is the service key cannot decrypt the DMK on the secondary. How can I fix that ?
Thank you