sql server - AlwaysOn Failover, secondary node jobs fails

Question

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

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