Setup Secure SQL Server Service Broker across databases in less than 10 seconds!

Article
07/25/2005

One of the most interesting new features in SQL Server 2005 is Service Broker. If you need more details on what it is and why you would want to use it, I will let the expert tell you. But, if you want to get it setup and configured on your machine now then look no further.

Below is a single T/SQL script, which you can cut and paste into a Query Window, that will have you sending messages from one database to the other in less time than it takes to say, “give me Secure-SQL-Server-Service-Broker now” (try and say that 5 times in quick succession).

It's free The reason why I think SQL Service Broker is going to be so interesting is because it enables cheap, easy, and secure deployment of hub-spoke applications. Why? Service Broker comes free with the SQL Server Express edition, and by using certificate based dialog- and transport-encryption, you can setup secure message based communications with many (potentially 1,000s, if you use the message forwarding capabilities to fan in/out) branch locations that report into a central hub.

By using the new public key/private key certificate based encryption and identity in SQL Server 2005, you don’t have to set up complicated cross branch/company/organization infrastructures (Active Directory, SSL, Kerberos, SQL Authentication etc.). NOTE: The hub does have to run a full version of SQL Server, e.g. Standard or Enterprise (The SQL Server Express Service Broker can only send through/to a full SQL Server edition). I have a feeling there are a lot of problems that can be easily solved with the platform we have built here, it will be interesting to see how people capitalize on this.

I couldn’t find a sample anywhere on the web of how to set up Service Broker with Certificates and CREATE USER WITHOUT LOGIN

IN. So, in this quick sample, we use CERTIFICATEs, AUTHORIZATION on objects, The Database MASTER KEY, USERs WITHOUT LOGINs, and QUEUEs. Because of this reliance on other features, setting up Service Broker is not quite as simple as doing your first CREATE TABLE. In fact, it is about as easy as the first time you tried to transitively close a four-way join! Still, once you have done it a few times, it seems to make sense.

Service Broker borrows from the world of Service Orientated Architecture (SOA), the concepts of Initiators and Targets (for the Client/Server among us, you might say the Initiator is the Client and the Target is the Server, but that would make the SOA folks cringe, so we won’t say it).

5 stages to glory The 5 Stages to cross-database Secure Service Broker glory using certificate based dialog security:

1. TARGET - Set up the target database, export a certificate (with public key)

2. INITIATOR - Set up the initiator database, export a certificate (with public key) and import the target certificate

3. TARGET - Set up the target database, import the certificate of the initiator user

4. INITIATOR - Create a stored proc to send messages, and send your first message

5. TARGET - Check the message got to the Target database

If you cut and paste all 5 stages into a single Query window (running against an edition other than SQL Server Express), and then Execute them in one go, you will have set-up, sent, and received your first secure SSB message across databases.

-- STAGE 1: SETUP TARGET DATABASE

USE master;

/* Create Target Database */

IF EXISTS (SELECT * FROM sys.databases WHERE name = 'Target') DROP DATABASE Target;

CREATE DATABASE Target

-- Activate the Broker in this database (can only be done with ALTER DATABASE)

ALTER DATABASE Target SET ENABLE_BROKER

USE Target;

-- Create the database master key

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<Specify a password here>'

/* Set up Target User, and place certificate (without private key) in a file */

-- Create Target User without login

IF EXISTS (SELECT * FROM sys.sysusers WHERE name = 'TargetUser') DROP USER TargetUser;

CREATE USER TargetUser WITHOUT LOGIN

-- Create a Cert for the Initiator user

IF EXISTS (SELECT * FROM sys.certificates WHERE name = 'TargetCert') DROP CERTIFICATE TargetCert;

CREATE CERTIFICATE TargetCert

AUTHORIZATION TargetUser

WITH SUBJECT = 'Target Certificate', EXPIRY_DATE = '10/31/2009';

-- Backup the cert up so the Target database can import the cert (public key)

BACKUP CERTIFICATE TargetCert TO FILE = 'c:\TargetCert.cer';

/* Set up SSB */

-- First, we need to create a message type. This message type is very simple and allows any type of content

-- (Drop contract because it binds to message)

IF EXISTS (SELECT * FROM sys.service_contracts WHERE name = 'SubmissionContract') DROP CONTRACT SubmissionContract;

IF EXISTS (SELECT * FROM sys.service_message_types WHERE name = 'Message') DROP MESSAGE TYPE Message;

CREATE MESSAGE TYPE Message VALIDATION = NONE

-- Now create a contract that specifies what type types of messages

CREATE CONTRACT SubmissionContract (Message SENT BY INITIATOR)

-- Set up Target receive queue to hold messages

-- (Need to drop service before queue, due to binding)

IF EXISTS (SELECT * FROM sys.services WHERE name = 'TargetService') DROP SERVICE TargetService;

IF EXISTS (SELECT * FROM sys.service_queues WHERE name = 'TargetQueue') DROP QUEUE TargetQueue;

CREATE QUEUE TargetQueue

-- Create the required service and bind to be above created queue

CREATE SERVICE TargetService

AUTHORIZATION TargetUser

ON QUEUE TargetQueue (SubmissionContract)

-- Create a

Local Route

for the destination TargetService

IF EXISTS (SELECT * FROM sys.routes WHERE name = 'InitiatorRoute')

DROP ROUTE

InitiatorRoute;

CREATE ROUTE InitiatorRoute WITH SERVICE_NAME = 'InitiatorService', ADDRESS = 'LOCAL'

-- STAGE 2: SETUP INITIATOR DATABASE

USE master;

/* Create Initiator Database */

IF EXISTS (SELECT * FROM sys.databases WHERE name = 'Initiator') DROP DATABASE Initiator;

CREATE DATABASE Initiator;

-- Activate the Broker in this database

ALTER DATABASE Initiator SET ENABLE_BROKER;

USE Initiator;

-- Create the database master key

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<Specify a password here>';

/* Set up Initiator User */

-- Create Initiator User without Login

IF EXISTS (SELECT * FROM sys.sysusers WHERE name = 'InitiatorUser01') DROP USER InitiatorUser01;

CREATE USER InitiatorUser01 WITHOUT LOGIN;

-- Create a Cert with InitiatorUser AUTHORIZATION

-- This links the Cert to the User so SSB uses the CERT for Dialog security

IF EXISTS (SELECT * FROM sys.certificates WHERE name = 'InitiatorCert01') DROP CERTIFICATE InitiatorCert01;

CREATE CERTIFICATE InitiatorCert01

AUTHORIZATION InitiatorUser01

WITH SUBJECT = 'Initiator Certificate 01', EXPIRY_DATE = '10/31/2009';

-- Backup the cert up so the Target can import the cert (public key)

BACKUP CERTIFICATE InitiatorCert01 TO FILE = 'c:\InitiatorCert01.cer';

/* Set up SSB */

-- First, we need to create a message type. Note that our message type is

-- very simple and allows any type of content

-- (Need to drop contract before message, due to binding)

IF EXISTS (SELECT * FROM sys.service_contracts WHERE name = 'SubmissionContract') DROP CONTRACT SubmissionContract;

IF EXISTS (SELECT * FROM sys.service_message_types WHERE name = 'Message') DROP MESSAGE TYPE Message;

CREATE MESSAGE TYPE Message VALIDATION = NONE;

-- Once the message type has been created, we need to create a contract

-- that specifies who can send what types of messages

CREATE CONTRACT SubmissionContract (Message SENT BY INITIATOR);

-- Set up Initiator send queue to hold messages

-- (Need to drop service before queue, due to binding)

IF EXISTS (SELECT * FROM sys.services WHERE name = 'InitiatorService') DROP SERVICE InitiatorService;

IF EXISTS (SELECT * FROM sys.service_queues WHERE name = 'InitiatorQueue') DROP QUEUE InitiatorQueue;

CREATE QUEUE InitiatorQueue;

-- Create the Initiator service and bind to be above created Initiator queue

CREATE SERVICE InitiatorService

AUTHORIZATION InitiatorUser01 ON QUEUE InitiatorQueue;

-- Create a

Local Route

for the destination TargetService

IF EXISTS (SELECT * FROM sys.routes WHERE name = 'TargetRoute')

DROP ROUTE

TargetRoute;

CREATE ROUTE TargetRoute WITH SERVICE_NAME = 'TargetService', ADDRESS = 'LOCAL'

/* Set up the Target user using the Target EXPORTED CERT */

-- Create Target User without login

IF EXISTS (SELECT * FROM sys.sysusers WHERE name = 'TargetUser') DROP USER TargetUser;

CREATE USER TargetUser WITHOUT LOGIN;

-- Create a Cert from the external cert file

CREATE CERTIFICATE TargetCert

AUTHORIZATION TargetUser

FROM FILE = 'c:\TargetCert.cer'

-- Create a remote service binding (only needs to be done on the initator)

CREATE REMOTE SERVICE BINDING TargetBinding

TO SERVICE 'TargetService'

WITH USER = TargetUser

-- STAGE 3:

use Target;

/* Set up the Target user using the Target EXPORTED CERT */

-- Create Initiator User without Login

IF EXISTS (SELECT * FROM sys.sysusers WHERE name = 'InitiatorUser01') DROP USER InitiatorUser01;

CREATE USER InitiatorUser01 WITHOUT LOGIN

-- Create a Cert with InitiatorUser AUTHORIZATION

-- This links the Cert to the User so SSB uses the CERT for Dialog security

IF EXISTS (SELECT * FROM sys.certificates WHERE name = 'InitiatorCert01') DROP CERTIFICATE InitiatorCert01;

-- Create a Cert from the external cert file

CREATE CERTIFICATE InitiatorCert01

AUTHORIZATION InitiatorUser01

FROM FILE = 'c:\InitiatorCert01.cer'

GRANT SEND ON SERVICE::TargetService TO InitiatorUser01

-- STAGE 4: Create a proc send SSB Message from Initiator to Target

USE Initiator;

/* Send Proc To Send SSB Message from Initiator to Target */

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'SendMessageProc') DROP PROCEDURE SendMessageProc;

CREATE PROCEDURE SendMessageProc AS

BEGIN

DECLARE @conversationHandle UNIQUEIDENTIFIER

DECLARE @message NVARCHAR(100)

BEGIN TRANSACTION;

BEGIN DIALOG @conversationHandle

FROM SERVICE InitiatorService

TO SERVICE 'TargetService'

ON CONTRACT SubmissionContract

-- Send a message on the conversation

SET @message = N'Your first cross database Secure SQL Service Broker message';

SEND ON CONVERSATION @conversationHandle

MESSAGE TYPE Message (@message)

COMMIT TRANSACTION

END

/* Send your first SSB message */

USE Initiator;

EXEC SendMessageProc;

-- STAGE 5: Make sure your message got there

USE Target;

SELECT convert( nvarchar(max), message_body ) from TargetQueue

You should see:

--------------------------------------

Your first cross database Secure SQL Service Broker message

(1 row(s) affected)

NOTE: If you run this script more than once, you will need to remove the files 'c:\InitiatorCert01.cer' and 'c:\TargetCert.cer'.

Tricky stuff Since it takes slightly over 100 lines of T/SQL to set this thing up, I won’t bore you with exactly what this all does except to say, I found the tricky parts are:

1. understanding why the ‘AUTHORIZATION’ clause matters so much on Service Broker objects when using certificate based dialog security

2. understanding why you need a database MASTER KEY

3. what is a REMOTE SERVICE BINDING anyway

4. why would you ever want to CREATE USER WITHOUT LOGIN

5. what is the magic that ensures messages actually get sent to the 'TargetService' using the credentials of the InitiatorUser01

If you are finding these points tricky yourself, don’t worry too much, I did as well. Although it all kind of makes sense to me now, the trickiness comes from securely wedging an asynchronous messaging infrastructure into what has always been a synchronous client/server system, with the emphasis on the ‘securely’ part. I can explain any of this points further is you need me to, just ask!

How about 2 machines? The purpose of this post was to get you going quickly; but, I can now hear you say, this is all fine and dandy, I am sending messages between two databases on the same (instance) machine. But what I really want is… send messages between databases on DIFFERENT machines, and I want to do it SECURELY. Okay, okay, if you ask nicely I will see what I can rustle up, in the mean time, you had better be installing SQL Server 2005 on that OTHER machine!

Setup Secure SQL Server Service Broker across databases in less than 10 seconds!

Additional resources