We setup a scenario with to DBs A (on Server X ) and B (on server y) communication via Service broker. One of the DBs, let say DB A is mirrored to another server Z. After failover Database A from Server X to server Y Service Broker communication fails with message :
‘The session keys for this conversation could not be created or accessed. The database master key is required for this operation.’
I was able to fix the problem with following statements :
OPEN MASTER KEY DECRYPTION BY PASSWORD = '<password>'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
CLOSE MASTER KEY
where <password> is the password I used for the database master key auf DB A on SERVER X.
But this a manuel step I have to to after an failover event.
Question : Is there a way to setup this scenario without manully alter the database master key after a failover occured ?
I cannot alter the master key for the mirror DB A on server Z because it is in recovering-State.
In this scenario al server are sql server 2008 R2. But I assume the same problem will occur on SQL SErver 2012.
I checked the internet but couldn't fin any solution for this problem