Establishing Distributed SQL Server Express’ Service Broker Conversations Using Certificate-based Authentication

In the
previous installment of our series covering features available in SQL
Server 2005 Express Edition, we started discussing the use of certificates in
authenticating Service Broker conversations in a distributed environment, where
communication partners reside on separate hosts. As we have pointed out, some
sort of authentication mechanism is needed in such scenarios in order to
satisfy transport-layer security requirements. The certificate-based method
offers more flexibility in this area, since Windows-based Kerberos protocol
(which implementation we have described earlier) is restricted to cases in
which both initiator and target belong to the same or trusted Active Directory
domains. However, regardless of the approach, it is important to note that such
provisions can be applied independently of transport or dialog level encryption
(which will be presented in upcoming articles of this series).

In our example, we employed self-signed X.509 certificates (and
corresponding pubic and private key pairs), which were generated by leveraging
functionality built into the SQL Server 2005 database engine (keep in mind that
for this to work as expected, Database Master Key must exist, since Service
Broker does not support encrypting certificates with passwords). Each of the
certificates was subsequently assigned to the local Service Broker endpoint for
the purpose of authentication (using AUTHENTICATION
clause of the CREATE ENDPOINT
statement). Furthermore, we also had to grant SEND permissions to Public fixed role in both initiator
and target databases, since their respective services can not be uniquely
identified when authentication relies on certificates associated with endpoints
(restricting this privilege to individual services will become possible once we
introduce certificate-based dialog-level encryption).

In order for the authentication to succeed, each of the communication
participants must have a public key corresponding to the private key of its
partner (the one bound to the local Service Broker endpoint). To accomplish
this, the files (which we created earlier by running the BACKUP CERTIFICATE statement) containing
certificates (along with their public keys) need to be exchanged between a
target and initiator (the method of transfer is not relevant in the context of
our discussion). Once that is completed, the certificates should be imported
into the local master database (with the CREATE
CERTIFICATE statement) and associated with a SQL Server principal
that will provide security context for incoming Service Broker endpoint
connections. (For this purpose, we will use a pair of login/user accounts on
the SQL Server 2005 Express and SQL Server 2005 Enterprise instance, called lSB01Ent/uSB01Ent and lSB01Ex/uSB01Ex, respectively). Such association
is formed by assigning certificate ownership to the user with the AUTHORIZATION clause followed by
granting CONNECT privilege
on the newly defined endpoint to the corresponding login (with GRANT CONNECT ON ENDPOINT statement).

— on srvExp01

USE master

CREATE LOGIN lSB01Ent

 WITH PASSWORD = ‘I@m$0C0mp1eX’

CREATE USER uSB01Ent

GO

CREATE CERTIFICATE cert_SB_02_Enterprise

 AUTHORIZATION uSB01Ent

 FROM FILE = ‘C:Tempcert_SB_02_Enterprise.cer’

GO

GRANT CONNECT ON ENDPOINT::EP_SB_01_Express

TO lSB01Ent

GO

— on srvEnt01

USE master

GO

CREATE LOGIN lSB01Ex

 WITH PASSWORD = ‘N01Wi11Gue$$Me’

CREATE USER uSB01Ex

GO

CREATE CERTIFICATE cert_SB_01_Express

 AUTHORIZATION uSB01Ex

 FROM FILE = ‘C:Tempcert_SB_01_Express.cer’

GO

GRANT CONNECT ON ENDPOINT::EP_SB_02_Enterprise

 TO SB01Ex

GO

In order to establish a conversation between two services, we also need to
define routes identifying their network location. As before, this is done by
executing CREATE ROUTE
statement.

— on srvExp01

USE dbSBExp01

GO

CREATE ROUTE rtsvcRecv WITH

 SERVICE_NAME = ‘//databaseJournal.com/SQL2005EX/ServiceBroker/svcRecv’,

 ADDRESS = ‘TCP://srvEnt01.DatabaseJournal.com:4022’

— on srvEnt01

USE dbSBEnt01

GO

CREATE ROUTE rtsvcSend WITH

 SERVICE_NAME = ‘//databaseJournal.com/SQL2005EX/ServiceBroker/svcSend’,

 ADDRESS = ‘TCP://srvExp01.DatabaseJournal.com:4022’

At this point, you are ready to start a dialog. As part of this process, the
initiator will contact the target endpoint (utilizing the newly defined route)
and authenticate leveraging the mutually recognized certificate via Transport
Layer Security-based channel (in the manner similar to any HTTP SSL
communication, omnipresent in secure Web client/server interactions). To test
an actual conversation, you can reuse the set of statements we provided when
demonstrating transport layer Kerberos authentication in one of the recent
articles of this series.

Note that it is possible to specify both Windows (with the ability to
restrict it specifically to Kerberos or NTLM, and an option to use either one
by assigning NEGOTIATE value
to the AUTHENTICATION
clause) as well as certificate-based authentication when configuring Service
Broker endpoint properties. For example, the following definition will accept
any type of valid authentication request, by attempting each of the
authentication methods in the order in which they are listed:

CREATE ENDPOINT EP_SB_01_Express

 STATE = STARTED

 AS TCP (LISTENER_PORT = 4022)

 FOR SERVICE_BROKER (

  AUTHENTICATION = CERTIFICATE certPriv_SB_01_Express WINDOWS NEGOTIATE,

  ENCRYPTION = DISABLED);

In the next article of our series, we will start an overview of transport
and dialog-level encryption, which rely on digital cryptography to ensure
integrity and confidentiality of Service Broker communication.

»


See All Articles by Columnist Marcin Policht