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