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 SEND permissions on the local service (the same applies to the RECEIVE statement, as long as its execution is triggered by a remote service). Another consideration is Windows authentication, which can utilize either NTLM or Kerberos protocol (you can specify either one as part of endpoint definition). While NTLM tends to be easier to configure and troubleshoot (in particular, in situations where both database engine services run in the security context of designated domain user accounts), Kerberos gives you superior flexibility and security, especially when dealing with SQL Server 2005 Express Edition (which, by default, logs on as NT AUTHORITY/Network Service principal). However, in order for it to work properly, you have to ensure that both computers (hosting initiator and target services) have the entry in the form MSSQLSvc/Fully_Qualified_Domain_Name:Service_Broker_Endpoint_Port added to the multi-valued servicePrincipalName attribute of their Active Directory accounts. This can be accomplished with tools such as ADSIEDIT.msc or LDP.exe, which offer direct access to Active Directory content, or with a freely downloadable SetSPN Windows Resource Kit utility, (we will shortly provide an example demonstrating its syntax). Furthermore, these accounts must be recognized as valid SQL Server logins and need to have CONNECT permissions on each other's Service Broker endpoints. It is also important to realize that two SQL Server 2005 Express Edition instances are not capable of direct message exchange (at least one of communication partners must be running a full-fledged edition, such as Standard or Enterprise).

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 (dbSBExp01 and dbSBEnt01), residing on two separate computers (srvExp01 and srvEnt01, hosting, respectively, SQL Server 2005 Express and Enterprise Editions) which are members of the same Active Directory domain (DatabaseJournal.com). We will assume the following:

• that both share the same message type (//databaseJournal.com/SQL2005EX/ServiceBroker/msgNV)
• and contract (//databaseJournal.com/SQL2005EX/ServiceBroker/contAnymsgNV) definitions,
• with the first one hosting qSend queue with its //databaseJournal.com/SQL2005EX/ServiceBroker/svcSend service
• and the second providing storage to qRecv queue and associated //databaseJournal.com/SQL2005EX/ServiceBroker/svcRecv service (for T-SQL statements required to create each of these objects, refer to the earlier article of this series).

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);

The ENCRYPTION parameter can take one of three values: DISABLED, SUPPORTED, and REQUIRED. The actual outcome depends on a combination of settings on two endpoints, between which the Service Broker dialog is established. Having either SUPPORTED or REQUIRED on both will enforce encryption, while the presence of DISABLED on at least one side will result in unencrypted traffic (although pairing it up with a conflicting REQUIRED, will trigger an error condition). As mentioned earlier, in our example, two computers hosting SQL Server 2005 Express and Enterprise instances reside in the same Active Directory domain, so we will take advantage of Windows-based Kerberos authentication. You can verify the outcome of these T-SQL statements (as well as confirm the status of the newly created endpoints) by viewing the Service Broker entry under the Database Engine node of your local instance displayed in the Surface Area Configuration for Features console (accessible from the Microsoft SQL Server 2005 program group menu).

In order for Kerberos authentication to work as intended, each system participating in future conversations should have the servicePrincipalName attribute of its Active Directory computer account populated with an entry representing the local Service Broker endpoint. You can verify whether this is the case by running the earlier mentioned SetSPN.exe Resource Kit utility with the -L switch and add one, if needed, by utilizing -A switch, as in the following example:

SetSPN -A MSSQLSvc/srvExp01.DatabaseJournal.com:4022 srvExp01
SetSPN -A MSSQLSvc/srvEnt01.DatabaseJournal.com:4022 srvEnt01

Next, create logons representing srvExp01 and srvEnt01 domain computer accounts (in the form DatabaseJournal\srvExp01$ and DatabaseJournal\srvEnt01$) as well as grant them CONNECT rights to relevant endpoints:

-- 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.

» See All Articles by Columnist Marcin Policht