Service Broker Mirroring Problem

Adam Connelly


Hi,

My company has a system where we have a mirrored server and multiple clients communicating with that server using service broker. Up until now although we had mirroring set up, we weren't bothering to specify a mirror when creating routes from the clients to the server. Now that we've done this messages can be sent from the server to the clients, but not the other way round.

I suspect this is because the mirror database instance does not have the same certificates installed in its master database as the primary (we are using both transport and dialog security). I thought about manually copying the certificates over from backups, but I don't think this would work. Also, according to one of my colleagues, it isn't possible to mirror the master database (which makes sense).

I've tried altering the endpoints on each of the machines involved using the following to disable transport encryption, but maybe I'm not understanding something since this doesn't seem to work:

Code Block

use master
alter endpoint [EndpointName]
for service_broker (encryption = disabled)

My question is how can I either make sure that anything required for transport security is available to the mirror, or how do I disable transport security and simple use dialog security instead (and also if this is actually the problem).

Thanks in advance,

Adam Connelly

[edit] I think I've realised why disabling encryption doesn't do anything for me (i.e. the certificates are still used to sign the messages), but unfortunately knowing that doesn't solve my problem [/edit]

Remus Rusanu


Hello Adam,

You are understandibly a bit confused about what to configure and how . As you alreeady noticed, there are two layers of security involved in Service Broker: dialogs and endpoints, so you are on the right track and actually very close to the solution.
Dialog security is between services, which are database entities and as such are secured by database certificates. Dialog security is completely agnostic to mirroring, as all the objects involved are in the database and will fail over to the mirror (certificates, remote service bindings, users w/o login, database permission grants).
Endpoint security is between SQL instances and it is configured with 'instance' objects (endpoints, certificates in master, logins, server level permissions).
So when you have a service communicating with another service that is hosted in a mirrored database, you need to:
- enable dialog security at the service layer (exactly the same thing you would do if there is no mirror)
- enable endpoint security between the SQL instances involved, but there are 3 instances involved. So you configure endpoint security between client and principal, then between client and mirror. Each individual pair is independent, you should configure the endpoint security between the client and the mirror instance as a new endpoint security session.

So lets say you have three machines A (the client), B (the principal) and C (the mirror), here is how you would configure the endpoint security (I'm skipping the dialog security part since you already know how to do it):

1. Create a certificate in [master] on A, create a broker endpoint on A that uses A's certificate.
2. Create a certificate in [master] on B, create a broker endpoint on B that uses B's certificate.
3. Create a certificate in [master] on C, create a broker endpoint on C that uses C's certificate.
4. Export A's certificate
5. Import A's certificate on B, grant CONNECT permission on B's endpoint
6. Export B's certificate
7. Import B's certificate on A, grant CONNECT permission on A's endpoint
8. Import A's certificate on C, grant CONNECT permission on C's endpoint
9. Export C's certificate
   
10. Import C's certificate on A, grant CONNECT permission on A's endpoint
    

As you see you have configured the endpoint security between A and C just as you would had done it if you wanted to target from A any service hosted on C.

Remus Rusanu

Adam Connelly wrote:

Hi, [edit] I think I've realised why disabling encryption doesn't do anything for me (i.e. the certificates are still used to sign the messages), but unfortunately knowing that doesn't solve my problem [/edit]

Right, endpoint authentication and authorization is unrelated to encryption settings and cannot be disabled.
BTW, Just for completness, there is one good to know trick here: granting CONNECT permission on the Broker endpoint to [public] removes the need to deploy the peer's certificate locally to configure endpoint security. That is, two SQL isntances that had granted connect on the Broker endpoint to [public] can actually establish a connection w/o exchanging the certificates used. This is for scenarios like a public service (think amazon.com) that is hosted on a SQL instance that has to accept connection from unknown clients. The equivalent functionality at the service security layer is the anonymous secruity setting on rmeote service binding.

Adam Connelly

Thanks for the quick reply. Turns out the problem was actually in the templates I was using to generate scripts that setup the databases - basically the broker guid wasn't getting set correctly when routes were being created. I've put in a quick fix for that and now everything seems to be working.

I think we'll probably use the trick you suggest simply because for the moment it makes things a bit easier, so thanks once again for the reply - probably would have taken a lot longer to figure out what was wrong otherwise.

Adam