Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Service broker traverses domain boundaries


Service broker traverses domain boundaries

Author
Message
anand_vanam
anand_vanam
SSC-Addicted
SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)

Group: General Forum Members
Points: 400 Visits: 343
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.
Evil Kraig F
Evil Kraig F
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5695 Visits: 7660
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
Nils Gustav Stråbø
Nils Gustav Stråbø
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2013 Visits: 3575
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
anand_vanam
SSC-Addicted
SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)

Group: General Forum Members
Points: 400 Visits: 343
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ø
Nils Gustav Stråbø
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2013 Visits: 3575
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
anand_vanam
SSC-Addicted
SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)

Group: General Forum Members
Points: 400 Visits: 343
Thanks Nils; The scripts are very helpful.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search