1

My organization has been successfully using service broker, dialog security, for a few years.

I have a new instance of service broker that I would like to configure, but I believe I am running into a situation where the order in which the new broker instance is being incorrectly placed into the databases (with existing broker instances in place) causing the new broker instance to fail.

My error is on the target server; Certificate Not Found. Security context could not be retrieved.

My situation is: Source server A (dmz) sending to target server B, where there are already existing broker instances sending messages both ways but on different databases.

Test 1: I switched the role of the source and target server around, making server B my source and server A (dmz) my target while using the same databases. This was successful.

Test 2: I dropped the test 1 instance and created 2 new databases on each server. I have again, reconfigured the source and target roles, now sending from server A (dmz) to server B and this was successful. There is only the one service broker instance configured in the new databases, so the order of placement is not an issue.

I have come into this situation in the past when we first set up our dev/test environments; However, the situation was slightly different as many other broker instances were also failing. Not all of them, but some. Once I realized what was happening, dropping all instances and recreating them in a specific order, source instances before target instances, the situation was resolved.

I would think that service broker would add a new instance in the correct place of order, automatically.

Am I correct in thinking that the broker instances must be configured within a specific order? If so, how can I add new broker instances without interrupting what is already there?

Please share your thoughts and suggestions.

  • 1
    Multiple instances of a service (multiple DBs) should all share the same private keys. You cannot add a new instance with a different key. – Remus Rusanu Feb 22 '18 at 1:05
  • Thank you for your response. In hopes to clear some confusion, I believe what you are saying is; if I have service A on DB1 and service A on DB2 then add another broker instance (say service B) using the same DB1 and DB2, then service B should use the same private key as service A. But now I question; when we have a third broker instance (service C) on DB2 and service C on DB3, shouldn't the broker instance for service C have a different key than service A and B? – Kourtney Feb 22 '18 at 21:37
  • Is the service C and DB2 owned by the same user (dbo?) as the service B on DB2? IF that is the case (and it usually is) then it cannot have another certificate, since the same identity owns both services. – Remus Rusanu Feb 23 '18 at 7:55
  • We do use the same user for each service and certificate, though this is not the same user that owns the database. Test 1: Create new user and login, made user owner of the new certificate for new service. Target Error: No certificate found, Msg could not be delivered because the security context could not be retrieved. Test 2: Created new service without certificate, with intention of using pre-existing certificate. Same error as above, on target. Note: we are setting up new service from DB1 to DB2, there is a pre-existing service (diff. process) from DB2 to DB1. – Kourtney Feb 23 '18 at 15:26
2

Set up a profiler trace and monitor for Security Audit/Audit Broker Conversation. Repro the issue, an event of this class should be produced. Look at the event details. It should shed light. I think two of the event columns (can't remeber which, and MSDN seems to omit the info) contain the problem certificate issuer name/serial number, which should further help narrow down which certificate is being used.

[Edit] Run this query query on all three databases to find out which certificates are used by which service and remote service binding:

select s.name, c.* 
from sys.services s
join sys.certificates c
on s.principal_id = c.principal_id
where c.is_active_for_begin_dialog = 1
and pvt_key_encryption_type != 'NA'
order by c.expiry_date desc


select b.remote_service_name, b.remote_service_binding_id, c.* 
from sys.remote_service_bindings b
join sys.certificates c on b.remote_principal_id = c.principal_id
where c.is_active_for_begin_dialog = 1
order by c.expiry_date desc

[Edit after your data]

1) In PRd2DB services tab: all services in the DB are owned by dbo, and dbo owns 4 certificates that are valid for SSB (certificate ids 280, 288, 327 and 357). The xls did not correctly capture the expiry_date (is all 00:00.0) so I cannot know for sure which one is used, but if the results come from the query I posted above then the first one will be used, certificate id 280 with the serial number 54 89 2c 1a.... So this is the certificate used to identify any service from this db.

2) In Prd2 in the RSB tab, the query shows a mixture of certificates with private key and ones without a private key. This should never happen. An user associated with an RSB represents the identity of a remote service and should own only certificates with no private key. Also, there is only one RSB user (the one with principal_id 58) for all remote services. There should be a different identity user for each remote service. Right now any dialog started in PrdDB2, to any service, will use the certificate with serial number 34 99 ....

3) DMZDB1 services: same as prd2, all services owned by dbo, dbo has 3certificates (ids 335, 341, 371) and the one used will be the one with serial number 74 0a ...

4) DMZDB RSBs: there are two RSB users, one principal id 9 and one 30. The principal id 9 has a mixture of certificates, including some with private key, while principal id 30 has only one certificate but that certificate has a private key.

Overall, the configuration is deeply flawed and and no wonder it doesn't work. You do not understand the role and the proper configuration of a remote service binding.

Please follow the following rules. Consider a remote service 'Foo':

  • create a different user (principal) for each remote service, call it remote_user_Foo
  • create a RSB for each remote service, using the principal for this service (ie. remote_user_Foo)
  • The remote_user_Foo should own exactly one certificate, and it should not have a private key
  • Grant necessary permissions (SEND) to remote_user_Foo

Next time you run the query for showing the RSB it should show a number of rows equal to the number of remote service bindings, the principal_id values should be all distinct and the pvt_key_encryption_type should all be NA.

  • It is showing me that my new service (trace column: TargetUserName) is indeed using the wrong certificate (trace column:DBUserName). A certificate that exists in DB1, but not in DB2. What are some reasons it might pick up the wrong certificate? The expiry date is the same across each cert. – Kourtney Feb 23 '18 at 20:25
  • 1
    Since you're talking about different services, there should be different identities. Which means different users, owning different certificates. Did you set up the Remote Service Binding for the new service reusing the previous service user? – Remus Rusanu Feb 23 '18 at 20:46
  • Yes, same user. But in previous test cases, mentioned above, I have tested while creating a new user for my new service. This includes the Remote Service Binding as well as grant user send on service. I tested with and without a cert. Same situation occurs. It still looks at that one other certificate, even with new user. I have new error msg for test case without cert, as I just now tested again. Dialog security is unavailable for this conversation because there is no security certificate bound to the database principal (Id: 5). I create new cert with same new user, still looks at other cert. – Kourtney Feb 23 '18 at 22:33
  • 1
    I have created a shared link to the documents containing the results. Please let me know if you have any troubles with this. Thanks. drive.google.com/open?id=1zPnsAhBMWD24RTKeT1hORXAHJ98BVNvF – Kourtney Mar 5 '18 at 21:18
  • 1
    NOTE: In the documents you will see source and target "DMZ-DMZDB1--PROD-PRDDB2", and this is the newly added cert/service with password different from the rest. The issue is with the new service using the "DB2Web" cert, instead of the new cert. I originally intended on using this cert "PROD-PRDDB2--DMZ-DMZDB1". – Kourtney Mar 5 '18 at 21:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.