Service Broker between two machine step by step

2017年10月29日 / kirito / 0 Comments

STEP 1: Create host certificate for login.
HOST A:

use master;
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'kirito'
GO

CREATE CERTIFICATE HOST_A_cert   
   WITH SUBJECT = 'HOST_A certificate',   
   EXPIRY_DATE = '11/30/2099';  
GO  

CREATE ENDPOINT InstTargetEndpoint
STATE = STARTED
AS TCP ( LISTENER_PORT = 4022 )
FOR SERVICE_BROKER (AUTHENTICATION = CERTIFICATE HOST_A_cert );
GO

BACKUP CERTIFICATE HOST_A_cert TO FILE = 'C:\ssd_shared\tmp\HOST_A_cert.cer';  
GO  

HOST B:

USE master;  
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'kirito'
GO

CREATE CERTIFICATE HOST_B_cert   
   WITH SUBJECT = 'HOST_B certificate',   
   EXPIRY_DATE = '11/30/2099';  
GO  

CREATE ENDPOINT InstInitiatorEndpoint
STATE = STARTED
AS TCP ( LISTENER_PORT = 4022 )
FOR SERVICE_BROKER (AUTHENTICATION = CERTIFICATE HOST_B_cert );
GO

BACKUP CERTIFICATE HOST_B_cert TO FILE = 'C:\ssd_shared\tmp\HOST_B_cert.cer';  
GO   

STEP 2: Exchange host certificate
HOST A:

USE master;  
CREATE LOGIN HOST_B_login   
   WITH PASSWORD = 'kirito';  
GO  

--SELECT * FROM sys.server_principals  

CREATE USER HOST_B_user FOR LOGIN HOST_B_login;  
GO  

--SELECT * FROM sys.sysusers;  

CREATE CERTIFICATE HOST_B_cert  
   AUTHORIZATION HOST_B_user  
   FROM FILE = 'C:\ssd_shared\tmp\HOST_B_cert.cer'
GO  

--SELECT * FROM sys.certificates  

--select * from sys.endpoints

GRANT CONNECT ON ENDPOINT::InstTargetEndpoint TO [HOST_B_login];  
GO  

HOST B:

USE master;  

CREATE LOGIN HOST_A_login WITH PASSWORD = 'kirito';  
GO  

CREATE USER HOST_A_user FOR LOGIN HOST_A_login  
GO  
--drop CERTIFICATE HOST_A_cert
CREATE CERTIFICATE HOST_A_cert  
   AUTHORIZATION HOST_A_user  
   FROM FILE = 'C:\ssd_shared\tmp\HOST_A_cert.cer';  
GO  

--select * from sys.endpoints 
GRANT CONNECT ON ENDPOINT::InstInitiatorEndpoint TO HOST_A_login  
GO  

STEP 3: Create Service Broker
HOST A:

USE master;
GO

CREATE DATABASE InstTargetDB;
GO

USE InstTargetDB;
GO

CREATE MASTER KEY
       ENCRYPTION BY PASSWORD = N'kirito';
GO

CREATE USER TargetUser WITHOUT LOGIN;
GO

--select * from sys.sysusers
--drop certificate InstTargetCertificate
CREATE CERTIFICATE InstTargetCertificate 
     AUTHORIZATION TargetUser
     WITH SUBJECT = 'Target Certificate',
          EXPIRY_DATE = N'12/31/2099';

BACKUP CERTIFICATE InstTargetCertificate
  TO FILE = N'C:\ssd_shared\tmp\InstTargetCertificate.cer';
GO

CREATE MESSAGE TYPE [//BothDB/2InstSample/RequestMessage]
       VALIDATION = WELL_FORMED_XML;
CREATE MESSAGE TYPE [//BothDB/2InstSample/ReplyMessage]
       VALIDATION = WELL_FORMED_XML;
GO

CREATE CONTRACT [//BothDB/2InstSample/SimpleContract]
      ([//BothDB/2InstSample/RequestMessage]
         SENT BY INITIATOR,
       [//BothDB/2InstSample/ReplyMessage]
         SENT BY TARGET
      );
GO

CREATE QUEUE InstTargetQueue;

CREATE SERVICE [//TgtDB/2InstSample/TargetService]
       AUTHORIZATION TargetUser
       ON QUEUE InstTargetQueue
       ([//BothDB/2InstSample/SimpleContract]);
GO

HOST B:

USE master;
GO

CREATE DATABASE InstInitiatorDB;
GO
USE InstInitiatorDB;
GO

CREATE MASTER KEY
       ENCRYPTION BY PASSWORD = N'kirito';
GO
CREATE USER InitiatorUser WITHOUT LOGIN;
GO

CREATE CERTIFICATE InstInitiatorCertificate
     AUTHORIZATION InitiatorUser
     WITH SUBJECT = N'Initiator Certificate',
          EXPIRY_DATE = N'12/31/2099';
GO

BACKUP CERTIFICATE InstInitiatorCertificate
  TO FILE = N'C:\ssd_shared\tmp\InstInitiatorCertificate.cer';
GO

CREATE MESSAGE TYPE [//BothDB/2InstSample/RequestMessage]
       VALIDATION = WELL_FORMED_XML;
CREATE MESSAGE TYPE [//BothDB/2InstSample/ReplyMessage]
       VALIDATION = WELL_FORMED_XML;
GO

CREATE CONTRACT [//BothDB/2InstSample/SimpleContract]
      ([//BothDB/2InstSample/RequestMessage]
         SENT BY INITIATOR,
       [//BothDB/2InstSample/ReplyMessage]
         SENT BY TARGET
      );
GO


CREATE QUEUE InstInitiatorQueue;
--drop service [//InstDB/2InstSample/InitiatorService]
CREATE SERVICE [//InstDB/2InstSample/InitiatorService]
       AUTHORIZATION InitiatorUser
       ON QUEUE InstInitiatorQueue
	   ([//BothDB/2InstSample/SimpleContract]);
GO

CREATE USER TargetUser WITHOUT LOGIN;
GO

--drop certificate InstTargetCertificate
CREATE CERTIFICATE InstTargetCertificate 
   AUTHORIZATION TargetUser
   FROM FILE = N'C:\ssd_shared\tmp\InstTargetCertificate.cer'
GO

--select * from sys.certificates

USE InstInitiatorDB;
CREATE ROUTE InstTargetRoute
WITH SERVICE_NAME =
       N'//TgtDB/2InstSample/TargetService',
     ADDRESS = N'TCP://192.168.2.150:4022';
GO

USE msdb;
CREATE ROUTE InstInitiatorRoute
WITH SERVICE_NAME =
       N'//InstDB/2InstSample/InitiatorService',
     ADDRESS = N'LOCAL';
GO

USE InstInitiatorDB;
CREATE REMOTE SERVICE BINDING TargetBinding
      TO SERVICE
         N'//TgtDB/2InstSample/TargetService'
      WITH USER = TargetUser;

GO

GRANT SEND ON SERVICE::[//InstDB/2InstSample/InitiatorService] to TargetUser

HOST A:

USE InstTargetDB
GO
CREATE USER InitiatorUser WITHOUT LOGIN;
GO

CREATE CERTIFICATE InstInitiatorCertificate
   AUTHORIZATION InitiatorUser
   FROM FILE = N'C:\ssd_shared\tmp\InstInitiatorCertificate.cer';
GO

USE InstTargetDB;
CREATE ROUTE InstInitiatorRoute
WITH SERVICE_NAME = N'//InstDB/2InstSample/InitiatorService',
     ADDRESS = N'TCP://192.168.159.130:4022';


USE msdb;
CREATE ROUTE InstTargetRoute
WITH SERVICE_NAME =
        N'//TgtDB/2InstSample/TargetService',
     ADDRESS = N'LOCAL';

USE InstTargetDB;
GRANT SEND
      ON SERVICE::[//TgtDB/2InstSample/TargetService]
      TO InitiatorUser;
GO

CREATE REMOTE SERVICE BINDING InitiatorBinding
      TO SERVICE N'//InstDB/2InstSample/InitiatorService'
      WITH USER = InitiatorUser;
GO

TEST Service Broker:
HOST A:

DECLARE @InitDlgHandle UNIQUEIDENTIFIER;
DECLARE @RequestMsg NVARCHAR(100);

BEGIN TRANSACTION;

BEGIN DIALOG @InitDlgHandle
     FROM SERVICE [//InstDB/2InstSample/InitiatorService]
     TO SERVICE N'//TgtDB/2InstSample/TargetService'
     ON CONTRACT [//BothDB/2InstSample/SimpleContract]
     WITH
         ENCRYPTION = ON;

SELECT @RequestMsg = N'<RequestMsg>Message for Target service.</RequestMsg>';

SEND ON CONVERSATION @InitDlgHandle
     MESSAGE TYPE [//BothDB/2InstSample/RequestMessage]
     (@RequestMsg);

SELECT @RequestMsg AS SentRequestMsg;

COMMIT TRANSACTION;
GO

HOST B:

DECLARE @RecvReqDlgHandle UNIQUEIDENTIFIER;
DECLARE @RecvReqMsg NVARCHAR(100);
DECLARE @RecvReqMsgName sysname;

BEGIN TRANSACTION;

WAITFOR
( RECEIVE TOP(1)
    @RecvReqDlgHandle = conversation_handle,
    @RecvReqMsg = message_body,
    @RecvReqMsgName = message_type_name
  FROM InstTargetQueue
), TIMEOUT 1000;

SELECT @RecvReqMsg AS ReceivedRequestMsg;

IF @RecvReqMsgName = N'//BothDB/2InstSample/RequestMessage'
BEGIN
     DECLARE @ReplyMsg NVARCHAR(100);
     SELECT @ReplyMsg =
        N'<ReplyMsg>Message for Initiator service.</ReplyMsg>';

     SEND ON CONVERSATION @RecvReqDlgHandle
          MESSAGE TYPE [//BothDB/2InstSample/ReplyMessage]
          (@ReplyMsg);

     END CONVERSATION @RecvReqDlgHandle;
END

SELECT @ReplyMsg AS SentReplyMsg;

COMMIT TRANSACTION;
GO

HOST A:

DECLARE @RecvReplyMsg NVARCHAR(100);
DECLARE @RecvReplyDlgHandle UNIQUEIDENTIFIER;

BEGIN TRANSACTION;

WAITFOR
( RECEIVE TOP(1)
    @RecvReplyDlgHandle = conversation_handle,
    @RecvReplyMsg = message_body
  FROM InstInitiatorQueue
), TIMEOUT 1000;

END CONVERSATION @RecvReplyDlgHandle;

-- Display recieved request.
SELECT @RecvReplyMsg AS ReceivedReplyMsg;

COMMIT TRANSACTION;
GO

For more information about Service broker, see service broker help and certificate help

For debugging Service Broker, see ssbdiagnose. Checking Configuration example will help you to find the configuration issue.

Categories SQL Server