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

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

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