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

November 10, 2008

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:\Temp\cert_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:\Temp\cert_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








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers