Search code examples
sqlsql-serverservice-broker

SQL Server Service Broker For Distributed App With Conversations Stucking in State of Conversing

I already have a little experience with SSB, but I just did this within databases on the same server. I never did this distributed, using two different servers on separated machines. So after a few research I found some youtube tutorials, and a nice tutorial with exactly what I want, security.

But I'm not able to deliver my message from Server A to Server B. The question is; How to find what I'm missing?

I know you all do have a job and are not here to review any code. But Im stucked on this for a couple days, and I just wanting some help, or some sharpenned eye to point me what I can't see.

I'm reading this book named "Pro SQL Server 2008 Service Broke", trying to find my answer.

I collected a few videos on youtube.

I tried this troubleshooting, but I couldn't use ssbdiagnose. I don't know where to find it.

Now, Im going to clarify and post my environment and what I have done so far.

Initiator is a SQL Server 2016 Express

  • Windows 2010 Pro
  • host: 192.168.20.44:1433
  • database: MarketPlace
  • firewall: Off
  • Server Configuration: TCP Enabled
  • SSB: port 4022 (tested just by telnet)

Target is a SQL Server 2016 Express

  • Windows Server 2012 R2 Standard Running on Hyper-V hosted by 192.168.20.44

  • host: 192.168.20.30:1433

  • database: MarketPlace

  • firewall: Off

  • Server Configuration: TCP Enabled

  • SSB: port 4022 (tested just by telnet)

Scripts for 192.168.20.44 The Initiator Server

--###
--All actions related to Basic Service Broker Objects and Dialog Security 
--will be performed in MarketPlace database of 192.168.20.44
--###
--1 Create the Basic Service Broker Objects
--###
USE MarketPlace
GO

ALTER DATABASE MarketPlace
SET Enable_broker;
GO

--1.1 Create Message Types
CREATE Message Type SenderMessageType validation = NONE;
GO

CREATE Message Type ReceiverMessageType validation = NONE;
GO

--1.2 Create Contract on the above message types
CREATE Contract PointOfSaleContract (
    SenderMessageType SENT BY INITIATOR
    ,ReceiverMessageType SENT BY TARGET
    );
GO

--1.3 Create an Initiator queue
CREATE QUEUE InitiatorQueue
    WITH STATUS = ON;
GO

--1.4  Create a Service on the queue and the contract
CREATE Service MarketPlaceService ON QUEUE InitiatorQueue (PointOfSaleContract);
GO

--###
--2 Set up Dialog Security
--###
--2.1 Create a master key in the local database i.e. the database we are going to use for our application.
CREATE Master KEY ENCRYPTION BY Password = 'gs53&"f"!385'
GO

--2.2 Create a user certificate
CREATE Certificate CertificateUserMarketPlace
    WITH Subject = 'CertificateUserMarketPlace'
        ,START_DATE = '2018-01-01'
        ,EXPIRY_DATE = '2020-12-31' ACTIVE
FOR BEGIN_DIALOG = ON;
GO

--2.3 Take a backup of the CertificateUserMarketPlace created and install it into the remote instance
--Copy the certificate to Stage Server Machine
--I Did install the certificates for current user within "automatically select the certificate store" option. Have to?
BACKUP CERTIFICATE CertificateUserMarketPlace TO FILE = 'C:\SSB\CertificateUserMarketPlace.cer';
GO

--2.4 Create a user with the same name as the user who has access rights on the other Database
--I Didn't understand this part. Should I create the very same user on Stage database?
CREATE User UserStage WITHOUT LOGIN
GO

--2.5 Create a user certificate from the user certificate backup file copied from the other server, 
--with authorization to the user created in Step 4
CREATE CERTIFICATE CertificateUserStage AUTHORIZATION UserStage
FROM FILE = 'C:\SSB\CertificateUserStage.cer';
GO

--2.6 Grant connect permissions to the user
GRANT CONNECT
    TO UserStage;
GO

--2.7 Grant send permissions to the user on the local service
GRANT SEND
    ON SERVICE::MarketPlaceService
    TO UserStage;
GO

--2.8 Create a Remote Service Binding with the user created.
CREATE REMOTE SERVICE BINDING ServiceBindingStage TO SERVICE 'StageService'
    WITH USER = UserStage
GO

--###
--3 Set up Transport Security
--All actions related to Transport Security
--will be performed in Master database of 192.168.20.44
--###
USE master
GO

--3.1 Create a master key for master database.
CREATE Master KEY ENCRYPTION BY Password = 'gs53&"f"!385'
GO

--3.2 Create certificate 
CREATE CERTIFICATE EndPointCertificateMarketPlace
    WITH Subject = 'EndPointCertificateMarketPlace'
        ,START_DATE = '2018-01-01'
        ,EXPIRY_DATE = '2020-12-31' ACTIVE
FOR BEGIN_DIALOG = ON;
GO

--3.3 Create End Point that support certificate based authentication
CREATE ENDPOINT ServiceBrokerEndPoint STATE = STARTED AS TCP (LISTENER_PORT = 4022)
FOR SERVICE_BROKER(AUTHENTICATION = CERTIFICATE EndPointCertificateMarketPlace, ENCRYPTION = SUPPORTED);
GO

--3.4 Take a backup of the certificate created and install it into the remote instance
--Copy the certificate to Stage
BACKUP CERTIFICATE EndPointCertificateMarketPlace TO FILE = 'C:\SSB\EndPointCertificateMarketPlace.cer';
GO

--3.5 Create certificate from the certificate backup file copied from the Target server
CREATE CERTIFICATE EndPointCertificateStage
FROM FILE = 'C:\SSB\EndPointCertificateStage.cer';
GO

--3.6 Create login from the certificate created in Step 3.5
CREATE LOGIN SSBLogin
FROM CERTIFICATE EndPointCertificateStage;
GO

--3.7 Grant the login, connect permissions on the end point.
GRANT CONNECT
    ON ENDPOINT::ServiceBrokerEndPoint
    TO SSBLogin
GO

SELECT *
FROM sys.service_broker_endpoints
GO

--###
--4 Create a Route
--###
USE MarketPlace
GO

--4.1 Get the UID from Stage database on 192.168.20.30 to use on the Route
SELECT service_broker_guid
FROM sys.databases
WHERE NAME = 'Stage';

--4.2 Use the UID from 4.1
CREATE Route RouteToStageService
    WITH SERVICE_NAME = 'StageService'
        ,BROKER_INSTANCE = 'A88B9743-EAFF-42FA-9404-0D551D4B29DB' -- Guid From Stage
        ,ADDRESS = 'TCP://192.168.20.30:4022'
GO

Scripts for 192.168.20.30 The Target Server

--###
--All actions related to Basic Service Broker Objects and Dialog Security 
--will be performed in Stage database of 192.168.20.30
--###
--1 Create the basic Service Broker Objects
--###
USE Stage
GO

ALTER DATABASE Stage
SET Enable_broker;
GO

--1.1 Create Message Types
CREATE Message Type SenderMessageType validation = NONE;
GO

CREATE Message Type ReceiverMessageType validation = NONE;
GO

--1.2 Create Contract on the above message types
CREATE Contract PointOfSaleContract (
    SenderMessageType SENT BY INITIATOR
    ,ReceiverMessageType SENT BY TARGET
    );
GO

--1.3 Create an Target queue
CREATE QUEUE TargetQueue
    WITH STATUS = ON;
GO

--1.4  Create a Service on the queue and the contract
CREATE Service StageService ON QUEUE TargetQueue (PointOfSaleContract);
GO

--###
--2 Set up Dialog Security
--###
--2.1 Create a master key in the local database i.e. the database we are going to use for our application.
CREATE Master KEY ENCRYPTION BY Password = '45Gme*3^&fwu'
GO

--2.2 Create a user certificate
CREATE Certificate CertificateUserStage
    WITH SUBJECT = 'CertificateUserStage'
        ,START_DATE = '2018-01-01'
        ,EXPIRY_DATE = '2020-12-31' ACTIVE
FOR BEGIN_DIALOG = ON;
GO
--2.3 Take a backup of the user certificate created and install it into the remote instance
--Copy the certificate to MarketPlace Server Machine
BACKUP CERTIFICATE CertificateUserStage TO FILE = 'C:\SSB\CertificateUserStage.cer';
GO

--2.4 Create a user with the same name as the user who has access rights on the other Database
CREATE User UserMarketPlace WITHOUT LOGIN
GO

--2.5 Create a user certificate from the user certificate backup file copied from the other server, 
--with authorization to the user created in Step 4
CREATE CERTIFICATE CertificateUserMarketPlace AUTHORIZATION UserMarketPlace
FROM FILE = 'C:\SSB\CertificateUserMarketPlace.cer';
GO

--2.6 Grant connect permissions to the user
GRANT CONNECT
    TO UserMarketPlace;
GO

--2.7 Grant send permissions to the user on the local service
GRANT SEND
    ON SERVICE::StageService
    TO UserMarketPlace;
GO

--2.8 Create a Remote Service Binding with the user created.
CREATE REMOTE SERVICE BINDING ServiceBindingMarketPlace TO SERVICE 'MarketPlaceService'
    WITH USER = UserMarketPlace
GO

--###
--3 Set up Transport Security
--All actions related to Transport Security
--will be performed in Master database of 192.168.20.30
--###
USE master
GO

--3.1 Create a master key for master database.
CREATE Master KEY ENCRYPTION BY Password = '45Gme*3^&fwu';
GO

--3.2 Create certificate and End Point that support certificate based authentication 
CREATE Certificate EndPointCertificateStage
    WITH Subject = 'EndPointCertificateStage'
        ,START_DATE = '2018-01-01'
        ,EXPIRY_DATE = '2020-12-31' ACTIVE
FOR BEGIN_DIALOG = ON;
GO

--3.3 Create End Point that support certificate based authentication
CREATE ENDPOINT ServiceBrokerEndPoint STATE = STARTED AS TCP (LISTENER_PORT = 4022)
FOR SERVICE_BROKER(AUTHENTICATION = CERTIFICATE EndPointCertificateStage, ENCRYPTION = SUPPORTED);
GO

--3.4 Take a backup of the certificate created and install it into the remote instance.
--Copy the certificate to MarketPlace
BACKUP CERTIFICATE EndPointCertificateStage TO FILE = 'C:\SSB\EndPointCertificateStage.cer';
GO

--3.5 Create certificate from the certificate backup file copied from the other server
CREATE Certificate EndPointCertificateMarketPlace
FROM FILE = 'C:\SSB\EndPointCertificateMarketPlace.cer';
GO

--3.6 Create login from the certificate created in Step 3.5
CREATE LOGIN SSBLogin
FROM CERTIFICATE EndPointCertificateMarketPlace;
GO

--3.7 Grant the login, connect permissions on the end point.
GRANT CONNECT
    ON ENDPOINT::ServiceBrokerEndPoint
    TO SSBLogin
GO

SELECT *
FROM sys.service_broker_endpoints
GO

--###
--4 Create a Route
--###
USE Stage
GO

--4.1 Get the UID from MarketPlace database on 192.168.20.44 to use on the Route
SELECT service_broker_guid
FROM sys.databases
WHERE NAME = 'Stage';

--4.2 Use the UID from 4.1
CREATE Route RouteToMarketPlaceService
    WITH SERVICE_NAME = 'MarketPlaceService'
        ,BROKER_INSTANCE = 'A18B5078-EB73-42D4-ACF9-4AF6549921A0' -- From MarketPlace
        ,ADDRESS = 'TCP://192.168.20.44:4022'
GO

Now when I run this on Initiator Server, the messages stucks:

USE MarketPlace
GO

SELECT conversation_handle, to_service_name, enqueue_time, cast(message_body AS XML)
FROM sys.transmission_queue;

DECLARE @ConversationHandle uniqueidentifier;
BEGIN TRANSACTION
  BEGIN DIALOG @ConversationHandle
  FROM SERVICE MarketPlaceService
  TO SERVICE 'StageService'
  ON CONTRACT PointOfSaleContract
  WITH ENCRYPTION = OFF;
  SEND
  ON CONVERSATION @ConversationHandle
  MESSAGE TYPE SenderMessageType
  ('<test>Test 001</test>')
COMMIT

SELECT conversation_handle, to_service_name, enqueue_time, cast(message_body AS XML)
FROM sys.transmission_queue;

SELECT conversation_handle, is_initiator, state_desc, far_service
FROM MarketPlace.sys.conversation_endpoints;

enter image description here

I can't see my message on my Target Server:

--###
--6 Sent Messages from MarketPlace
--###
USE Stage
GO

SELECT cast(message_body AS XML)
FROM TargetQueue;
GO

If you read till here. Thank you for your atention.

Solution

  • From "SQL Server 2005 Express Edition Overview":

    SQL Server Express can use Service Broker only in combination with other SQL Server 2005 editions. If SQL Server Express receives a Broker message from another Express instance, and if another SQL Server 2005 edition has not processed the message, then the message is dropped. So the message can originate from an Express instance and end up at one, but it must be routed through a non-Express instance if that is the case. You can check a Message Drop trace event that is accessible from the Profiler or use tracing stored procedures to track this type of occurrence. The error message associated with the dropped message includes verbiage to this effect: "This message has been dropped due to licensing restrictions."

    I know this is for a way older version but I couldn't find an equally verbose statement for 2016. However as "Editions and supported features of SQL Server 2016" still lists restrictions regarding the Service Broker ("No (Client only)") I do believe this is still valid for 2016.

    I did try that myself not too long ago with a very similar setup (also 2016 Express) and did not find a way to make it directly working.

    What I found as a workaround was using linked servers. I send the BEGIN DIALOG CONVERSATION ... SEND ON CONVERSATION ... code to the remote server via the link and have it executed there using sp_executesql (internally the Message Broker runs fine in Express Editions, it just cannot interchange messages with other Express Edition servers). Doing so I ran into a bug (apparently) with the DTC, that prevented the remote call to work initially. Instead it told me the DTC wasn't available on the remote server. However this could be fixed by setting the remote proc transaction promotion option to false.

    EXEC master.dbo.sp_serveroption @server = N'<the server link>', @optname = N'remote proc transaction promotion', @optvalue = N'true';

    It all worked well for quite some time. But some of the latest updates for SQL Server might have broken something. At least I'm facing some strange problems with my Service Broker setup similar to yours and they seem to have begun at the very day SQL Server was updated. But as this isn't a too import thing, I didn't yet find the time to investigate this in greater detail and find a solution. So I cannot give you a hint along the way for this right now. (The updates in question seem to be from around May this year. Sorry, but I don't have the KB numbers at hand now.)

    Another option, if you use this just for developing, might be to "upgrade" to the Developer Edition. This claims to be fully featured (equal to the Enterprise Edition AFAIK). Here's the link for the 2017 version. But I believe there is a 2016 around also, should you insist to stay on 2016.