Communicating between 3 Service Broker instances

I have a problem.
I am trying to set up Service Broker communication between 3 machines, all running the September CTP release.

I have included the scrip (edited) that I used to setup the 3 machines.
This scrip refers mainly to what was done to server1, with comments to indicate which parts were done on the other servers (commented out the connectionstring property to indicate a change of server)
I also added comments to indicate the copying/deleting of certificates; this script is intended to recreate the three setups if necessary so there is script that checks for the existence of objects before trying to create said objects. Certificates are deleted from the harddisks (so that the backup doesnt give errors), and then, copied to where the other servers can access them.
Also, the code used to generate this script gets the service_broker_guids from each server and sticks them in the right place in the CREATE ROUTE parts.
I apologise for the length of the script, but I didnt want to edit out too much and leave anyone who wanted to or could answer without enough information:

-
-- server1 master database stuff
-

-- conection string: server=server1;Trusted_Connection=true;Application Name=DDUTest
USE [master];
IF (NOT EXISTS(SELECT * FROM sys.symmetric_keys WHERE name = '##MS_DatabaseMasterKey##'))
BEGIN
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'DDU_MASTER_KEY';
END;
SELECT COUNT (*) FROM sys.symmetric_keys WHERE name = '##MS_DatabaseMasterKey##'

-- Deleted certificate from \\server1\server1_TS_MASTER_PUB.cer if it existed

-- conection string: server=server1;Trusted_Connection=true;Application Name=DDUTest
USE [master];
IF (NOT EXISTS(SELECT * FROM sys.certificates WHERE name = 'server1_TS_MASTER'))
BEGIN
CREATE CERTIFICATE
[server1_TS_MASTER]
AUTHORIZATION DBO
WITH
SUBJECT = 'server1_TRANSPORTSECURITY_MASTER',
START_DATE= '01/01/2005';
END;
BACKUP CERTIFICATE
[server1_TS_MASTER]
TO FILE ='\\server1\server1_TS_MASTER_PUB.cer';
SELECT COUNT(*) from sys.certificates WHERE name = 'server1_TS_MASTER';

-- conection string: server=server1;Trusted_Connection=true;Application Name=DDUTest
IF (NOT EXISTS(SELECT * FROM sys.endpoints WHERE name ='server1_Endpoint'))
BEGIN
CREATE ENDPOINT [server1_Endpoint]
STATE = STARTED
AS TCP
(
LISTENER_PORT = 4022,
LISTENER_IP = ALL
)
FOR SERVICE_BROKER
(
AUTHENTICATION = CERTIFICATE [server1_TS_MASTER],
ENCRYPTION = DISABLED
)
END;
SELECT COUNT(*) FROM sys.endpoints WHERE name = 'server1_Endpoint';

-
-- Copy \\server1\server1_TS_MASTER_PUB.cer to \\server2\server1_TS_MASTER_PUB.cer
-- Copy \\server1\server1_TS_MASTER_PUB.cer to \\server3\server1_TS_MASTER_PUB.cer
-

-- conection string: server=server1;Trusted_Connection=true;Application Name=DDUTest
USE [master];
IF( NOT EXISTS (SELECT * FROM sys.syslogins WHERE name ='DDU_MASTER_TS_LOGIN'))
BEGIN
CREATE LOGIN
[DDU_MASTER_TS_LOGIN]
WITH
PASSWORD = 'DDU_TS_LOGIN';
END;
SELECT COUNT(*) FROM sys.syslogins WHERE name = 'DDU_MASTER_TS_LOGIN';

-- conection string: server=server1;Trusted_Connection=true;Application Name=DDUTest
USE [master];
IF (NOT EXISTS(SELECT* FROM sys.sysusers WHERE name = 'DDU_MASTER_TS_USER'))
BEGIN
CREATE USER
[DDU_MASTER_TS_USER]
FROM LOGIN
[DDU_MASTER_TS_LOGIN]
END;
GRANT CONNECT ON ENDPOINT::[server1_Endpoint] TO [DDU_MASTER_TS_LOGIN];
SELECT COUNT(*) FROM sys.sysusers WHERE name ='DDU_MASTER_TS_USER';

-
-- do similar to the above on server2 and server3
-

-
-- create certificates from other servers on local server, authorized to DDU_MASTER_TS_USER
-

-- conection string: server=server2;Trusted_Connection=true;Application Name=DDUTest
USE [master];
IF (NOT EXISTS(SELECT * FROM sys.certificates WHERE name = 'server1_TS_MASTER_PUB'))
BEGIN
CREATE CERTIFICATE
[server1_TS_MASTER_PUB]
AUTHORIZATION
[DDU_MASTER_TS_USER]
FROM FILE ='\\server2\server1_TS_MASTER_PUB.cer'
END;
SELECT COUNT(*) from sys.certificates WHERE name = 'server1_TS_MASTER_PUB';

-- conection string: server=server3;Trusted_Connection=true;Application Name=DDUTest
USE [master];
IF (NOT EXISTS(SELECT * FROM sys.certificates WHERE name = 'server1_TS_MASTER_PUB'))
BEGIN
CREATE CERTIFICATE
[server1_TS_MASTER_PUB]
AUTHORIZATION
[DDU_MASTER_TS_USER]
FROM FILE ='\\server3\server1_TS_MASTER_PUB.cer'
END;
SELECT COUNT(*) from sys.certificates WHERE name = 'server1_TS_MASTER_PUB';

-- conection string: server=server1;Trusted_Connection=true;Application Name=DDUTest
USE [master];
IF (NOT EXISTS(SELECT * FROM sys.certificates WHERE name = 'server2_TS_MASTER_PUB'))
BEGIN
CREATE CERTIFICATE
[server2_TS_MASTER_PUB]
AUTHORIZATION
[DDU_MASTER_TS_USER]
FROM FILE ='\\server1\server2_TS_MASTER_PUB.cer'
END;
SELECT COUNT(*) from sys.certificates WHERE name = 'server2_TS_MASTER_PUB';

-- conection string: server=server3;Trusted_Connection=true;Application Name=DDUTest
USE [master];
IF (NOT EXISTS(SELECT * FROM sys.certificates WHERE name = 'server2_TS_MASTER_PUB'))
BEGIN
CREATE CERTIFICATE
[server2_TS_MASTER_PUB]
AUTHORIZATION
[DDU_MASTER_TS_USER]
FROM FILE ='\\server3\server2_TS_MASTER_PUB.cer'
END;
SELECT COUNT(*) from sys.certificates WHERE name = 'server2_TS_MASTER_PUB';

-- conection string: server=server1;Trusted_Connection=true;Application Name=DDUTest
USE [master];
IF (NOT EXISTS(SELECT * FROM sys.certificates WHERE name = 'server3_TS_MASTER_PUB'))
BEGIN
CREATE CERTIFICATE
[server3_TS_MASTER_PUB]
AUTHORIZATION
[DDU_MASTER_TS_USER]
FROM FILE ='\\server1\server3_TS_MASTER_PUB.cer'
END;
SELECT COUNT(*) from sys.certificates WHERE name = 'server3_TS_MASTER_PUB';

-- conection string: server=server2;Trusted_Connection=true;Application Name=DDUTest
USE [master];
IF (NOT EXISTS(SELECT * FROM sys.certificates WHERE name = 'server3_TS_MASTER_PUB'))
BEGIN
CREATE CERTIFICATE
[server3_TS_MASTER_PUB]
AUTHORIZATION
[DDU_MASTER_TS_USER]
FROM FILE ='\\server2\server3_TS_MASTER_PUB.cer'
END;
SELECT COUNT(*) from sys.certificates WHERE name = 'server3_TS_MASTER_PUB';

-
-- create database on server1
-

-- conection string: server=server1;Trusted_Connection=true;Application Name=DDUTest
use [master];
IF DB_ID (N'DDU') IS NOT NULL
BEGIN
ALTER DATABASE
[DDU]
SET TRUSTWORTHY ON;
End
ELSE
BEGIN
CREATE DATABASE
[DDU]
ON
(
Name = DDU_dat,
FILENAME = 'D:\Yukon test\data\DDU.mdf'
)
LOG ON
(
NAME = 'DDU_log',
FILENAME ='D:\Yukon test\data\DDU.ldf'
)
WITH TRUSTWORTHY ON;
End;
SELECT COUNT(*) FROM sys.databases WHERE name = 'DDU';

-- conection string: server=server1;Trusted_Connection=true;Application Name=DDUTest
USE [DDU];
IF (NOT EXISTS(SELECT * FROM sys.symmetric_keys WHERE name = '##MS_DatabaseMasterKey##'))
BEGIN
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'DDU_LOCAL_KEY';
END;
SELECT COUNT(*) FROM sys.symmetric_keys WHERE name ='##MS_DatabaseMasterKey##';

-
-- Deleted certificate from \\server1\server1_DS_LOCAL_PUB.cer if it existed
-

-- conection string: server=server1;Trusted_Connection=true;Application Name=DDUTest
USE [DDU];
IF (NOT EXISTS(SELECT * FROM sys.certificates WHERE name ='server1_DS_LOCAL'))
BEGIN
CREATE CERTIFICATE
[server1_DS_LOCAL]
WITH
SUBJECT = 'server1_DIALOGSECURITY_LOCAL',
START_DATE = '01/01/2005'
ACTIVE FOR begin_dialog = ON;
END;
BACKUP CERTIFICATE
[server1_DS_LOCAL]
TO FILE ='\\server1\server1_DS_LOCAL_PUB.cer';
SELECT COUNT(*) FROM sys.certificates WHERE name ='server1_DS_LOCAL';

-
-- Copy \\server1\server1_DS_LOCAL_PUB.cer to \\server2\server1_DS_LOCAL_PUB.cer
-- Copy \\server1\server1_DS_LOCAL_PUB.cer to \\server3\server1_DS_LOCAL_PUB.cer
-

-- conection string: server=server1;Trusted_Connection=true;Application Name=DDUTest
USE [DDU];
IF (NOT EXISTS(SELECT * FROM sys.sysusers WHERE name ='DDU_LOCAL_DS_USER'))
BEGIN
CREATE USER
[DDU_LOCAL_DS_USER]
WITHOUT
LOGIN;
END;
SELECT COUNT (*) FROM sys.sysusers WHERE name ='DDU_LOCAL_DS_USER';

-
-- do similar local database creation on server2 and server3
-

-
-- create certificates on local server from other servers, authorized to DDU_LOCAL_DS_USER
-

-- conection string: server=server2;Trusted_Connection=true;Application Name=DDUTest
USE [DDU];
IF (NOT EXISTS(SELECT * FROM sys.certificates WHERE name = 'server1_DS_LOCAL_PUB'))
BEGIN
CREATE CERTIFICATE
[server1_DS_LOCAL_PUB]
AUTHORIZATION
[DDU_LOCAL_DS_USER]
FROM FILE ='\\server2\server1_DS_LOCAL_PUB.cer'
END;
SELECT COUNT(*) from sys.certificates WHERE name = 'server1_DS_LOCAL_PUB';

-- conection string: server=server3;Trusted_Connection=true;Application Name=DDUTest
USE [DDU];
IF (NOT EXISTS(SELECT * FROM sys.certificates WHERE name = 'server1_DS_LOCAL_PUB'))
BEGIN
CREATE CERTIFICATE
[server1_DS_LOCAL_PUB]
AUTHORIZATION
[DDU_LOCAL_DS_USER]
FROM FILE ='\\server3\server1_DS_LOCAL_PUB.cer'
END;
SELECT COUNT(*) from sys.certificates WHERE name = 'server1_DS_LOCAL_PUB';

-- conection string: server=server1;Trusted_Connection=true;Application Name=DDUTest
USE [DDU];
IF (NOT EXISTS(SELECT * FROM sys.certificates WHERE name = 'server2_DS_LOCAL_PUB'))
BEGIN
CREATE CERTIFICATE
[server2_DS_LOCAL_PUB]
AUTHORIZATION
[DDU_LOCAL_DS_USER]
FROM FILE ='\\server1\server2_DS_LOCAL_PUB.cer'
END;
SELECT COUNT(*) from sys.certificates WHERE name = 'server2_DS_LOCAL_PUB';

-- conection string: server=server3;Trusted_Connection=true;Application Name=DDUTest
USE [DDU];
IF (NOT EXISTS(SELECT * FROM sys.certificates WHERE name = 'server2_DS_LOCAL_PUB'))
BEGIN
CREATE CERTIFICATE
[server2_DS_LOCAL_PUB]
AUTHORIZATION
[DDU_LOCAL_DS_USER]
FROM FILE ='\\server3\server2_DS_LOCAL_PUB.cer'
END;
SELECT COUNT(*) from sys.certificates WHERE name = 'server2_DS_LOCAL_PUB';

-- conection string: server=server1;Trusted_Connection=true;Application Name=DDUTest
USE [DDU];
IF (NOT EXISTS(SELECT * FROM sys.certificates WHERE name = 'server3_DS_LOCAL_PUB'))
BEGIN
CREATE CERTIFICATE
[server3_DS_LOCAL_PUB]
AUTHORIZATION
[DDU_LOCAL_DS_USER]
FROM FILE ='\\server1\server3_DS_LOCAL_PUB.cer'
END;
SELECT COUNT(*) from sys.certificates WHERE name = 'server3_DS_LOCAL_PUB';

-- conection string: server=server2;Trusted_Connection=true;Application Name=DDUTest
USE [DDU];
IF (NOT EXISTS(SELECT * FROM sys.certificates WHERE name = 'server3_DS_LOCAL_PUB'))
BEGIN
CREATE CERTIFICATE
[server3_DS_LOCAL_PUB]
AUTHORIZATION
[DDU_LOCAL_DS_USER]
FROM FILE ='\\server2\server3_DS_LOCAL_PUB.cer'
END;
SELECT COUNT(*) from sys.certificates WHERE name = 'server3_DS_LOCAL_PUB';

-
-- create service broker objects on database on server1
-

-- conection string: server=server1;Trusted_Connection=true;Application Name=DDUTest
USE [DDU];
IF (NOT EXISTS(SELECT * FROM sys.service_message_types WHERE name ='DDU_Message'))
BEGIN
CREATE MESSAGE TYPE
[DDU_Message]
VALIDATION = WELL_FORMED_XML
END;
SELECT COUNT(*) FROM sys.service_message_types WHERE name ='DDU_Message';

-- conection string: server=server1;Trusted_Connection=true;Application Name=DDUTest
USE [DDU];
IF (NOT EXISTS(SELECT * FROM sys.service_queues WHERE name ='server1_ujah'))
BEGIN
CREATE QUEUE
[server1_ujah]
WITH
STATUS = ON,
RETENTION = OFF
END;
SELECT COUNT (*) FROM sys.service_queues WHERE name ='server1_ujah';

-- conection string: server=server1;Trusted_Connection=true;Application Name=DDUTest
USE [DDU];
IF (NOT EXISTS(SELECT * FROM sys.service_contracts WHERE name ='DDU_Contract'))
BEGIN
CREATE CONTRACT
[DDU_Contract]
(
DDU_Message
SENT BY ANY
);
END;
SELECT COUNT (*) FROM sys.service_contracts WHERE name ='DDU_Contract';

-- conection string: server=server1;Trusted_Connection=true;Application Name=DDUTest
USE [DDU];
IF (NOT EXISTS(SELECT * FROM sys.services WHERE name ='server1_DDU_Service'))
BEGIN
CREATE SERVICE
[server1_DDU_Service]
ON QUEUE
[server1_ujah]
(
[DDU_Contract]
);
END;
GRANT SEND ON SERVICE::[server1_DDU_Service] TO [DDU_LOCAL_DS_USER] WITH GRANT OPTION;
GRANT SEND ON SERVICE::[server1_DDU_Service] TO [public];
GRANT RECEIVE ON [server1_ujah] TO [DDU_LOCAL_DS_USER] WITH GRANT OPTION;
SELECT COUNT (*) FROM sys.services WHERE name ='server1_DDU_Service';

-- conection string: server=server1;Trusted_Connection=true;Application Name=DDUTest
USE [DDU];
IF (NOT EXISTS(SELECT * FROM sys.routes WHERE name = 'server2_Route'))
BEGIN
CREATE ROUTE
[server2_Route]
WITH
service_name = 'server2_DDU_Service',
broker_instance = 'b3a03ba8-6d21-4c46-91e9-eed4a9629c92',
address = 'tcp://server2:4022'
END;
SELECT COUNT(*) from sys.routes WHERE name = 'server2_Route';

-- conection string: server=server1;Trusted_Connection=true;Application Name=DDUTest
USE [DDU];
IF (NOT EXISTS(SELECT * FROM sys.remote_service_bindings WHERE name = 'server2_RBS'))
BEGIN
CREATE REMOTE SERVICE BINDING
[server2_RBS]
TO SERVICE
'server2_DDU_Service'
WITH
USER = [DDU_LOCAL_DS_USER],
ANONYMOUS = ON;
END;
SELECT COUNT(*) from sys.remote_service_bindings WHERE name = 'server2_RBS';

-- conection string: server=server1;Trusted_Connection=true;Application Name=DDUTest
USE [DDU];
IF (NOT EXISTS(SELECT * FROM sys.routes WHERE name = 'server3_Route'))
BEGIN
CREATE ROUTE
[server3_Route]
WITH
service_name = 'server3_DDU_Service',
broker_instance = '086bf5c1-ea08-4829-9f3d-8eb3408a647c',
address = 'tcp://server3:4022'
END;
SELECT COUNT(*) from sys.routes WHERE name = 'server3_Route';

-- conection string: server=server1;Trusted_Connection=true;Application Name=DDUTest
USE [DDU];
IF (NOT EXISTS(SELECT * FROM sys.remote_service_bindings WHERE name = 'server3_RBS'))
BEGIN
CREATE REMOTE SERVICE BINDING
[server3_RBS]
TO SERVICE
'server3_DDU_Service'
WITH
USER = [DDU_LOCAL_DS_USER],
ANONYMOUS = ON;
END;
SELECT COUNT(*) from sys.remote_service_bindings WHERE name = 'server3_RBS';

-
-- do similar to the above on server2 and server3
-

This script runs without errors, on all three servers.

The issue is this: I can communicate perfectly between server1 and server2.
However, things get wierd when i try and do anything from or to server3.
I can send stuff from server3 to server1; stuff sent from server1 to server3 gets stuck in sys.transmission_queue, with no transmission_status message.

Nothing works between server2 and server3, meaning that messages get stuck in the respective sys.transmission_queues, with no transmission_status messages.

I used Profiler to try and see what was going on. The weird thing is this:
When sending worked (from server1 to server2) I saw the following stuff in the TextData, EventClass and EventSubClass columns (I can include other columns if more data is needed) on server1:

TextData EventClass EventSubClass Broker:Conversation Group 1 - Create STARTED_OUTBOUND Broker:Conversation 11 - Begin Dialog CONVERSING Broker:Conversation 1 - Send Dialog Broker:Message Classify 2 - Remote

I see absolutely no Service Broker activity on server2, and I have all the Service Broker events and columns selected in both traces.

When sending DIDNT work (server3 to server2) this is what I saw:

TextData EventClass EventSubClass STARTED_OUTBOUND Broker:Conversation 11 - Begin Dialog CONVERSING Broker:Conversation 1 - Send Dialog Broker:Message Classify 2 - Remote

Note that missing row for EventSubClass '1 - Create' in the second table.

Here is the script I use to send messages (I change the sender and recipient services, and recipient broker guid as necessary; also, the same thing happened whether i included or omitted the broker guid, which i gather is optional):

use ddu
declare @.handle uniqueidentifier;
declare @.msg xml;

set @.msg = '<MESSAGE><SUBJECT>Message subject</SUBJECT><BODY>Message from server3 </BODY></MESSAGE>';

begin dialog conversation
@.handle
from service
[server3_DDU_Service]
to service
'server2_DDU_Service',
'B3A03BA8-6D21-4C46-91E9-EED4A9629C92'
--'server1_DDU_Service',
--'79D8B0B3-D9CA-4DC6-8AA0-4A6003971397'
on contract
[DDU_Contract]
WITH
ENCRYPTION = off,
RELATED_CONVERSATION_GROUP = '771677D3-1335-DA11-B541-00114325CBB6';

send on conversation
@.handle
Message type
[DDU_Message] (@.msg);

Select @.handle

I realise this is a lot of stuff to look at, but I am stuck.

I found out something interesting the hard way. I have access to 5 servers, three of which are running the September CTP release, two the June CTP release.
When i tried to have all 5 serves talking to each other, nothing worked, i.e., stuff got stuck on sys.transmission_queue, with no transmission_status messages.

When i got the June CTP releases to talk only to each other (wiped the databases and rebuilt with the script), everything worked; messages flowed back and forth. So, could it be that you cant mix and match releases, which is acceptable... or that two is company and 3 plus is a crowd?

If you are able to begin a dialog and send a message successfully from server1 to server2, you should certainly see some broker events in server2's trace. Could you include the full trace on both server1 and server2 during the execution of begin dialog/send?
In the second example, the reason you are probably not seeing the 'Broker:Conversation Group' 1-Create event is perhaps because the conversation group already exists. Check sys.conversation_endpoints.
Also I'm a little confused with your security setup. Particularly:

i) For the adjacent security layer on server1, why are you mapping the identities of both server2 and server3 to a single user on server1? It is cleaner to have separate users for each adjacent SQL Server instance.

ii) Similarly at the dialog security layer, you are mapping the identities of both 'server2_DDU_Service' and 'server3_DDU_Service' to the same database principle 'DDU_LOCAL_DS_USER' instead of creating users for their exported public key certificates.

iii) For the dialog security layer, I hope you do understand that the 'ENCRYPTION = OFF' is overridden when an RSB exists.

iv) If you specify ANONYMOUS = ON in your RSB, the remote target service needs to grant send to public. I think you are doing this already.
Rushi Desai [MSFT]|||

I am not sure what you mean by a full trace so I turned every column/event on except for the user configurable ones. I couldnt save anything for server2: there was nothing to see in the trace window, and profiler threw an exception when I tried to save. I dont know how to attach the trace file, so please find the trace file as XML at the end of this post. FYI the server called GHQMSGWEB2 is server1 and GHQMSGWEB1 is server2.

As for the security setup confusion you had, I guess that reflects the confusion I have. I based my script off the example that Niels posted. In that example, there was one user, created on the master database, for transport security, and another created on the user database (DDU in my case) for dialog security. To make things simpler (I thought) for me, I wanted these two users on each server to be the same, so that I dont have to figure our which userid/password combo to use to connect to a database. I am not too conversant with the requirements of identities, especially at the server level, so if this is a hinderence to getting Service Broker working on all three servers, it can be changed.

From what you said... a RSB is not necessary? Is that right? I can still communicate between servers without defining any?

I would prefer it if I didnt have to bother either with transport or dialog security; I understand the need for security, but I dont need it for my purposes and it seems to complicate things. I asked Niels if that was possible, in a private email (before I found this forum) and was told that both security layers are required for queueing between servers.

For instance, after setting up the two users on a server, who do I have to login as to send or receive a message?
I tried pasting the xml file here. I got an error when i tried to post. Can I email you the trace file?

|||

I am not sure why you are unable to see the service broker trace events in the profiler on server2. Are you connecting to the right instance on server2 (in case there are multiple instances running on that machine). Do you have the right set of permissions?

You could simplify your setup a lot. If you start from scratch and you don't care about security either at the adjacent or dialog level, all you need to do is the following on each server instance:

In master:
1. Create master key for database 'master'.
2. Create certificate for user 'dbo' in database 'master'.
3. Create service broker endpoint using certificate created above.
4. Grant connect on above endpoint to the login 'Public'.

In your database:
1. Create master key.
2. Grant send on target service to user 'public'.
3. Create routes to initiator services on other servers.
4. Create routes to target services on other servers.

Now begin dialog with encryption = off and send.

|||

Rushi, thanks for your answer
I tried what you suggested and have full duplex communication between all three servers.
This is exactly what I wanted!

I still cant see stuff being done on the reception database using sql profiler, but, thats a minor inconvenience for now.

Thanks again.

|||Can you try running the profiler on a different machine (say server1) and connect to server2?|||Not sure what you are asking me to do
I did recheck the traces while sending messages from any one server to the other servers. In all cases I only saw stuff from the sending server, not from the receiving server, until I actually did a receive on the receiving server.

I selected all events except for stuff like locks, security and user configurable events; I was really looking for only Service Broker events, but I left the others in to see if there was any sign of activity. Ileft the columns checked as they came by default.

I did this stuff from a fourth server; the three servers I described above and in previous posts dont have Management Studio installed.

This wont be a concern for me unless things start going wrong with my applicaiton. Is there some setting I need to set to see the stuff that I am assuming the receiving servers should be doing?
Is my assumption right?

Thanks again for you previous answer