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