Conducting Service Broker Conversation Using Full Dialog Security in SQL Server 2005 Express Service

December 31, 2008

In the previous installment of our series covering the most relevant features of SQL Server 2005 Express Edition, we started an overview of Service Broker full dialog-level security. As we have pointed out, its primary advantages include the ability to encrypt individual conversations as well as to identify and authorize their participants. In order to make our presentation more palpable, we also decided to demonstrate a sample implementation of such configuration, starting with a couple of preliminary steps including the generation of master keys in databases hosting the initiator and target as well as creation of a designated user representing each of these services through the AUTHORIZATION clause. In this article, we will conclude this subject by describing the remaining prerequisites and a method to invoke a secure dialog.

Each of the newly created users associated with local Service Broker services and their queues also need to be linked to asymmetric key pairs that will identify both parties involved in a conversation and provide a mechanism to exchange symmetric keys used to encrypt messages between them. This association is formed by granting certificate ownership during their creation to both users. The public key of each pair is then backed up to an arbitrary file system location by running the BACKUP CERTIFICATE T-SQL statement:

-- on srvEnt01
USE dbSBEnt01
GO
CREATE CERTIFICATE cert_dbSBEnt01
 AUTHORIZATION udbSBEnt01
 WITH SUBJECT = 'dbSBEnt01 Certificate'
GO
BACKUP CERTIFICATE cert_dbSBEnt01
 TO FILE = 'C:\Temp\cert_dbSBEnt01.cer'
GO
-- on srvExp01
USE dbSBEnt01
GO
CREATE CERTIFICATE cert_dbSBExp01
 AUTHORIZATION udbSBExp01
 WITH SUBJECT = 'dbSBExp01 Certificate'
GO
BACKUP CERTIFICATE cert_dbSBExp01
 TO FILE = 'C:\Temp\cert_dbSBExp01.cer'
GO

After the public keys (in the form of certificate files) are exchanged between the Service Broker communication partners, you import them into their respective databases (those containing initiator and target Service Broker services). Once again, in order to provide a security context for them, you need to designate accounts that will become their owners (via authorization mechanism). We will create a new user for this purpose (as before, defined without a corresponding SQL Server login) in each database and named identically to the owner of the corresponding private key (this is not required, but helps keep track of all principals involved in Service Broker conversations). Note that the resulting mutual ability of each service to identify its communication partner constitutes the primary difference between the full and anonymous dialog security. Furthermore, this unique functionality facilitates authorization, allowing you to assign specific credentials (instead of resorting to Public role, as we did previously) that will be used to carry out all tasks involved in message exchange. In particular, REMOTE SERVICE BINDING defined on the initiator references (via WITH USER clause) local owner of the public key, whose corresponding private key is in possession of the target database user with authorization to the target service (appearing in the TO SERVICE clause). At the same time, SEND permission on the target service needs to be granted to its database user (udbSBExp01) associated with the public key, whose corresponding private key is owned by the user in the initiator database (also named udbSBExp01) linked to the initiator service via AUTHORIZATION clause.

-- on srvEnt01
USE dbSBEnt01
GO
CREATE USER udbSBExp01 WITHOUT LOGIN
GO
CREATE CERTIFICATE cert_dbSBExp01
 AUTHORIZATION udbSBExp01
 FROM FILE = 'C:\Temp\cert_dbSBExp01.cer'
GO
GRANT SEND ON SERVICE::[//databaseJournal.com/SQL2005EX/ServiceBroker/svcRecv]
 TO udbSBExp01
GO
-- on srvExp01
USE dbSBExp01
GO
CREATE USER udbSBEnt01 WITHOUT LOGIN
GO
CREATE CERTIFICATE cert_dbSBEnt01
 AUTHORIZATION udbSBEnt01
 FROM FILE = 'C:\Temp\cert_dbSBEnt01.cer'
GO
CREATE REMOTE SERVICE BINDING rsbSBEnt01
 TO SERVICE '//databaseJournal.com/SQL2005EX/ServiceBroker/svcRecv'
 WITH USER = udbSBEnt01
GO

By default, all Service Broker conversations operate with dialog security enabled. However, as you might recall, we explicitly disabled this feature by assigning an OFF value to the ENCRYPTION parameter when invoking the BEGIN DIALOG CONVERSATION statement. By changing its value to ON (or simply removing this clause from the BEGIN DIALOG CONVERSATION statement), all messages sent to the other SQL Server 2005 instance in our distributed scenario will be encrypted (as before, you can verify this by examining captures of network traffic between the communication partners). You should also keep in mind that defining remote service binding will actually force encryption of a dialog regardless of the value of ENCRYPTION parameter.

-- on srvExp01
USE dbSBExp01
DECLARE @convHandle uniqueidentifier
BEGIN DIALOG CONVERSATION @convHandle
 FROM SERVICE [//databaseJournal.com/SQL2005EX/ServiceBroker/svcSend]
 TO SERVICE '//databaseJournal.com/SQL2005EX/ServiceBroker/svcRecv'
 ON CONTRACT [//databaseJournal.com/SQL2005EX/ServiceBroker/contAnymsgNV]
 WITH ENCRYPTION = ON
GO

In the next article of our series, we will examine the concept and implementation details of anonymous dialog-level security in a distributed Service Broker environment, including SQL Server 2005 Express Edition.

» See All Articles by Columnist Marcin Policht