Service broker traverses domain boundaries

Question

Service broker traverses domain boundaries

anand_vanam

SSCrazy

Points: 2376
More actions
November 22, 2011 at 11:14 am

#263370

Hi All,
I am looking for references/Information on how to set up SQL Service Broker to send and receive message from databases across different domain.
Please provide any references, links where I can get information on this.
Thanks,
AK.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

That's going to be in endpoint and routing (for the keywords you need), and a discussion with your network administrator about what you can do to get shared trust between the domains. Otherwise you're going to have to do sql login style security and I recommend using encryption, which you can attach to the endpoints (or was that the route... one of those 🙂 )

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Nils Gustav Stråbø SSChampion Points: 11259 More actions · Answer 2

Unless there is a requirement to to use Windows authentication I would rather recommend certificate authentication between the servers.

I wrote a tutorial with examples (for personal use) a few years ago that has a step by step description on how to set up service broker between two instances that uses certificates for transport and dialog security. The only requirement if you use certificates is that the two servers can communicate over a port that you select when you create the service broker endpoints.

I can find it tomorrow when I'm at work a post it here if you want to.

anand_vanam SSCrazy Points: 2376 More actions · Answer 3

Nils Gustav Stråbø (11/22/2011)
Unless there is a requirement to to use Windows authentication I would rather recommend certificate authentication between the servers.
I wrote a tutorial with examples (for personal use) a few years ago that has a step by step description on how to set up service broker between two instances that uses certificates for transport and dialog security. The only requirement if you use certificates is that the two servers can communicate over a port that you select when you create the service broker endpoints.
I can find it tomorrow when I'm at work a post it here if you want to.

Hi Nils,

If you could post some scripts to setup the Service Broker end points, that will be very helpful for me.

As of now I got some notes "Pro SQL Server 2008 Service Broker" Book.

Thanks in Advance.

Nils Gustav Stråbø SSChampion Points: 11259 More actions · Answer 4

This is a copy/paste form my own document about transport security. This description only configures transport security between the two instances. You still need to set up dialog security which also involves creating certificates, exchange of public keys, creating users based on the certificates, granting SEND on services for the users etc. Let me know if you need info about this too.

Each instance of SQL Server creates a service broker endpoint. Only one service broker endpoint can exist per instance. The endpoint specifies which certificate it will use for authentication on the far side. The certificate must be created in the master database. The far endpoint has a certificate with the public key, and a login created from this certificate. The login must be granted connect permission on the endpoint.
The same is done at the far side. A certificate is created in the master database, a endpoint is created with authentication based on the certificate, the public key is copied to the remote server, and certificate is created with the public key, a login is created based on this certificate and granted connect permission on the endpoint.
Create a certificate in the master database, create an endpoint using the certificate for authentication, and backup the public key of the certificate to file.
On SERV1:
use master
go
create certificate ServiceBrokerEndpointCertificate
with subject='SERV1 service Broker Endpoint Certificate',
expiry_date='12/31/2999'
active for begin_dialog=on;
go
create endpoint ServiceBrokerEndpoint
state=started
as tcp(listener_port=4022)
for service_broker(
authentication=certificate ServiceBrokerEndpointCertificate,
encryption=supported
)
go
backup certificate ServiceBrokerEndpointCertificate
TO FILE = 'c:\program files\microsoft sql server\mssql.1\mssql\backup\SERV1ServerCert.cer'
Execute the same statement on SERV2:
use master
go
create certificate ServiceBrokerEndpointCertificate
with subject='SERV2 service Broker Endpoint Certificate',
expiry_date='12/31/2999'
active for begin_dialog=on;
go
create endpoint ServiceBrokerEndpoint
state=started
as tcp(listener_port=4022)
for service_broker(
authentication=certificate ServiceBrokerEndpointCertificate,
encryption=supported
)
go
backup certificate ServiceBrokerEndpointCertificate
TO FILE = 'c:\program files\microsoft sql server\mssql.1\mssql\backup\SERV2ServerCert.cer'
Copy the certificate file from SERV1 to SERV2 and vice versa. Create a certificate based on the public key, create a login based on the certificate and grant the login connect permissions:
On SERV1:
create certificate SERV2ServerSBCert from file='c:\program files\microsoft sql server\mssql.1\mssql\backup\SERV2ServerCert.cer'
go
create login sbSERV2 from certificate SERV2ServerSBCert
go
grant connect on endpoint::ServiceBrokerEndpoint to sbSERV2
On SERV2:
create certificate SERV1ServerSBCert from file='c:\program files\microsoft sql server\mssql.1\mssql\backup\SERV1ServerCert.cer'
go
create login sbSERV1 from certificate SERV1ServerSBCert
go
grant connect on endpoint::ServiceBrokerEndpoint to sbSERV1
Only one task remains, and that is to set up a route on SERV1 to SERV2, and on SERV2 to SERV1.
The broker_instance values are found in sys.databases on each server.
On SERV1:
create route SERV1ToSERV2Route
with service_name='DateAndTimeResponseService',
broker_instance='B8E0239E-25A3-4511-B8F5-806FA87C1547',
address='TCP://SERV2:4022'
On SERV2:
create route SERV2ToSERV1Route
with service_name='DateAndTimeRequestService',
broker_instance='144A7BFD-8204-4086-8D90-CAC116F0CC48',
address='TCP://SERV1:4022'

anand_vanam SSCrazy Points: 2376 More actions · Answer 5

anand_vanam

SSCrazy

Points: 2376

February 1, 2012 at 12:43 pm

#1441396

Thanks Nils; The scripts are very helpful.