1

while setting up AlwaysOn - Availability Group on a test system, I generated all the restores from live and restored them to this new server.

then I backup all these databases to a network share to make them ready for being added to the availability group.

when then adding then to the availability group I get this message below, for a couple of them. (2 or 3 databases out of 50)

enter image description here

Bad news:

I don't have the master key for that live server - the server where the databases come from.

Good news.

I have a backup of the master key in the original live server.

enter image description here

I also have backups of certificates:

enter image description here

Not so good news:

I haven't got the decryption password for that master key backup file

This question seems to be related:

What do I need to restore an encrypted MSSQL database?

Question:

How can I restore those databases only having a backup of the master key in the original server?

P.S. I don't have the decryption password for that master key backup file - above pic

Maybe I will have to regenerate the master key.

Improve this question
4
  • 1
    You got the decryption password for that master key backup file?
    – Biju jose
    Commented May 19, 2019 at 8:55
  • You've restored the databases, that's not the issue. Do those databases have any secrets within them that the master key is actually needed?
    – Sean Gallardy
    Commented May 19, 2019 at 13:47
  • No, these databases don't have any extra sensitive data, it would be great if I could get rid of that link to the master key
    – Marcello Miorelli
    Commented May 19, 2019 at 13:50
  • If they don't hold anything, just drop the database master key. The wizard is just looking for a master key, and if there is one thinks there are other secrets. Since there aren't, drop the master key and all will be well.
    – Sean Gallardy
    Commented May 19, 2019 at 15:01

1 Answer 1

Reset to default
2

The screenshots show a backup of the service master key for the instance but that is not the same as the database master key that the AAG wizard is asking for. See Encryption Hierarchy in SQL.

Your situation sounds similar to adding SSISDB to an AlwaysOn AG, in that a password protected DMK exists on the database, and so you must provide that password in order for the DB to be restored and accessible on the other replica.

If you don't know the password, you need to go back to the live server and alter the DMK password:

ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = '####';

Once this has been done, record the new DMK password in a safe place, like a password safe. Backup the DMK to this location if possible as well, so that you can recover the DMK if need be.

Once this is done, take a new backup use that to join the secondary in the AG, providing the password when prompted. An alternative to this is to use a symmetric key created by a certificate instead of a password which is protected by the DMK, and then simply backup and restore that certificate on all replicas to enable encrypted databases to be joined to the AG with relative ease. More info here.

Improve this answer
1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.