SQL Server 2005 Express Edition - Part 30 - Distributed Service Broker Environment - Endpoints
September 8, 2008
In the latest articles of our series dedicated to SQL Server 2005 Express Edition, we have been discussing Service Broker, providing an overview of its basic concepts and presenting characteristics of its dialog protocol, which facilitates session-level features responsible for asynchronous, secure, and reliable message exchange. For the sake of clarity, we have postponed coverage of more advanced topics, focusing instead on an example with all Service Broker objects residing in the same database, which allowed us to temporarily disregard any security-related concerns. By operating within the boundaries of a single SQL Server Express 2005 installation, we have also benefited from simplified message flow (obviating the need for any routing provisions) with straightforward message delivery path. However, in order to bring us closer to a typical deployment scenario, as well as to realize the full potential of Service Broker capabilities, we need to review a distributed implementation, with initiator and target services residing on two separate computers. In this installment, we will describe the initial setup of components required to demonstrate such an arrangement. As before, we will make several simplifying assumptions, leaving more in-depth analysis (including such topics as encryption or certificate-based authentication) for later.
When designing a Service Broker environment spanning a network, it becomes necessary to take into consideration two additional factors - the role of a transport mechanism used to handle lower-level tasks required to carry communication between services as well as the extra security needed to protect its payload. The first one is the primary responsibility of Adjacent Broker Protocol, which utilizes TCP/IP-based traffic and custom-defined routes (forming mappings between IP addresses and Service Broker services) to efficiently transfer multiple messages (potentially from different conversations) between SQL Server 2005 objects called endpoints. So far, we have not dealt with the second, since authentication and encryption were not relevant in the context of our earlier discussions, but in distributed scenarios, their functionality becomes essential. For the time being, we will choose the simplest approach that will allow us to proceed with a sample implementation, by leveraging Windows (rather than certificate-based) authentication, without applying dialog or transport-level encryption. Note that this approach requires that computers hosting both SQL Server 2005 installations are members of the same Active Directory domain (or two joined by a trust relationship).
However, even with these simplifications in place, there are several caveats
that you need to be aware of when setting up a Service Broker conversation. In particular,
in absence of dialog-level security, operations performed on behalf of a remote
service in a local database run in the security context of Public fixed
database role. In effect, this role should be granted
In addition, you need to make sure that TCP/IP protocol is enabled (especially in the case of SQL Server Express Edition, where this is not the default setting) via Protocols for SQLEXPRESS (assuming the typical instance name) subnode of the SQL Server 2005 Network Configuration node in the SQL Server Configuration Manager. Remember to enable the Remote Connections feature (by choosing Local and the remote connection option and making sure that either "Using TCP/IP only" or "Using both TCP/IP and named pipes" are selected) in the Surface Area Configuration for Services and Connections. Keep in mind that a designated TCP port (typically set to an integer greater than 1024), on which Service Broker is listening for incoming connections should not be used by any other application and be reachable from a computer hosting the remote service (review firewall restrictions and create appropriate exceptions if necessary). Finally, do not forget to define routes, which associate each remote service with its network destination (in the form of the IP address and TCP port combination).
In our sample implementation, we will leverage our previously presented
Service Broker example, but adjust it to our current requirements by setting up
two databases (
Ensure that the names of all objects included in messages (such as their type names, contracts, and each of the services) are the same on the initiator and target (including case, since they are subjects to the binary comparison). Next, create two endpoints (one per instance) while logged on as a member of sysadmin fixed server role, using the following code (in both cases, we explicitly set the listening port to its default 4022):
-- on srvExp01 CREATE ENDPOINT epSBExp01 STATE = STARTED AS TCP (LISTENER_PORT = 4022) FOR SERVICE_BROKER ( AUTHENTICATION = WINDOWS KERBEROS, ENCRYPTION = DISABLED); -- on srvEnt01 CREATE ENDPOINT epSBEnt01 STATE = STARTED AS TCP (LISTENER_PORT = 4022) FOR SERVICE_BROKER ( AUTHENTICATION = WINDOWS KERBEROS, ENCRYPTION = DISABLED);
In order for Kerberos authentication to work as intended, each system participating
in future conversations should have the
SetSPN -A MSSQLSvc/srvExp01.DatabaseJournal.com:4022 srvExp01 SetSPN -A MSSQLSvc/srvEnt01.DatabaseJournal.com:4022 srvEnt01
Next, create logons representing
-- on srvExp01 USE master GO CREATE LOGIN [DatabaseJournal\srvEnt01$] FROM Windows; GRANT CONNECT ON ENDPOINT::epSBExp01 TO [DatabaseJournal\srvEnt01$]; -- on srvEnt01 USE master GO CREATE LOGIN [DatabaseJournal\srvExp01$] FROM Windows; GRANT CONNECT ON ENDPOINT::epSBEnt01 TO [DatabaseJournal\srvExp01$];
In the next article of our series, we will continue our discussion, presenting the remaining steps necessary to establish Service Broker communication in a distributed environment, focusing on setting up routes and assigning appropriate dialog-level permissions.