Using Service Broker Queuing between servers in different domains

For good reason, Microsoft has made this a secure process.  At the same time, since this is a technical process, they have not made it easy to use.

I am writing these steps as I do this for the first time, so you will be learning as I learn.

1. Create Database Master Key
I already noted that the queues do not work if you do not have a database master key.  It is still needed now.

Make sure that you are logged into the database that contains your service broker

CREATE MASTER KEY ENCRYPTION BY PASSWORD='YourK3yH3r3'

As previously noted in another post, I am depending upon Professional Microsoft SQL Server 2012 Administration for much of the information in this post.

It is recommended that you backup and save the key externally.  The command to backup the key is:

backup master key to file='d:\temp\databaseMasterKey.key' encryption by password ='YourPasswordHere'

Question for home study:  If you restore the database and have to recreate the database master key, can you do it with the CREATE MASTER KEY command, or must you use your backed up version of the original key?

Rather than tempt fate, or take the time to experiment, I backup up the master key and saved it in another cloud.

I found that the master key was owned by the Administrator account.  As I don't run as that account, I did not have rights to move or copy the backup files.  I had to change the ownership to myself before I could copy it to email to take it offsite.

2. Create Certificates on each server
Since we are not using domain security, SQL Server will depend upon certificates to validate the identity of each server.  The following command needs to be run on each server, with the appropriate server names inserted.

use master
go

CREATE CERTIFICATE YourCertificateName
WITH SUBJECT = 'Description of Certificate',      
START_DATE = '1/1/2011',      
EXPIRY_DATE = '12/31/2099'

The certificate name should reference the server name and the instance of the database that the certificate is being created for.

REMEMBER: This has to be done on each server.

3. Exchanging Certificates

We have to back up the certificate on EACH server and move it to the other server.  

First, we have to backup the certificate to a file so that we have a file that we move.  Issue the following command:

backup certificate YourCertificateName 

       to File ='d:\temp\YourCertificateName.cer'

The filename is completely arbitrary, but the certificate name is the name that you used when creating the certificate above.

I use RDP to connect from one server to the other,   Again, I have to change the ownership of the file to allow me to copy it to the other server.

Now that the certificates have been copied to the other servers, you need to instantiate the certificates on each server:

create certificate OtherComputerCertificate

       from File ='d:\temp\OtherComputerCertificate.cer'

This has to be done once on each server, referencing the certificate from the other server.

4. Establishing Endpoints

Defining the endpoint tells the Service Broker how to talk to other servers.  It defines the TCP parameters, as well as the security used.

The following commands have to be issued on EACH server:

CREATE ENDPOINT ServiceBrokerEndpoint 

STATE = STARTED AS TCP (LISTENER_PORT = 4022, LISTENER_IP=ALL) 

FOR SERVICE_BROKER 

               (AUTHENTICATION = CERTIFICATE YourCertificateName,

ENCRYPTION = REQUIRED ALGORITHM RC4)

Note that the endpoint uses its own certificate in the command.  That text will have to be changed to support each computer's naming convention.

Any open port may be used.  The default port for this is 4022, but any unassigned ports between 1024 and 32767 may be used,

5.  Open port on firewalls

Make sure that the port that you specify is open through any firewalls that you have in place.  On my server, I have windows firewall, and I also have the cloud vendor's firewall service.

6. Setting up the Route

The initiator needs to know who to connect to.  It uses a route to do this.

The route needs to know a few things, starting with the Broker Instance.  On the target machine:

select service_broker_guid from sys.databases where name= 'yourTargetDatabase'

Back on the source machine, create the route, using the broker guid that you retrieved above.:

create route ConnectToCore

with Service_Name ='MySourceService',

BROKER_INSTANCE = '9FA8BB55-FEC7-4A6B-AED1-F3BAFC2D7190',

ADDRESS = 'TCP://www.targetmachine.net:52309'

The address refers to your target for the queue, and includes the port that you are using.

Now when you use MySourceService, it will connect to the target machine.