Service Broker failed after Mirroring failover - by Kunk

Status : 

 


1
0
Sign in
to vote
ID 790194 Comments
Status Active Workarounds
Type Bug Repros 0
Opened 6/14/2013 8:18:37 AM
Access Restriction Public

Description

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 

Regards
Wolfgang Kunk
Sign in to post a comment.