2

After configuring two Service Broker instances that successfully send msg to remote server on separate databases, I am trying to set up a second service broker instance on one of the same, successful, databases.

I am receiving errors on ssbdiagnose, only. No other tools show errors. 2 Errors from ssbdiagnose, when running on the source server.

  1. 'target IP' 'targetDatabaseName' The certificate 'source cert name' used by user '' for dialog security is not deployed in the database.
  2. 'target IP' 'targetDatabaseName' The user '' from sourceDatabase '' on 'source IP' cannot be mapped into this database using certificates.

The source cert name from the first error msg is my previously existing certificate on the source server that is used to send msg to a different database on the target server.

So it appears to be looking at the wrong certificate.

When running ssbdiagnose on the target server, I get the following error:

  1. Login Failed. The login is from an untrusted domain and cannot be used with Windows Authentication.

Thank you

1

It appears you're using Dialog Level Security. This means you have deployed remote service bindings, users and certificates, successfully exchanged certificates and and granted SEND permission on the target service. For example, if you have a DB1 and a DB2, and a service S1 in DB1 and a service S2 in DB2, then, to begin a secure dialog from S1 to S2, you must have also:

  • create a certificate C1 in DB1 owned by user that owns S1, lets say U1 (usually dbo)
  • create a certificate C2 in DB2 owned by the user that owns S2, say U2 (again usually dbo, but this time dbo of DB2)
  • export public key of C1 and C2
  • create in DB1 an user, say U2' that will represent the S2 identity in DB1
  • import C2 public key as a certificate C2' in DB1, owned by U2'
  • create, in DB1, a remote service binding object that binds S2 to U2'. This way BEGIN DIALOG will know to use C2 public key to encrypt messages for S2
  • create in DB2 an user U1' that represents U1's identity in DB2
  • import C1 public key in DB2 as a certificate C1', owned by U1'. Because messages from S1 are signed with C1 private key (owned by U1), they will be identified in DB2 as being sent by U1' (the owner of C1', the corresponding public key).
  • grant SEND on service S2 to U1'

Again, if you say that messages between S1 and S2 are flowing fine, then all the above steps are already correctly done (not a small feat!).

If I understand correctly now you want to add a new service, say S3, also located in DB2, if I understand correctly, that needs to also accept messages from S1. I will make a simplifying assumption that S3 is owned by the same user as S2 (since usually the owner is dbo, this assumption is usually true). If so then you already have the necessary users/certificates exchanged, all you need to do tell SSB to use them:

  • in DB1 create a new remote service binding, RSB3, for service S3, using the same user U2'. BEGIN DIALOG will use the same certificate C2' to encrypt messages for S3 as for S2, but this is OK since both S3 and S2 are owned by the same user
  • in DB2 grant SEND permission on service S3 to user U1'.

This should be it, now S1 can talk with S2 and also with S3.

This certificate exchange and user ownership logic is used to 'map' a user from DB1 to DB2, without relying on Windows domains. This mapping is done via exchanging public key certificates and then following the certificate ownership: In DB1, U1 (ie. the owner of S1) owns a certificate with a private key, C1. The same certificate's public key exists in DB2 and is owned by U1' (a user in DB2). Therefore U1 'maps' to U1'. Whenever ssbdiagnose complains that a user cannot be 'mapped using certificates' it means that this chain is broken somewhere.

  • Thank you for your quick response. All assumptions are correct. – Kourtney Oct 18 '16 at 21:43
  • Are you telling me that I should be using the same certificate from DB1 and DB2 in DB3? I have the same databases setup in production with separate certificates and all is working fine. I am deploying a test and dev environment to match what is on production. I had everything working and some of the scope changed to require me to drop and recreate everything. Now I have this probably. Unable to add multiple certificates to one database. – Kourtney Oct 18 '16 at 21:47
  • There is no DB3 in my scenario. Can you clarify? – Remus Rusanu Oct 19 '16 at 16:00
  • I am sorry. My new service, S3 will not be receiving msg from S1. However; S3 and S2 share the same database DB2, then S3 will receive msg from, say S4 from DB3. DB1 and DB3 are on the same server, remote from DB2. I hope this makes since and that we are on the same page. – Kourtney Oct 19 '16 at 21:48
  • This morning I updated both source and target servers, SQL Server 2014 sp2, with the latest cumulative update in hopes to correct any permission bugs. This did not help. Please let me know if you have any additional suggestions/questions.Thanks – Kourtney Oct 25 '16 at 16:46
0

The reason for my errors, looking for a different certificate, is due to one of my certificate expiry dates being different from the rest; therefore, it was overlooking the certificate I wanted and using the certificate with the date furthest from today.

I fixed all the dates and this resolved the errors mentioned above.

However, some instances would work and others wouldn't. This time I was getting an error like, 'Object reference not set to an instance of an object', plus 11 other msg's in ssbdiagnose. This wasn't resolved until I configured each service broker instance in a specific order. I am speculating that this has something to do with the master key tying up with the source certificate.

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.