Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Dec 31, 2008

Conducting Service Broker Conversation Using Full Dialog Security in SQL Server 2005 Express Service

By Marcin Policht

In the previous installment of our series covering the most relevant features of SQL Server 2005 Express Edition, we started an overview of Service Broker full dialog-level security. As we have pointed out, its primary advantages include the ability to encrypt individual conversations as well as to identify and authorize their participants. In order to make our presentation more palpable, we also decided to demonstrate a sample implementation of such configuration, starting with a couple of preliminary steps including the generation of master keys in databases hosting the initiator and target as well as creation of a designated user representing each of these services through the AUTHORIZATION clause. In this article, we will conclude this subject by describing the remaining prerequisites and a method to invoke a secure dialog.

Each of the newly created users associated with local Service Broker services and their queues also need to be linked to asymmetric key pairs that will identify both parties involved in a conversation and provide a mechanism to exchange symmetric keys used to encrypt messages between them. This association is formed by granting certificate ownership during their creation to both users. The public key of each pair is then backed up to an arbitrary file system location by running the BACKUP CERTIFICATE T-SQL statement:

-- on srvEnt01
USE dbSBEnt01
GO
CREATE CERTIFICATE cert_dbSBEnt01
 AUTHORIZATION udbSBEnt01
 WITH SUBJECT = 'dbSBEnt01 Certificate'
GO
BACKUP CERTIFICATE cert_dbSBEnt01
 TO FILE = 'C:\Temp\cert_dbSBEnt01.cer'
GO
-- on srvExp01
USE dbSBEnt01
GO
CREATE CERTIFICATE cert_dbSBExp01
 AUTHORIZATION udbSBExp01
 WITH SUBJECT = 'dbSBExp01 Certificate'
GO
BACKUP CERTIFICATE cert_dbSBExp01
 TO FILE = 'C:\Temp\cert_dbSBExp01.cer'
GO

After the public keys (in the form of certificate files) are exchanged between the Service Broker communication partners, you import them into their respective databases (those containing initiator and target Service Broker services). Once again, in order to provide a security context for them, you need to designate accounts that will become their owners (via authorization mechanism). We will create a new user for this purpose (as before, defined without a corresponding SQL Server login) in each database and named identically to the owner of the corresponding private key (this is not required, but helps keep track of all principals involved in Service Broker conversations). Note that the resulting mutual ability of each service to identify its communication partner constitutes the primary difference between the full and anonymous dialog security. Furthermore, this unique functionality facilitates authorization, allowing you to assign specific credentials (instead of resorting to Public role, as we did previously) that will be used to carry out all tasks involved in message exchange. In particular, REMOTE SERVICE BINDING defined on the initiator references (via WITH USER clause) local owner of the public key, whose corresponding private key is in possession of the target database user with authorization to the target service (appearing in the TO SERVICE clause). At the same time, SEND permission on the target service needs to be granted to its database user (udbSBExp01) associated with the public key, whose corresponding private key is owned by the user in the initiator database (also named udbSBExp01) linked to the initiator service via AUTHORIZATION clause.

-- on srvEnt01
USE dbSBEnt01
GO
CREATE USER udbSBExp01 WITHOUT LOGIN
GO
CREATE CERTIFICATE cert_dbSBExp01
 AUTHORIZATION udbSBExp01
 FROM FILE = 'C:\Temp\cert_dbSBExp01.cer'
GO
GRANT SEND ON SERVICE::[//databaseJournal.com/SQL2005EX/ServiceBroker/svcRecv]
 TO udbSBExp01
GO
-- on srvExp01
USE dbSBExp01
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
GO

By default, all Service Broker conversations operate with dialog security enabled. However, as you might recall, we explicitly disabled this feature by assigning an OFF value to the ENCRYPTION parameter when invoking the BEGIN DIALOG CONVERSATION statement. By changing its value to ON (or simply removing this clause from the BEGIN DIALOG CONVERSATION statement), all messages sent to the other SQL Server 2005 instance in our distributed scenario will be encrypted (as before, you can verify this by examining captures of network traffic between the communication partners). You should also keep in mind that defining remote service binding will actually force encryption of a dialog regardless of the value of ENCRYPTION parameter.

-- on srvExp01
USE dbSBExp01
DECLARE @convHandle uniqueidentifier
BEGIN DIALOG CONVERSATION @convHandle
 FROM SERVICE [//databaseJournal.com/SQL2005EX/ServiceBroker/svcSend]
 TO SERVICE '//databaseJournal.com/SQL2005EX/ServiceBroker/svcRecv'
 ON CONTRACT [//databaseJournal.com/SQL2005EX/ServiceBroker/contAnymsgNV]
 WITH ENCRYPTION = ON
GO

In the next article of our series, we will examine the concept and implementation details of anonymous dialog-level security in a distributed Service Broker environment, including SQL Server 2005 Express Edition.

» See All Articles by Columnist Marcin Policht



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date