Troubleshooting Service Broker with Certificate Authentication

Service broker can be a tricky beast to get your head around, however if you can build up your skills around identifying issues with the setup and operation of it – this will certainly help add to your DBA skill set.

Here is a common scenario – your developers may have set up a process using service broker, however when it comes to implementation time things may not be working correctly. Here are a list of the things that you can check in an instance to instance service broker configuration. (not all things may apply to your configuration, this is just a list of possible things that could go wrong or be incorrectly configured)

Routes
Make sure the routes are pointing to the correct IP address and port number. Check twice, and if you have doubts about connectivity use telnet to connect to the address and port to verify connectivity.

Queues
Make sure the queues are not disabled. If they are, renenable and you may need to clear out old conversations first if the queue keeps disabling itself.

You can run this code to clear out conversations.

USE <dbname>

declare @counter int
declare @conversationHandle uniqueidentifier
SET @counter  = 1
select @counter=COUNT(1) from sys.conversation_endpoints
select top 1 @conversationHandle = conversation_handle -- select * 
 from sys.conversation_endpoints

while (@counter<>0
AND @conversationHandle IS NOT NULL)
begin
      
      print @counter
    end conversation @conversationHandle with cleanup

    select top 1 @conversationHandle = conversation_handle from sys.conversation_endpoints
      SET @counter=@counter-1
      
end 
 

Certificates
Check the thumbprints are matching at both ends, if not export and copy the certificate again until they are.

use master

select name, thumbprint from sys.certificates

Permissions
Make sure the users have been granted permission on the service broker endpoint

Tool – SQL Profiler
In SQL Profiler there are a number of service broker options that you can select. Open up profiler and select all events and all columns. Tick all the broker ones as in the below image. You might want to untick everything else especially on a busy database.

service-broker

Once you have the trace going you can fire through a few messages and view the output. Remember to check the normal SQL Server logs as well.

Tool – ssbdiagnose
ssbdiagnose is a SQL Server command line utility that you can run via a cmd window and switches. This is quite a handy tool and I have used it a couple of times when I was struggling to get to the bottom of a problem. You are best to read about it on BOL. Basically you put in a couple of switches and details and it outputs any errors it can find in the configuration.

As you fix up different implementations of service broker your skills and experience will continue to grow! If you have any tips you would like to share please do so.

Rob StGeorge
Senior SQL Server Database Administrator residing in Auckland, NZ

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.