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

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:Tempcert_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:Tempcert_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:Tempcert_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:Tempcert_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