Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Service broker traverses domain boundaries Expand / Collapse
Author
Message
Posted Tuesday, November 22, 2011 11:14 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, October 3, 2013 5:13 PM
Points: 400, Visits: 339
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.
Post #1210402
Posted Tuesday, November 22, 2011 11:53 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:58 PM
Points: 6,172, Visits: 7,246
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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1210430
Posted Tuesday, November 22, 2011 12:45 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 5:56 AM
Points: 1,839, Visits: 3,421
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.
Post #1210455
Posted Tuesday, November 22, 2011 3:57 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, October 3, 2013 5:13 PM
Points: 400, Visits: 339
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.
Post #1210613
Posted Wednesday, November 23, 2011 2:42 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 5:56 AM
Points: 1,839, Visits: 3,421
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'

Post #1210808
Posted Wednesday, February 1, 2012 12:43 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, October 3, 2013 5:13 PM
Points: 400, Visits: 339
Thanks Nils; The scripts are very helpful.
Post #1245289
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse