A simple secure dialog with transport certificates

  • Article

If you want to have a broker dialog between server instances, you have to be concerned with having a secure network connection, which in service broker terminology is called transport security. An effective way to accomplish this is using certificates to secure the broker endpoints. Then you can safely establish dialogs knowing that the communication context is authenticated. By default, messages are also encrypted on behalf of dialogs by the endpoints to prevent monitoring.

Certificate-based authentication is more cumbersome to set up than Windows based authentication, but it works in more general circumstances, e.g., between different domains, and allows users to specify a window of time in which authentication will be honored. In any case, some form of transport security is always necessary.

Transport security allows dialogs to be set up between services and is not concerned with permissions and security associated with these services. For example, in the code below the target service will accept messages from any source in the sender server. If this is an issue, then dialog security can help. A tutorial is available here.

Configuration 

This example requires two server instances on different machines to avoid a port collision. It is essential that the servers are configured to enable communication protocols. In this example, we will be using TCP, so use the SQL Server Configuration Manager to make sure TCP is enabled on both servers.

Initiator

Here is the initiator code, using the "sender" service, queue, and database. 

-- Initiator part of dialog with certificate-based transport security.

use master;
go

-- We need a master key to create a certificate.

BEGIN TRANSACTION;
IF NOT EXISTS (SELECT * FROM sys.symmetric_keys where name = '##MS_DatabaseMasterKey##')
 CREATE MASTER KEY ENCRYPTION BY PASSWORD='Password#123'
COMMIT;
go

-- By default, the certificate is valid immediately and expires in one year. 

create certificate sender_transport_cert
 with subject = 'SSB transport authentication for sender'
go

-- Write the cert to a file since we have to transfer it to the target.

backup certificate sender_transport_cert
 to file = 'c:\remote\sender_transport_cert.cert'
go

create endpoint SsbEndpoint
state = started
as tcp (listener_port = 4022)
for service_broker (authentication = certificate sender_transport_cert)
go

----------Exchange certificates before proceeding---------------

-- Make sure you trust the certificate bearer!

-- A user must be associated with the target certificate.

create login target_user with password = 'Password#123'
go

create user target_user
go

create certificate target_transport_cert
 authorization target_user
 from file = 'c:\remote\target_transport_cert.cert'
go

-- The target user is allowed to connect to the endpoint. 

grant connect on endpoint::SsbEndpoint to target_user
go

-------Endpoint security complete, proceed to create dialog------

create database [sender]
go

alter database [sender]
 set recovery simple;
go

use [sender];
go

create queue [sender];
go

create service [sender] on queue [sender] ([DEFAULT]);
go 

grant send on service::sender to [public];
go

-- This is the route to the target service: 

create route [target]
 with service_name = 'target',
 address = 'tcp://targetserver:4022';
go

-- Messages returning to the server are routed with an entry in msdb. 

use msdb;
go

create route [sender]
 with service_name = 'sender',
 address = 'local';
go

use sender;
go

--------Make sure target is completely set up before continuing------

-- Send an empty message to target.

-- Note that the endpoint will do the encryption. 

declare @h uniqueidentifier;
declare @payload varchar(max);
select @payload = replicate(0x00,1024);

begin dialog conversation @h
      from service [sender]
      to service 'target'
      with encryption = off;

send on conversation @h (@payload);

Target 

Here is the target code, using the "target" service, queue, and database.

-- Target part of dialog with certificate-based transport security.

use master;
go

BEGIN TRANSACTION;
IF NOT EXISTS (SELECT * FROM sys.symmetric_keys where name = '##MS_DatabaseMasterKey##')
 CREATE MASTER KEY ENCRYPTION BY PASSWORD='Password#123'
COMMIT;
go

create certificate target_transport_cert
 with subject = 'SSB transport authentication for target'
go

backup certificate target_transport_cert
 to file = c:\remote\target_transport_cert.cert'
go

create endpoint SsbEndpoint
state = started
as tcp (listener_port = 4022)
for service_broker (authentication = certificate target_transport_cert)
go

----------Exchange certificates before proceeding---------------

create login sender_user with password = 'Password#123'
go

create user sender_user
go

create certificate sender_transport_cert
 authorization sender_user
 from file = c:\remote\sender_transport_cert.cert'
go

grant connect on endpoint::SsbEndpoint to sender_user
go

------------------Endpoint set up, create the target part of the dialog-------------

create database [target]
go

alter database [target]
 set recovery simple;
go

use [target];
go

create queue [target];
go

create service [target] on queue [target] ([DEFAULT]);
go

grant send on service::target to [public];
go

create route [sender]
 with service_name = 'sender',
 address = 'tcp://senderserver:4022';
go

use msdb;
go

create route [target]
 with service_name = 'target',
 address = 'local';
go

use target;
go

----------Send message from sender before proceeding--------------

select * from target