In the recent installments of our series dedicated to the most relevant
features of SQL Server 2005 Express Edition, we have described the steps
necessary to configure Service Broker full dialog level security. As we have
explained, this not only resulted in encryption of all messages that were part
of the same conversation with a unique set of symmetric keys, but also made it possible
to tighten security of individual database objects. Since both initiator and
target were capable of identifying each other, we could restrict access to them
to specific principals. However, such configuration required a mechanism
facilitating the exchange of certificates between communication partners, which
might not be logistically feasible in some situations. In this article, we will
present an alternative approach, which eliminates this requirement by relying
on anonymous dialog security.
As with anonymous transport level security (which we have presented
earlier), there are situations where the logistics of managing certificate
exchange are too complex to consider their implementation (for example, due to
a large number of initiators or lack of administrative authority over them). In
such cases, you have an option of employing anonymous dialog security. While
the resulting compromise precludes the ability to prevent receiving messages
from unauthorized senders, it continues to protect all conversations with
distinct encryption keys and ensures that messages originating from initiators
can be decrypted only by their intended targets.
As before, for the sake of brevity, we will assume that all rudimentary
Service Broker components, including a message type, contract, queues,
associated services, and their respective routes are defined on both initiator
and target systems (for specifics regarding their creation, refer to our
earlier article). In addition, in order to implement anonymous dialog
security, we need to have transport level security in place. Since the most
logical option in this context is its anonymous type, such setup involves (as
we have recently described) generating a master key in the master database on
SQL Server 2005 instances hosting initiator and target services (using
CREATE MASTER KEY statement), followed
by the creation of certificates which are subsequently used to provide
authentication mechanism for connections reaching Service Broker endpoints. Due
to the anonymous nature of incoming traffic, you also have to grant
CONNECT permissions for each endpoint to
the Public role on both systems.
Since anonymous dialog security involves encryption, both initiator and
target databases need to have their master keys present. This is accomplished
by executing the already familiar
MASTER KEY statement. This time around, however, you create a
certificate only in the target database (in addition, without a corresponding
database user designated as its owner). The certificate (along with its public
key) is backed up to a file, which needs to be somehow transferred to the
initiator, providing the unilateral identification of the target we mentioned above.
On the other hand, since there is no equivalent mechanism that would offer the
same functionality to the target, access to its service needs to be open to the
-- on srvEnt01
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 't0UGh2gu3$$'
CREATE CERTIFICATE cert_dbSBEnt01
WITH SUBJECT = 'dbSBEnt01 Certificate'
BACKUP CERTIFICATE cert_dbSBEnt01
TO FILE = 'C:\Temp\cert_dbSBEnt01.cer'
GRANT SEND ON SERVICE::[//databaseJournal.com/SQL2005EX/ServiceBroker/svcRecv]
Once the certificate file is copied to an arbitrary location on the target
system, it has to be imported into the database containing local Service Broker
objects (note that this requires the presence of a master key). The certificate
needs to be associated with a database user (we create, for this purpose, a
designated account not linked to a SQL Server login) via
AUTHORIZATION mechanism. The same user
is also referenced when defining
SERVICE BINDING pointing to the target service and including
ANONYMOUS = ON clause.
-- on srvExp01
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '1@mS0$ecReT'
CREATE USER udbSBEnt01 WITHOUT LOGIN
CREATE CERTIFICATE cert_dbSBEnt01
FROM FILE = 'C:\Temp\cert_dbSBEnt01.cer'
CREATE REMOTE SERVICE BINDING rsbSBEnt01
TO SERVICE '//databaseJournal.com/SQL2005EX/ServiceBroker/svcRecv'
WITH USER = udbSBEnt01,
ANONYMOUS = ON
To launch an encrypted dialog at this point, you simply need to execute the
BEGIN DIALOG CONVERSATION statement.
With the remote service binding already in place, the value of
WITH ENCRYPTION parameter is no longer
relevant (although for clarity sake, you might want to include it and set it to
ON). To make our example
more realistic, you might want to attempt running the conversation in the
security context of the newly created user account. This can be easily done by running
EXECUTE AS USER statement
when logged on as the
(to return from the context switch, simply execute
REVERT). Keep in mind, however, that in
order for this approach to succeed,
needs to be granted
permissions on the initiator service queue.
-- on srvExp01
GRANT RECEIVE ON qSend
In the next article of our series, we will look at the Service Broker
activation mechanism, which can be used to automate processing of messages
arriving at a target service queue.
See All Articles by Columnist Marcin Policht