Setup Secure SQL Server Service Broker across databases in less than 10 seconds!
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
GO
-- 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>'
GO
/* 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';
GO
/* 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'
GO
-- 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;
GO
-- 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>';
GO
/* 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';
GO
/* 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'
GO
/* 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'
GO
-- Create a remote service binding (only needs to be done on the initator)
CREATE REMOTE SERVICE BINDING TargetBinding
TO SERVICE 'TargetService'
WITH USER = TargetUser
GO
-- 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'
GO
GRANT SEND ON SERVICE::TargetService TO InitiatorUser01
GO
-- 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;
GO
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
GO
/* Send your first SSB message */
USE Initiator;
EXEC SendMessageProc;
GO
-- STAGE 5: Make sure your message got there
USE Target;
GO
SELECT convert( nvarchar(max), message_body ) from TargetQueue
GO
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!
Comments
Anonymous
August 27, 2006
This is a good script but it did not work in my machine. The message is not received.Anonymous
August 28, 2006
What other set up in the service broker must I do? I already set the TRUSTWORTHY ON and ENABLE_BROKER of the involved databases. Thanks.Anonymous
April 02, 2007
This script only worked for me after I set the START_DATE option on the CREATE CERTIFICATE statements. Otherwise, very good, and a useful hook to start learning Service Broker. ThanksAnonymous
September 24, 2007
Your example worked perfectly. Did you ever put together an example of messaging across db servers?Anonymous
October 19, 2007
hi Stuart thanks for such a wonderful script. I m a new developer working with Service Broker just wanted to know, if you can help me in understanding What is the Set of permissions we are required to set when we want to establish Service Broker across Servers.Anonymous
October 24, 2007
So why do i have to understanding why the ‘AUTHORIZATION’ clause matters so much on Service Broker objects when using certificate based dialog security? And why would I ever want to CREATE USER WITHOUT LOGIN?Anonymous
June 05, 2008
hi stuart, FOA, thanks for such a wonderful stuff, i am trying the same on two different instances can you guide me through. I will feel obliged. regards, RahulAnonymous
August 26, 2008
me too iam also trying the same on two different machines have you got anythingAnonymous
October 09, 2008
Try this example for setting up messaging over distributed databases http://www.sqlservercentral.com/articles/Service+Broker/2797/Anonymous
June 01, 2009
PingBack from http://paidsurveyshub.info/story.php?id=73474Anonymous
June 15, 2009
PingBack from http://edebtsettlementprogram.info/story.php?id=24400Anonymous
June 16, 2009
PingBack from http://fixmycrediteasily.info/story.php?id=4717