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

December 8, 2008

In the previous installment of our series dedicated to the most prominent features of SQL Server 2005 Express Edition, we discussed the role of transport-level encryption in securing communication between distributed Service Broker endpoints. As we mentioned at that point, a considerably more flexible mechanism not only provides similar functionality on the dialog level, but also facilitates authorization of individual services. Presentation of its principles will be the main subject of this article.

Unlike its transport counterpart, which protects all Service Broker-related traffic flowing between designated endpoints (constituting entry points for any number of distinct services collocated on the same SQL Server 2005 instance), dialog security is associated directly with a specific initiator and target pair, providing the means for their identification (which, in turn, is the primary reason for its granular authorization capabilities). As you might recall, lack of this functionality in earlier examples forced us to loosen up SEND permissions on both services by granting them to the database Public role. With dialog-level security in place, this is no longer necessary. However, you still retain the flexibility to decide whether both communication partners will be expected to identify each other, which in turn determines whether full or anonymous dialog security is in effect. We will start by providing a description of the first of these two arrangements (including an example illustrating its implementation) leaving the discussion of the second one until later.

Full dialog security relies on the same concept of hybrid cryptography that we described when discussing transport-level protection. Asymmetric keys and their respective certificates serve as identifiers of initiator and target services participating in a Service Broker dialog (the primary distinction here is that keys are associated with services, rather than with endpoints). To accomplish this, the setup involves generating master keys in both databases hosting respective services and leveraging them to protect locally stored (manually created) private keys as well as (automatically generated) symmetric keys responsible for encrypting content of individual conversations (note that this is different from the configuration of transport-level encryption, which employed master database for this purpose). Ownership of these private keys need to be granted (via the AUTHORIZATION clause) to designated database users, who also hold authorization over the local service (on the target side, CONTROL permissions are verified before the conversation starts). Corresponding public keys (in the form of certificates) must be transferred between the databases. Next, each of certificates has to be linked to another arbitrarily chosen user (using the same authorization mechanism), serving as the proxy for its remote counterpart (by virtue of ownership of the corresponding private and public key of the same pair). A principal starting a conversation needs to have RECEIVE permissions on the initiator service queue. On the target side, the newly defined proxy user of the owner of the initiating service must be granted SEND permissions on the target service. On the initiator side, the proxy of the user with authorization to the target service has to be associated with it via remote service binding (defined using CREATE REMOTE SERVICE BINDING T-SQL statement).

Since the description we just provided might sound fairly confusing, we will guide you through a sample process of configuring dialog-level encryption using the same distributed setup we have been leveraging in the recent examples (for the relevant T-SQL statements, refer to our earlier article). To refresh your memory, our sample environment consists of two databases (dbSBExp01 and dbSBEnt01), residing on two separate computers (srvExp01 and srvEnt01, hosting, respectively, SQL Server 2005 Express and Enterprise Editions). Both of them share the same message type (//databaseJournal.com/SQL2005EX/ServiceBroker/msgNV) and contract (//databaseJournal.com/SQL2005EX/ServiceBroker/contAnymsgNV) definitions, with the first one hosting qSend queue with its //databaseJournal.com/SQL2005EX/ServiceBroker/svcSend service and the second providing storage to qRecv queue and associated //databaseJournal.com/SQL2005EX/ServiceBroker/svcRecv service. (Note that all object names included in messages, such as their types, contracts, and each of the services, need to match exactly - including character case - between the initiator and target). We will also assume that either Windows or certificate based authentication has already been implemented (as described previously), since this is one of the requirements of the dialog-level security. The SQL Server 2005 Express (srvExp01) and Enterprise (srvEnt01) will serve, respectively, as the Service Broker initiator and target.

To start, we will create master keys in databases hosting initiator and target services using the CREATE MASTER KEY statement. (To verify its outcome, you can check the value of is_master_key_encrypted_by_server column in the appropriate rows of the sys.databases catalog view on each SQL Server 2005 instance). This step is required in order to protect a private keys involved in initiating encrypted dialogs as well as a symmetric session key used to encrypt the payload of Service Broker messages exchanged during its progress:

-- on srvEnt01
USE dbSBEnt01
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 't0UGh2gu3$$'
GO
-- on srvExp01
USE dbSBExp01
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '1@mS0$ecReT'
GO

In the next step, we create a local user in each database that will provide an association (through authorization) between the symmetric key pair (with its respective certificate) and the local service. This is necessary since certificates do not constitute security principals and cannot be directly tied to services. Users, with their ability to hold certificate authorization and service-level association (assigned via either AUTHORIZATION or REMOTE SERVICE BINDING clauses) function as the missing link. Note that during user creation, we leverage a new database user type (introduced in the SQL Server 2005 platform, including its Express Edition), which does not have a corresponding login. This approach not only offers security benefits, but also improves database portability. As mentioned earlier, you might also need to grant RECEIVE permissions on the initiator service queue to a principal that will start a conversation (this statement can be omitted if you step through our example in the security context of a privileged account).

-- on srvEnt01
USE dbSBEnt01
GO
CREATE USER udbSBEnt01 WITHOUT LOGIN
GO
ALTER AUTHORIZATION ON SERVICE::[//databaseJournal.com/SQL2005EX/ServiceBroker/svcRecv]
 TO udbSBEnt01
GO
-- on srvExp01
USE dbSBExp01
GO
CREATE USER udbSBExp01 WITHOUT LOGIN
GO
ALTER AUTHORIZATION ON SERVICE::[//databaseJournal.com/SQL2005EX/ServiceBroker/svcSend]
 TO udbSBExp01
GO
GRANT RECEIVE ON qSend
 TO udbSBExp01
GO

In the next article of our series we will present the remaining steps necessary to establish a distributed Service Broker conversation protected with dialog-level security between two instances of SQL Server 2005.

» See All Articles by Columnist Marcin Policht