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

January 9, 2009

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:\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.

» See All Articles by Columnist Marcin Policht