SSISDB master key in an Availability Group

Question

I activated Always On support for SSIS and everything works fine until a fail over operation happens. Turns out the secondary nodes have a different service master key than the primary node and after each fail over I need to run this at the new primary node:

USE SSISDB
GO
open master key decryption by password = 'secretpassword' 
Alter Master Key Add encryption by Service Master Key

I've thought about including this steps as part of the "SSIS Failover Monitor Job" to check if the node has changed to primary and then alter the DMK but I wouldn't want to leave the password explicit.

Does anybody have an idea how to deal with this?

Answer 1

We had similar case where the DMK being used within the DB to protect some columns in the database, and DMK protected by Server Master Key (SMK), this is good as always the DMK is opened as long as the SQL Service running with same SMK, there is no need of password in which case the application need to explicitly OPEN and CLOSE DMK using password.

I agree with:

You should make all the nodes in your Always On Group have the same service master key, then failover would be seamless. https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-service-master-key-transact-sql

Having same SMK across the replicas in availability group would be seamless. It may considered at some-point as less secure, well we still maintain the boundary of AG. i.e. the database cannot be functional when it is restored into a instance other than AG replicas, and we need to extend that boundary (one instance to multiple) as we need to compliance to the High Availability.

Consider it like maintaining same SID of logins across Availability Replicas. To make SMK same in all replicas, you can do following (if there are no other things protected already by SMK on secondary):

---- On Primary replica 
BACKUP 
SERVICE MASTER KEY TO FILE = '\\Sharedfolder\SMK-202005121328.key'
ENCRYPTION BY PASSWORD = '*************';

---- On Secondary replica 
RESTORE
SERVICE MASTER KEY FROM FILE = '\\Sharedfolder\SMK-202005121328.key' 
DECRYPTION BY PASSWORD = '*************';