Configuring Anonymous Dialog Security in SQL Server 2005 Express Service Broker Conversation

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 CREATE
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
Public role.


— on srvEnt01
USE SB_02
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘t0UGh2gu3$$’
GO
CREATE CERTIFICATE cert_dbSBEnt01
WITH SUBJECT = ‘dbSBEnt01 Certificate’
GO
BACKUP CERTIFICATE cert_dbSBEnt01
TO FILE = ‘C:Tempcert_dbSBEnt01.cer’
GO
GRANT SEND ON SERVICE::[//databaseJournal.com/SQL2005EX/ServiceBroker/svcRecv]
TO Public
GO

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 REMOTE
SERVICE BINDING
pointing to the target service and including ANONYMOUS = ON clause.


— on srvExp01
USE SB_01
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘1@mS0$ecReT’
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,
ANONYMOUS = ON
GO

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 dbo
(to return from the context switch, simply execute REVERT). Keep in mind, however, that in
order for this approach to succeed, udbSBEnt01
needs to be granted RECEIVE
permissions on the initiator service queue.


— on srvExp01
USE SB_01
GRANT RECEIVE ON qSend
TO udbSBEnt01
GO

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

Marcin Policht
Marcin Policht
Being a long time reader, I'm proud to join the crowd of technology gurus gathered here. I have a fair share of Microsoft exams behind me, a couple of acronyms after my name - MCSE, MCSD, and MCT, decent familiarity with majority of MS BackOffice products (SMS, SQL, Exchange, IIS), programming and scripting languages (VB, C++, VBScript with wsh) and several years of practical experience with Windows environment administration and engineering. My focus these days is on SQL Server and Windows 2000, and I'll attempt to share the most interesting experiences with these products.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles