In the recent articles of our series dedicated to the most prominent features incorporated into SQL Server 2005 Express Edition, we have been discussing Service Broker functionality. So far we have presented a couple of scenarios demonstrating implementation of a sample dialog between two services residing in the same database and an equivalent arrangement taking place in a distributed environment. As we have pointed out, while the former could be conducted without any security-related provisions, the latter, at the minimum, required the presence of an authentication mechanism (which facilitates Service Broker transport security). For the sake of simplicity, we decided to use Windows-based Kerberos protocol (as determined by the value of
AUTHENTICATION option assigned to both endpoints) for this purpose, which, while fairly straightforward to set up, limits the scope of systems participating in a Service Broker dialog to those residing in the same or trusted Active Directory domains. However, it is possible to eliminate this limitation by employing certificates. We will provide an overview of such an approach in this article (note that the functionality described here does not depend on using dialog-level encryption).
Certificate-based authentication is based on the principle of asymmetric cryptography, which involves the concept of public and private digital key pairs as well as the certificates associated with them (representing the relationship between the matching keys and identifying their owner). Access to the private key is secured and available strictly to its owner, while the public, as its name indicates, might be safely exposed (via its certificate) without jeopardizing confidentiality of its counterpart. At the same rate, both of them are related in a unique manner, such that any piece of data encrypted with the former can be reconstructed using the latter. This confirms the authenticity of its origin with a high degree of certainty. The reverse operation is also possible, thus providing encryption capabilities, which we will be covering in future articles). In the context of Service Broker communication, it is possible to leverage this behavior to authenticate two endpoints. This is accomplished by configuring each of them with a locally stored private key and having the matching public key deployed (in the form of its certificate) to the communication partner. In addition, on each server, you need to designate a login (and a corresponding master database user) in which security context all conversations will be conducted, authorized to access a certificate representing the remote side and granted the privileges required to connect to the local endpoint. Let’s take a look at a sample implementation of this scenario.
We will leverage previously created SQL Server 2005 objects (for the relevant T-SQL statements, refer to one of the earlier articles of our series), including two databases (
dbSBEnt01), residing on two separate computers (
srvEnt01, hosting, respectively, SQL Server 2005 Express and Enterprise Editions). Note that, unlike before, these systems do not have to belong to a mutually trusted Active Directory environment. Both of them will share the same message type (
//databaseJournal.com/SQL2005EX/ServiceBroker/msgNV) and contract (
//databaseJournal.com/SQL2005EX/ServiceBroker/contAnymsgNV) definitions, with the first one hosting
qSend queue with its
//databaseJournal.com/SQL2005EX/ServiceBroker/svcSend service and the second providing storage to
qRecv queue and
//databaseJournal.com/SQL2005EX/ServiceBroker/svcRecv service associated with it. (Make sure that the names of all objects included in messages, such as their type names, contracts, and each of the services, match exactly – including character case – between the initiator and target).
While using third party certificates is possible, for the purpose of our example, we will take advantage of equivalent functionality incorporated into SQL Server 2005 platform (especially considering that validation process employed by the database engine does not track their origin, but only verifies that their expiration date has not yet passed). This involves creating self-signed X.509 certificates (by invoking
CREATE CERTIFICATE T-SQL statement). The ability to successfully complete this step relies on the presence of a database master key, which is needed to encrypt the private key (even though it is possible to produce a similar result by using arbitrarily an chosen password, such approach is not applicable to Service Broker endpoint authentication). We will generate our database master key in the master database (where the key pair and the corresponding certificate associated with our endpoint will reside) on both servers (while logged on with an account that is a member of its sysadmin role) and secure each with a password (protecting its content via Triple DES algorithm). Once that is accomplished, we will create a key pair as well as a self-signed certificate. If you do not assign a value to its
EXPIRY_DATE parameter, it will be automatically set to one year from either the current date or the
START_DATE – if you have specified it as part of the
CREATE CERTIFICATE statement and export the latter to a file. You will need to point to it when executing the
CREATE ENDPOINT T-SQL statement (note that, as we have mentioned before, we will keep the
ENCRYPTION option disabled for the time being):
-- on srvExp01 USE master GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = '1m$0C0mPleX' GO CREATE CERTIFICATE cert_SB_01_Express WITH SUBJECT = 'SB_01_Express Certificate' GO BACKUP CERTIFICATE cert_SB_01_Express TO FILE = 'C:Tempcert_SB_01_Express.cer' GO CREATE ENDPOINT EP_SB_01_Express STATE = STARTED AS TCP (LISTENER_PORT = 4022) FOR SERVICE_BROKER ( AUTHENTICATION = CERTIFICATE cert_SB_01_Express, ENCRYPTION = DISABLED); -- on srvEnt01 USE master GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = '[email protected]$$' GO CREATE CERTIFICATE cert_SB_02_Enterprise WITH SUBJECT = 'SB_02_Enterpise Certificate' GO BACKUP CERTIFICATE cert_SB_02_Enterprise TO FILE = 'C:Tempcert_SB_02_Enterprise.cer' GO CREATE ENDPOINT EP_SB_02_Enterprise STATE = STARTED AS TCP (LISTENER_PORT = 4022) FOR SERVICE_BROKER ( AUTHENTICATION = CERTIFICATE cert_SB_02_Enterprise, ENCRYPTION = DISABLED);
Since the authentication takes place on the transport level (which might involve multiple dialogs between services sharing the same endpoints), initiator and targets cannot be uniquely identified by relying solely on its provisions. As the result, you will need to grant SEND permissions on both services to the Public role in both
-- on srvExp01 USE dbSBExp01 GO GRANT SEND ON SERVICE::[//databaseJournal.com/SQL2005EX/ServiceBroker/svcSend] TO Public -- on srvEnt01 USE dbSBEnt01 GO GRANT SEND ON SERVICE::[//databaseJournal.com/SQL2005EX/ServiceBroker/svcRecv] TO Public
In the next article of our series, we will present the remaining steps necessary to establish Service Broker dialog in the distributed environment while leveraging certificate-based authentication.