Service Broker--between two SQL Servers

Two SQL Server: SERVER and KingPC, which belong to the same domain: VIP.

In SQL Server A: Server, database Adata is created.

In SQL Server B: KingPC, database Bdata is created.

Try to remember that SEND permission is granted on service and RECEIVE permission is granted on queue.

Owner of the initiating service must have SEND permission on the target service. How to achieve that? From the following script, you must pay attention to CREATE USER WITHOUT LOGIN and associate the USER with a certificate.

Owner of a service must have RECEIVE permission for its queue. This is easy to understand.

Communication between service broker endpoints uses SQL server service account, if the Windows authentication is applied. Service broker uses SEND and RECEIVE permissions.

If you have problems, SELECT * FROM sys.transmission_queue. Its status column at the far right will have error messages.

For example, I experienced the following error messages:

"Dialog security is not available for this conversation because there is no remote service binding for the target service. Create a remote service binding, or specify ENCRYPTION = OFF in the BEGIN DIALOG statement."

"An error occurred while receiving data: '10054(An existing connection was forcibly closed by the remote host.)'."

"Connection handshake failed. The login 'VIP\SQLagent' does not have CONNECT permission on the endpoint. State 84."

SCRIPTS:

--run this script in SQL SERVER A
USE Adata
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD='password'
GO
ALTER DATABASE Adata
SET ENABLE_BROKER
GO
/* in this simple example, you can set TRUSTWORTHY OFF.
TRUSTWORTHY is used fof Database modules (for example, user-defined functions or stored procedures) that use an impersonation context can access resources outside the database.
*/
ALTER DATABASE Adata
SET TRUSTWORTHY ON
GO
CREATE MESSAGE TYPE [initMessage] VALIDATION=NONE;
GO
CREATE MESSAGE TYPE [respMessage] VALIDATION=NONE;
GO
CREATE CONTRACT mContract
( [initMessage] sent by Initiator,
[respMessage] sent by target)
go
CREATE QUEUE mQueue WITH STATUS=ON,RETENTION=ON
GO
CREATE SERVICE mService ON QUEUE mQueue
(mContract);
GO
/* /////////////////////////////////////////////////// */
CREATE USER aUser WITHOUT LOGIN;
GO
CREATE CERTIFICATE [aUserCertificate] AUTHORIZATION aUser
WITH SUBJECT='Certificate for the mService in Adata of SQL Server A';
GO
ALTER AUTHORIZATION ON SERVICE::mService TO [aUser]
GO
GRANT RECEIVE ON mQueue TO aUser;
GO
--C:\Certificates folder can be changed
BACKUP CERTIFICATE [aUserCertificate]
TO FILE='C:\Certificates\aUserCertificate.cer'
/* ///////////////////////////////////////////////////
Copy the C:\Certificates\aUserCertificate.cer to SQL Server B;
Switch to SQL Server B and configure the service broker.
//////////////////////////////////////////////////// */
/* Assume that you have bUserCertificate.cer copied to C:\Certificates folder
From the following, I understand a little bit more about SQL server security.
A Database User can exist without SQL Server login.
*/
CREATE USER bUser WITHOUT LOGIN;
GO
CREATE CERTIFICATE [bUserCertificate] AUTHORIZATION bUser
FROM FILE='C:\Certificates\bUserCertificate.cer';
GO
GRANT SEND ON SERVICE::mService TO bUser
GO
/* When mService (in SQL Server A) initiates a conversation
with pService (in SQL Server B), the bUser is used for authentication.
*/
CREATE REMOTE SERVICE BINDING mServiceTOpService
TO SERVICE 'pService' WITH USER=bUser;
GO
/* communication between SQL Servers needs ENDPOINT */
USE MASTER
GO
/* communication between ENDPOINTS uses SQL Server Login, not database user. */
CREATE ENDPOINT broker STATE=STARTED
AS TCP(LISTENER_PORT=7000)
FOR SERVICE_BROKER(AUTHENTICATION=WINDOWS);
GO
/* VIP\SQLservice is the service account of SQL Server B:KingPC.
*/
CREATE LOGIN [VIP\SQLservice] FROM WINDOWS;
GO
GRANT CONNECT ON ENDPOINT::broker TO [VIP\SQLservice];
GO
/* route--when communicating with pService in SQL Server B,
broker ENDPOINT of SQL Server B is contacted. */
USE Adata
GO
CREATE ROUTE TO_B WITH SERVICE_NAME='pService',
ADDRESS=N'TCP://KingPC:7000'
GO

=========================

--run this script in the SQL Server B :KingPC
USE Bdata
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD='password'
GO
ALTER DATABASE Bdata
SET ENABLE_BROKER
GO
/* in this simple example, you can set TRUSTWORTHY OFF.
TRUSTWORTHY is used fof Database modules (for example, user-defined functions or stored procedures) that use an impersonation context can access resources outside the database.
*/
ALTER DATABASE Bdata
SET TRUSTWORTHY ON
GO
CREATE MESSAGE TYPE [initMessage] VALIDATION=NONE;
GO
CREATE MESSAGE TYPE [respMessage] VALIDATION=NONE;
GO
CREATE CONTRACT mContract
( [initMessage] sent by Initiator,
[respMessage] sent by target)
go
CREATE QUEUE pQueue WITH STATUS=ON,RETENTION=ON
GO
CREATE SERVICE pService ON QUEUE pQueue
(mContract);
GO
/* /////////////////////////////////////////////////// */
CREATE USER bUser WITHOUT LOGIN;
GO
CREATE CERTIFICATE [bUserCertificate] AUTHORIZATION bUser
WITH SUBJECT='Certificate for the pService in Bdata of SQL Server B';
GO
ALTER AUTHORIZATION ON SERVICE::pService TO [bUser]
GO
GRANT RECEIVE ON pQueue TO bUser;
GO
--C:\Certificates folder can be changed
BACKUP CERTIFICATE [bUserCertificate]
TO FILE='C:\Certificates\bUserCertificate.cer'
/* ///////////////////////////////////////////////////
Copy the C:\Certificates\bUserCertificate.cer to SQL Server A;
Switch to SQL Server A and configure the service broker.
//////////////////////////////////////////////////// */
-- Assume that you have aUserCertificate.cer copied to C:\Certificates folder
CREATE USER aUser WITHOUT LOGIN;
GO
CREATE CERTIFICATE [aUserCertificate] AUTHORIZATION aUser
FROM FILE='C:\Certificates\aUserCertificate.cer';
GO
GRANT SEND ON SERVICE::pService TO aUser
GO
/* When pService (in SQL Server B) initiates a conversation
with mService (in SQL Server A), the aUser is used for authentication.
*/
CREATE REMOTE SERVICE BINDING pServiceTOmService
TO SERVICE 'mService' WITH USER=aUser;
GO
/* communication between SQL Servers needs ENDPOINT */
USE MASTER
GO
/* communication between ENDPOINTS uses SQL Server Login, not database user. */
CREATE ENDPOINT broker STATE=STARTED
AS TCP(LISTENER_PORT=7000)
FOR SERVICE_BROKER(AUTHENTICATION=WINDOWS);
GO
/* VIP\SQLagent is the service account of SQL Server A */
CREATE LOGIN [VIP\SQLagent] FROM WINDOWS;
GO
GRANT CONNECT ON ENDPOINT::broker TO [VIP\SQLagent];
GO
USE Bdata
GO
CREATE ROUTE TO_A WITH SERVICE_NAME='mService',
ADDRESS=N'TCP://Server:7000'
GO

===========

Test the communication

--run the script in SQL Server B: KingPC

USE bdata
GO
SET NOCOUNT ON
DECLARE @id uniqueidentifier
BEGIN TRAN
BEGIN DIALOG @id
FROM SERVICE pService
TO SERVICE 'mService'
ON CONTRACT [mContract]
WITH LIFETIME=600,encryption=on;
SEND ON CONVERSATION @id
MESSAGE TYPE initMessage (N'service is good.')
COMMIT
GO

--Run the script in SQL Server A: Server

USE Adata
GO
SET NOCOUNT ON
DECLARE @id uniqueidentifier;
DECLARE @body nvarchar(max);
DECLARE @type sysname;
BEGIN TRAN
WAITFOR
(RECEIVE TOP (1)
@type=message_type_name,
@id=conversation_handle,
@body=message_body
FROM [mQueue]
),TIMEOUT 60;
PRINT @body;
--SEND ON CONVERSATION @id
-- MESSAGE TYPE [respMessage] (N'what matters?');
END CONVERSATION @id;
COMMIT
GO