SQL AlwaysOn Availability Group - Secondary can not decrypt database master key (DMK)

  • 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

  • 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.

  • 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

  • Gamleur84 - Wednesday, January 17, 2018 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

    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 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.

    No, you don't need to restore the SMK

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Gamleur84 - Thursday, January 18, 2018 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

    Did it break access to existing data?

  • 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