-1

When testing the failover functionality, the secondary node is unable to run any jobs accessing a TDE database failing with the following error Please create a master key in the database or open the master key in the session before performing this operation.

I have tried

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password' --now set encryption by the SMK ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

restarting the service and disabling encryption.

I don't know much about the encryption functionality, and just followed steps to turn it on. Did not expect the failover mechanism to be so rubbish, and worse, so little information about it online. Any help would be appreciated. Thanks Jon

jlj
  • 75
  • 1
  • 10

3 Answers3

1

So it turned out the SSISDB was the issue. I managed to stop error with the following command

EXEC sp_control_dbmasterkey_password @db_name=N'SSISDB', @password=N'', @action=N'add'

It then started throwing up trustworthiness errors which the following then fixed: ALTER DATABASE [SSISDB] SET TRUSTWORTHY ON

Jobs started running after that.

jlj
  • 75
  • 1
  • 10
1

This error is caused by the Service Master Keys (SMK) not matching between the nodes on your AG. Your database has a Database Master Key (DMK) that, when created, was encrypted by the local server's SMK. If you have only two nodes in the AG, the fix is relatively simple - backup the SMK from the secondary node, open the DMK on the primary with password, restore the SMK to the primary, and re-encrypt the DMK with the new SMK. Why that direction (as opposed to primary → secondary)? Changing the DMK is a write operation and those aren't possible on the secondary nodes!

Ben Thul
  • 31,080
  • 4
  • 45
  • 68
1

The Database Master Key (DMK) is encrypted with the Service Master Key (SMK) which is unique to each SQL Service instance and you want it this way.

SQL Server has an alternate way of decrypting the DMK. If the DMK cannot be decrypted with the SMK, it searches the credential store for a password that matches the same family GUID. If it finds a family GUID that matches your database it will attempt to decrypt the DMK with the stored password. If that succeeds then it will use the DMK to encrypt or decrypt credentials or keys within the database.

So the use of sp_control_dbmasterkey_password will store a family GUID based on the database name and the password decrypting the DMK in the master database.

To ensure that the DMK works when a AG fails from the primary to a secondary, run the sp_control_dbmasterkey_password on the secondary as part of your process to join an database to an AG.

In the AG wizard in SSMS 2016+ there is a prompt that has you add the password for a database with a DMK. When you add this password, the wizard will run the sp_control_dbmasterkey_password automatically on the secondary notes. (However, there is a known bug if you use automatic seeding).

See this link for further information: sp_control_dbmasterkey_password

JPuttman
  • 11
  • 1
  • 1