Configuring Certificate-based Authentication in SQL Server Express' Distributed Service Broker Environment

October 20, 2008

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 (dbSBExp01 and dbSBEnt01), residing on two separate computers (srvExp01 and 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:\Temp\cert_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 = 'H@rD2GuE$$'
GO
CREATE CERTIFICATE cert_SB_02_Enterprise
 WITH SUBJECT = 'SB_02_Enterpise Certificate'
GO 
BACKUP CERTIFICATE cert_SB_02_Enterprise
 TO FILE = 'C:\Temp\cert_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 srvExp01 and srvEnt01 databases:

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

» See All Articles by Columnist Marcin Policht