January 17, 2018 at 11:37 am
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
January 17, 2018 at 11:47 am
Hmmm, this is tricky. How did you set this up? I suspect that you really needed to restore the SMK from the primary onto the secondaries and then setup the restore.
You're not adding the SMK (Service Master Key) to the DMK (database master key), what you're doing is adding your DMK in the user db to the hierarchy on the secondary that allows the SMK to encrypt the DMK (and decrypt it). What you're running is what is needed. I haven't thought about this from the AG perspective, but really you do need a job that runs this in case something changes, but if you had the same SMK on both instances, then I think this might work.
January 18, 2018 at 9:48 am
Hi Steve,
You are right, I took a backup of the Service Master Key and restore it on the secondaries and it worked !
Thank you
January 18, 2018 at 10:03 am
Gamleur84 - Wednesday, January 17, 2018 11:37 AMI 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 KEYAll 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.certificatesI 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
You get a similar issue when you put SSISDB into an AG, the key needs to be stored locally so that it can be accessed.
Create a credential to store the DMK using the stored procedure
sp_control_dbmasterkey_password
Steve Jones - SSC Editor - Wednesday, January 17, 2018 11:47 AMHmmm, this is tricky. How did you set this up? I suspect that you really needed to restore the SMK from the primary onto the secondaries and then setup the restore.You're not adding the SMK (Service Master Key) to the DMK (database master key), what you're doing is adding your DMK in the user db to the hierarchy on the secondary that allows the SMK to encrypt the DMK (and decrypt it). What you're running is what is needed. I haven't thought about this from the AG perspective, but really you do need a job that runs this in case something changes, but if you had the same SMK on both instances, then I think this might work.
No, you don't need to restore the SMK
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 30, 2018 at 9:51 am
Gamleur84 - Thursday, January 18, 2018 9:48 AMHi Steve,You are right, I took a backup of the Service Master Key and restore it on the secondaries and it worked !
Thank you
Did it break access to existing data?
February 9, 2018 at 12:12 pm
Thanks Perry Whittle, you solution is much better !
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply