June 28, 2019 | Posted in: SQL Server
I haven’t blogged in a while, but this certainly warrants some sort of formal entry, because it may just help someone else with the same problem, or in the future I may be helping myself! (back to the future moment).
If you are trying to debug what on earth is going on in the land of service broker because your messages aren’t arriving and you get the following on the initiator side…
Service Broker received an error message on this conversation. Service Broker will not transmit the message; it will be held until the application ends the conversation.
…you may have come to the right place.
This one pretty well took up an entire day of my time trying to work out why the messages were stuck on the initiator with that error (Microsoft, if you are reading this, send the internal errors back to the initiator and set it on the sys.tranmission_queue.transmission_status!!!!). All configuration looked normal, ssbdiagnose didn’t give me any kind of leads, so I turned to my friend SQL Profiler. Every time I tried to reprocess the sending of the message to the target, I would see the following in the SQL logs on the target:
A new connection was established with the same peer. This connection lost the arbitration and it will be closed. All traffic will be redirected to the newly opened connection. This is an informational message only. No user action is required. State 79.
For me this was a red herring. It had nothing to do with my issue, and perhaps it doesn’t for you either.
Having switched on all broker events, filtered to the database in question (the target), and filtered out all of the unnecessary tracing I had no interest in, I eventually found this:
This message could not be delivered because the user with ID 0 in database ID 20 does not have permission to send to the service. Service name: ‘YOURSERVICE’
When I looked at that user it was [public] (at this point I felt like I was getting closer to the truth):
select * from master.sys.database_principals where principal_id = 0
I had some commands tucked away that I have used in the past, one of which is a command to GRANT SEND ON SERVICE to public. This seemed to fit what I was looking at quite well:
GRANT SEND ON SERVICE::[YOURSERVICE] TO [public] AS [dbo]
I went back to the initiator, and reprocessed the message one last time.. Eureka! And so therein lies the FIX.
You may also like to try these which have also helped me in the past in other situations:
- DROP/CREATE the Service Broker endpoint
- If you do the above, make sure you grant connect on the end point to public:
GRANT CONNECT ON ENDPOINT::[ServiceBrokerEndPoint] TO PUBLIC
- Make sure there are not two end points listening on port 4022 (if you have two versions of SQL Server installed one may come up listening on that port before the other, in which case was move them to using different ports)
- Check both the target and initiator have sys.databases.is_trustworthy_on = 1, sys.databases.is_broker_enabled = 1, sys.databases.suser_sname(owner_sid) = ‘sa’
- ssbdiagnose is a command line utility which can be useful in finding configuration issues between the initiator and the target… examples (switch initiator to target to test the other way):
Initiator to Target:
ssbdiagnose CONFIGURATION FROM SERVICE “YOURINITIATORSERVICE” -S “INITIATORSERVER” -d “INITIATORDATABASENAME” TO SERVICE “YOURTARGETSERVICE” -S “TARGETSERVERNAME” -d “TARGETDATABASENAME” ON CONTRACT “YOURCONTRACT”
- Grant receive on your initiator AND target queues:
GRANT RECEIVE ON [dbo].[your_initiator_queue] TO [public]
GRANT RECEIVE ON [dbo].[your_target_queue] TO [public]
- Search through SQL logs for some specific error or message:
set dateformat ymd;
declare @log table (LogDate datetime, ProcessInfo varchar(50), Description varchar(500))
insert into @log
EXEC master.dbo.sp_readerrorlog 0, 1, ‘handshake’
select * from @log
where LogDate > ’2019-06-28′
- Investigating broker connections and tasks:
select * from sys.dm_broker_connections
select * from sys.dm_broker_activated_tasks
select * from sys.dm_exec_connections where protocol_type = ‘Service Broker’ order by connect_time desc
- And finally, how I solved this, SQL Profiler. I have on some occasions fallen back to this tool and it has helped me find the error, and in some cases Service Broker is not very good at bubbling up the internal errors like the one above. When you find yourself in this situation, use SQL Profiler, find the problem, and fix it. It may save you a lot of time!
Consider this too… there were other services that needed the same fix so you may want to do them at the same time:
select ‘GRANT SEND ON SERVICE::[' + name + '] TO [public] AS [dbo]‘
where name like ‘%yoursearchstring%’