Exporting and importing service listings into a database

Article
09/14/2005

You implemented a Service Broker service and now want others to be able to talk to it. But they will have to first create your message types and contracts to be able to begin a dialog and send messages to your target service. If their service does not reside in the same SQL Server instance, they will have to setup a broker endpoint and setup adjacent layer security to talk to your endpoint. Next they have to setup routing and dialog level security on their side and hand you the public key certificates and reverse route information so that you can setup routing and dialog level security on your side. All this can be quite tedious and prone to errors as some of you may have experienced with BrokerChallege 0.

To ease this process, we are releasing sample stored procs that can extract all the information needed to talk to your service into an XML file and then on the other side import the service listing XML file into the database. This service listing XML is to Service Broker what WSDL is to Web Services.

The package contributed by my teammate Remus Rusanu below contains two SQL files:

ServiceListing.Master.sql: Run this in the 'master' database.
ServiceListing.Database.sql: Run this in the database to export/import service listing.

Example of using the Service Broker Service Listing procedures

Before starting this example, the Service Broker endpoint has to be created and configured before in order for this example to work. If there is no Service Broker endpoint configured and started, one has to be created. Use select * from sys.service_broker_endpoints to verify if an Service Broker endpoint already exists. If no Service Broker exists, use these steps to create an Service Broker endpoint. Our example will use certificate based Service Broker Transport security. For this, a database master key is required in the master database. Here is a script that creates the Service Broker endpoint:

 -- 
-- replace <computer_name> with the actual machine name on which this script is run 
-- 
use Master; 
go 

create certificate [<computer_name>] 
   with subject = N'<computer_name>'; 
go 

create endpoint [ServiceBroker]
 state = started as tcp (listener_port = 4022) for service_broker 
   (authentication = certificate [<computer_name>]); 
go

First, the target service host instance administrator creates a database that will host the target service. A database master key is required for dialog security:

 
create database SampleServiceDb;
go

use SampleServiceDb;
create master key encryption by password = '....';

The administrator now has to install the Service Listing procedures in the SampleServiceDb. The ServiceListing.Database.sql script has be executed in the SampleServiceDb database.

After that, the target administrator creates the SampleService and prepares it for dialog security:

 
use database SampleServiceDb;
go

create message type [Sample/Request] validation = well_formed_xml;
create message type [Sample/Reply] validation = well_formed_xml; 
create contract [SampleContract] 
  ([Sample/Request] sent by initiator, 
   [Sample/Reply] sent by target);
create queue [SampleQueue]; 
create service [SampleService] on queue [SampleQueue] ([SampleContract]); 
go

exec sp_secure_service [SampleService];

Then the administrator exports the service listing for this service:

 use database SampleServiceDb;
go

declare @service_listing xml;
exec sp_export_service_listing [SampleService], @service_listing output;
exec sp_save_service_listing @service_listing, N'c:\SampleService.ServiceListing.xml';
go

The service listing was saved in the file c:\SampleService.ServiceListing.xml and it will contain an XML document similar to the following:

definition xmlns="https://schemas.microsoft.com/SQL/ServiceBroker/ServiceListing" author="REDMOND\rushid" timestamp="2005-09-13T18:35:08.397" version="1.0">
   <message xmlns="https://schemas.microsoft.com/SQL/ServiceBroker/ServiceListing" name="Sample/Reply" validation="XML"/>
<message xmlns="https://schemas.microsoft.com/SQL/ServiceBroker/ServiceListing" name="Sample/Request" validation="XML"/>
<contract xmlns="https://schemas.microsoft.com/SQL/ServiceBroker/ServiceListing" name="SampleContract">
<message xmlns="https://schemas.microsoft.com/SQL/ServiceBroker/ServiceListing" name="Sample/Request" sent-by="INITIATOR"/>
<message xmlns="https://schemas.microsoft.com/SQL/ServiceBroker/ServiceListing" name="Sample/Reply" sent-by="TARGET"/>
</contract>
<service xmlns="https://schemas.microsoft.com/SQL/ServiceBroker/ServiceListing" name="SampleService" broker-instance="FDF5116A-F129-48E6-91BB-C9902ECD4601" public-access="No">
<contract xmlns="https://schemas.microsoft.com/SQL/ServiceBroker/ServiceListing" name="SampleContract"/>
<certificate xmlns="https://schemas.microsoft.com/SQL/ServiceBroker/ServiceListing" issuer-name="SampleService" serial-number="b2 4d 4b 02 93 a7 a8 93 4e c8 24 bd 05 74 6d c0">
<blob>MIIBszCCARygAwIBAgIQsk1LApOnqJNOyCS9BXRtwDANBgkqhkiG9w0BAQUFADAYMRYwFAYDVQQDEw1TYW1wbGVTZXJ2aWNlMB4XDTA1MDkxMzE4MzQzOVoXDTA2MDkxMzE4MzQzOVowGDEWMBQGA1UEAxMNU2FtcGxlU2VydmljZTCBnzANBgkqhkiG9w0BAQEFAAOBjQAwgYkCgYEAoEOMkpKAICM168qG5JWXruYnKvEa2saUIxm+OmkE8f5VIthQ4cVaV5adnPKNKjJ/oWYf0v99o82amRNSJqFUCoyxwyNH5A7LCHjNTmIRrZvZb9hxCn66hJhkvZDEWNUFpC4yubokhKaP5gPDE2k8LAVO+KTuqlZi9EPvRcA0Il0CAwEAATANBgkqhkiG9w0BAQUFAAOBgQBFdCGeawEHJg0QD5lLiQ/nzFJfCaRIyc14aZfmr5ywXokMhtmWSzoE7Ty/GfvSXL86JLZsbJ87mAhFhF7px2WGWS0PUCpdE+y84NeZfvq98scnt4XTcjNuhJ46arjKlmX4c4cikp/gfMKmQc+YnzWBtgFolOMo+e7x1HHbTKf6pg==</blob>
</certificate>
   </service>
<endpoint xmlns="https://schemas.microsoft.com/SQL/ServiceBroker/ServiceListing" machinename="rushi.desai.name" tcp-port="4023" authentication="CERTIFICATE" encryption="NONE, AES" public-access="No">
<certificate xmlns="https://schemas.microsoft.com/SQL/ServiceBroker/ServiceListing" issuer-name="rushid01 IDENTITY" serial-number="3e e2 04 5d 08 0b f2 b9 44 67 a0 06 ee aa f7 5d">
         <blob>MIIBuzCCASSgAwIBAgIQPuIEXQgL8rlEZ6AG7qr3XTANBgkqhkiG9w0BAQUFADAcMRowGAYDVQQDExFydXNoaWQwMSBJREVOVElUWTAeFw0wNTA5MTMxNjA0MDJaFw0wNjA5MTMxNjA0MDJaMBwxGjAYBgNVBAMTEXJ1c2hpZDAxIElERU5USVRZMIGfMA0GCSqGSIb3DQEBAQUAA4GNADCBiQKBgQD6U4JDzBDCPLiGHQ3VFEghiw5Rl72HyHODw6+9Y6JtMdhuB3KDf3liRRV5/YZCK2R97cRJXqnTraR/xW9Jx2H7HSmr2nB5NoEF3k/bDeekcrfOHLCggYiyDdVZQXkp7g9Ak7elDjYhjyaYN6CBTGJI+OLlvxSObjXgNWjuD/OD9wIDAQABMA0GCSqGSIb3DQEBBQUAA4GBAN1xjIRYv3sPLX1M91OofdMwG5yQiqOoxIfbdqPwgGwAR4hlJ7zWXncT/UEeBddg6QeNsR2PaMzWW/ZhfI78ji+IMMmmthV9avvZu8AkdaO2fSjiYB54wguksi4SyZAMo5O9gAxOsQs51zPtBYiEz0FQJ+q01GzKhlGpJ/0cyqzk</blob>
</certificate>
</endpoint>
</definition>The file contains all the information needed for a different host to begin dialogs and send requests to this service. Note that there is no secret information in the service listing. The service administrator now sends this information to the administrator of the initiator host. With the service listing received from the target, the initiator admin can start setting up his site for a secure dialog with the target service. First, he creates a database to host the initiator service. A database master key is required for dialog security:

 
create database InitiatorServiceDb;
go

use InitiatorServiceDb;
create master key encryption by password = '....';
go

The initiator administrator now has to install the Service Listing procedures in the InitiatorServiceDb. The ServiceListing.Database.sql script has be executed in the InitiatorServiceDb database.

The initiator admin can now import the service listing received from the target service administrator:

 
use InitiatorServiceDb;
go

declare @service_listing xml;
exec sp_load_service_listing N'c:\SampleService.ServiceListing.xml', @service_listing output;
exec sp_import_target_service_listing_at_initiator @service_listing, [SampleServiceOwner];
go

The import has created the message types, the contract, a route to the target SampleService, a remote service binding, a user that locally represents the owner of the target SampleService and the target SampleService certificate was extracted from the listing and imported into the database. The administrator can now verify that all these completed succesfully:

 use InitiatorServiceDb;
go
  
select * from sys.service_message_types;
select * from sys.service_contracts;
select * from sys.routes;
select * from sys.database_principals;
select * from sys.certificates;
select * from sys.remote_service_bindings;
go

In addition, if this is the first service listing received from the SQL instance that hosts the target service, the Service Broker transport security also has to be imported:

 
---- replace <target-service-computer-name> with the actual name of the machine hosting the target service--
use InitiatorServiceDb;
go

declare @service_listing xml;
exec sp_load_service_listing N'c:\SampleService.ServiceListing.xml', @service_listing output;
exec master..sp_import_service_listing_grant_connect_on_endpoint @service_listing, '....', [<target-service-computer-name>];
go

The initiator administrator can now create a service that will be used to begin the dialogs with the target service:

 use InitiatorServiceDb;
go

create queue [InitiatorQueue];
create service [InitiatorService] on queue [InitiatorQueue];
go

exec sp_secure_service [InitiatorService];
go

The initiator service admin can now export the initiator service listing and send it to the target service admin. This step is needed for two reasons:

the target service administrator needs to grant SEND permission on the target service to the initiator service

the target service database must contain a route back to the initiator database, so that acks reply messages can arrive back at the initiator

First, the initiator admin exports the initiator service listing:

 use InitiatorServiceDb;
go

declare @service_listing xml;
exec sp_export_service_listing [InitiatorService], @service_listing output;
exec sp_save_service_listing @service_listing, N'c:\InitiatorService.ServiceListing.xml';
go

The resulted listing is saved in the c:\InitiatorService.ServiceListing.xml file. The initiator service administrator sends this listing to the target service administrator. The target service administrator can import this listing:

 use SampleServiceDb;
go

declare @service_listing xml;
exec sp_load_service_listing N'c:\InitiatorService.ServiceListing.xml', @service_listing output;
exec sp_import_initiator_service_listing_at_target @service_listing, [SampleService], [InitiatorServiceOwner];
go

In addition, if this is the first service listing received from the SQL instance that hosts the initiator service, the Service Broker transport security also has to be imported:

 
---- replace <initiator-service-computer-name> with the actual name of the machine hosting the initiator service--
use InitiatorServiceDb;
go

declare @service_listing xml;
exec sp_load_service_listing N'c:\InitiatorService.ServiceListing.xml', @service_listing output;
exec master..sp_import_service_listing_grant_connect_on_endpoint @service_listing, '....', [<initiator-service-computer-name>];
go

The two systems are now ready to exchange messages.

Service Broker Service Listing procedures reference

sp_secure_service

 sp_secure_service (
 @service_name as SYSNAME)

This utility stored procedure creates a certificate for the service owner. Should be run by both the initiator and target service administrators. It must be run BEFORE exporting the service listing.
Parameters:

@service_name: the name of the service to be secured

Created by: ServiceListing.Database.sql

sp_export_service_listing

 sp_export_service_listing (
 @service_name as SYSNAME,
   @service_listing as XML OUTPUT)

Will export all the necessary info, including the required certificates, the routing info and the supported contracts/message types for the exported service. A Service Broker endpoint must be configured in order to export the needed routing info.
Parameters:

@service_name: the name of the service to be exported

@service_listing: output, the service listing XML document

Created by: ServiceListing.Database.sql

sp_import_target_service_listing_at_initiator

 sp_import_target_service_listing_at_initiator (
 @service_listing as XML,
    [@proxy_user_name as SYSNAME],
  [@request_anonymous as BIT]),

Imports, at the initiator site, a service listing created for a target service. Should be used by the initiator service administrator. It will create the routing info needed to send messages to the target service. It will import the security info needed for targeting the service (the RSB). It will import the contracts and message types used by the target service.
Parameters:

@service_listing: the target service listing

@proxy_user_name: local name to be used for the target service owner proxy user

@requestanonymous: flag to request an anonymous configuration. It cannot be honored if the target service does not accept public access.

Created by: ServiceListing.Database.sql

sp_import_initiator_service_listing_at_target

 sp_import_initiator_service_listing_at_target (
 @service_listing,
   @targeted_service,
  [@proxy_user_name])

Imports, at the target site, a service listing created by an initiator. Should be used by the target service administrator. It will import the routing information needed to send back replies to the initiator and the security information needed to grant SEND permission to the initiator service owner. If the initiator service listing does not contain a certificate, no SEND persmission is granted. If anonymous security is desired, the target service administrator should grant SEND permissions on the targeted service to [Public].
Parameters:

@service_listing: the initator service listing

@targeted_service_name: the service targeted by the initiator. SEND permission will be granted on this service to the proxy user representing the initiator service owner.

@proxy_user_name: name to be used for the proxy user representing the initiator service owner.

Created by: ServiceListing.Database.sql

sp_save_service_listing

 sp_save_service_listing sp_save_service_listing (
   @service_listing as xml,
    @filename as nvarchar(256)

Saves a service listing to a file. Size of service listing is limited to 8000 bytes Use sp_export_service_listing to create the service listing The file is overwritten if already exists.
Parameters:

@service_listing: the service listing

@filename: the fully qualified name of the file

Created by: ServiceListing.Database.sql

sp_load_service_listing

 sp_load_service_listing (
   @filename as nvarchar(256),
 @service_listing as xml output)

Loads a service listing to a file. Size of service listing is limited to 8000 bytes.
Parameters:

@filename: the fully qualified name of the file

@service_listing: output, the service listing

Created by: ServiceListing.Database.sql

sp_import_service_listing_grant_connect_on_endpoint

 sp_import_service_listing_grant_connect_on_endpoint(
    @service_listing as xml,
    @loginpassword nvarchar(max),
   @proxyinstanceuser sysname = NULL,
  @dropexisting as bit = 1)

Imports a proxy user for a service host machine and grants CONNECT permission on the broker endpoint. Both initiator service administrator and target service administrator need to do this step. The initator service listing needs to be imported by the target and vice-versa.This step needs to be done only once for each pair of machines. A proxy login will be created to represent the peer service host machine. This login will be granted CONNECT permission on the broker endpoint. Only CERTIFICATE authentication option is supported by this procedure.
Parameters:

@service_listing: the service listing

@loginpassword: a password is required for the proxy login

@proxyinstanceuser: the local name for the proxy user

@dropexisting: flag to control the dropping of already existing proxy login, user and certificate

Created by: ServiceListing.Master.sql

You can download the scripts from SSB CodeGallery.

Share via

Exporting and importing service listings into a database

Example of using the Service Broker Service Listing procedures

Service Broker Service Listing procedures reference

sp_secure_service

sp_export_service_listing

sp_import_target_service_listing_at_initiator

sp_import_initiator_service_listing_at_target

sp_save_service_listing

sp_load_service_listing

sp_import_service_listing_grant_connect_on_endpoint

Additional resources