AboutIn 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:\Temp\cert_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:\Temp\cert_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.