SQL Server 2005 Express Edition – Part 30 – Distributed Service Broker Environment – Endpoints

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)
  • with the first one hosting qSend
    queue with its //databaseJournal.com/SQL2005EX/ServiceBroker/svcSend
  • 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
— on srvEnt01

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 DatabaseJournalsrvExp01$
and DatabaseJournalsrvEnt01$)
as well as grant them CONNECT
rights to relevant endpoints:

— on srvExp01
USE master
CREATE LOGIN [DatabaseJournalsrvEnt01$] FROM Windows;
GRANT CONNECT ON ENDPOINT::epSBExp01 TO [DatabaseJournalsrvEnt01$];
— on srvEnt01
USE master
CREATE LOGIN [DatabaseJournalsrvExp01$] FROM Windows;
GRANT CONNECT ON ENDPOINT::epSBEnt01 TO [DatabaseJournalsrvExp01$];

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

Marcin Policht
Marcin Policht
Being a long time reader, I'm proud to join the crowd of technology gurus gathered here. I have a fair share of Microsoft exams behind me, a couple of acronyms after my name - MCSE, MCSD, and MCT, decent familiarity with majority of MS BackOffice products (SMS, SQL, Exchange, IIS), programming and scripting languages (VB, C++, VBScript with wsh) and several years of practical experience with Windows environment administration and engineering. My focus these days is on SQL Server and Windows 2000, and I'll attempt to share the most interesting experiences with these products.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles