CODEWITHSUNDEEP
sql-serversocketswindows-10service-broker
milez
milez

Reputation: 2201

SQL Server Service Broker not sending messages, unable to start endpoint

I had a perfectly working SQL Server Service Broker this morning, until I tested how it recovers from crashing.

I forced a system shutdown on the sender during a messaging session between servers over a network. I was sending binary messages of about 5mb size. There are automatic procedures for sending, replying and receiving messages and ending conversations from both sides in place and my setup uses certificates for security.

I am now unable to send any messages from the server side.

Both sides of the messaging chain have queues on and it does not seem like poison message handling would be causing this. The sender side accepts new messages but is not sending them.

The sender side transmission queue has messages with transmission_status

The Service Broker endpoint cannot listen for connections due to the following error: '10013(An attempt was made to access a socket in a way forbidden by its access permissions.)'.

Running ALTER ENDPOINT myendpoint STATE = STARTED returns the same error as above.

Running select * from sys.endpoints shows the endpoint with state_desc = STARTED anyhow..

Running select state_desc from [sender_database].sys.conversation_endpoints shows state_desc = CONVERSING for all results.

Running SELECT COUNT(*) FROM dbo.sender_queue returns 0.

There is no other traffic to the port my endpoint is using, at least not any that is visible with netstat or the TCPView tool. The ports have rules to allow traffic from the firewall and sqlagent and sqlsrvr processes also have extra rules to be allowed.

Using ssbdiagnose tool with ssbdiagnose -level info configuration from service... from the sender side shows a (not new) error

The route for service sender_service is classified as REMOTE. This will result in the message being forwarded.

along with some other errors about certificates that have always been there even when messaging was working. Ssbdiagnose with RUNTIME flag shows nothing at all.

Ssbdiagnose from the target side now says an exception occurs during connection. The target database also has a couple of reply messages stuck in the transmission queue with an empty transmission_status.

Edit: Seems that occasionally the status on the target side changes to the error 10060 connection failed...

What more can I do to diagnose the problem and fix it?

Edit: I tried changing the port the endpoint uses but the same error is thrown.

Edit: I am able to ping the servers from each other. Ssbdiagnose with RUNTIME option on target side says it cannot find the connection to the SQL Server that corresponds to the routing address of my sender endpoint/database.

Upvotes: 0

Views: 2123

Answers (1)

Remus Rusanu
Remus Rusanu

Reputation: 294407

The Service Broker endpoint cannot listen for connections due to the following error: '10013(An attempt was made to access a socket in a way forbidden by its access permissions.)'

WSAEACCESS (10013) is a rather unusual socket listen error. I never encountered it before. A quick search reveals KB3039044: Error 10013 (WSAEACCES) is returned when a second bind to a excluded port fails in Windows which is an acknowledged bug in Windows Server 2008R2, 2012 and 2012R2 when excluding a range of ports (netsh ... add excludedportrange ...). So my first question is, are you on one of the affected server OSes and are you actually using a network port exclusion range?

I strongly urge you to open a Microsoft support case for this issue and follow up with them, making sure networking guys are involved (again, WSAEACCESS is rather unusual symptom). This is not one of the usual issues and it is difficult to diagnose over forums discussion.

Upvotes: 1

Related Questions