Securing a dialog with certificates

[SQL-Server-Team]

First published on MSDN on Aug 26, 2008

This sample shows how to set up a secure dialog using certificates. Service broker will always have a level of security at the transport level, which may include encryption, but this is at a server level of granularity. It does not secure conversations on a database-to-database basis. If this is required, then dialog security can be used. Dialog security is also end-to-end as opposed to the point-to-point connection-based security provided by transport security. Since conversations may entail multiple hops through the use of forwarding, dialog security can provide authentication and one-time encryption at the terminating services. Certificate-based authentication also allows users to specify a window of time in which authentication will be honored.

The initiator and target certificates must be exchanged in order for them to authenticate each other. This "out of band" exchange should be done with a high level of trust, since a certificate bearer will be able to begin dialogs and send messages to service broker services in the authenticating server.

Running the sample

1. 
   

   This sample 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. To keep things simple, Windows authentication is used for transport security. The transport security sample shows how to use certificates for this if needed.

   
   
2. 
   

   Run the scripts, in order:



Initiator endpoint setup.

Target endpoint setup.

Initiator service setup.

Target service setup.

Initiator certification of target.

Target certification of initiator.

Initiator message send.

Target message receive.

Initiator cleanup.

Target cleanup.

Scripts

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

-- Script for dialog security sample.

--

-- This file is part of the Microsoft SQL Server Code Samples.

-- Copyright (C) Microsoft Corporation. All Rights reserved.

-- This source code is intended only as a supplement to Microsoft

-- Development Tools and/or on-line documentation. See these other

-- materials for detailed information regarding Microsoft code samples.

--

-- THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF

-- ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO

-- THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A

-- PARTICULAR PURPOSE.

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

-- Set up an initiator service broker endpoint for dialog

-- certificate-based security.

-- Modify domain_name and target_host in script to suit configuration.

USE master;

GO

-- Create the broker endpoint using Windows authentication.

IF EXISTS (SELECT * FROM sys.endpoints WHERE name = 'service_broker_endpoint')

DROP ENDPOINT service_broker_endpoint;

GO

CREATE ENDPOINT service_broker_endpoint

STATE = STARTED

AS TCP (LISTENER_PORT = 4022)

FOR SERVICE_BROKER (AUTHENTICATION = Windows);

GO

-- Create a login for the target machine (target_host) in the shared domain

-- (domain_name). This assumes the availability of Kerberos authentication.

-- Note: the '$' is significant.

CREATE LOGIN [domain_name\target_host$] FROM Windows;

GO

-- Grant the target connection access to the endpoint.

GRANT CONNECT ON ENDPOINT::service_broker_endpoint TO [domain_name\target_host$];

GO

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

-- Script for dialog security sample.

--

-- This file is part of the Microsoft SQL Server Code Samples.

-- Copyright (C) Microsoft Corporation. All Rights reserved.

-- This source code is intended only as a supplement to Microsoft

-- Development Tools and/or on-line documentation. See these other

-- materials for detailed information regarding Microsoft code samples.

--

-- THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF

-- ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO

-- THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A

-- PARTICULAR PURPOSE.

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

-- Set up a target service broker endpoint for dialog

-- certificate-based security.

-- Modify domain_name and initiator_host in script to suit configuration.

USE master;

GO

-- Create the broker endpoint using Windows authentication.

IF EXISTS (SELECT * FROM sys.endpoints WHERE name = 'service_broker_endpoint')

DROP ENDPOINT service_broker_endpoint;

GO

-- Use Windows for authentication.

CREATE ENDPOINT service_broker_endpoint

STATE = STARTED

AS TCP (LISTENER_PORT = 4022)

FOR SERVICE_BROKER (AUTHENTICATION = Windows);

GO

-- Create a login for the initiator machine (initiator_host) in the shared domain

-- (domain_name). This assumes the availability of Kerberos authentication.

-- Note: the '$' is significant.

CREATE LOGIN [domain_name\initiator_host$] FROM Windows;

GO

-- Grant the initiator connection access to the endpoint.

GRANT CONNECT ON ENDPOINT::service_broker_endpoint TO [domain_name\initiator_host$];

GO

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

-- Script for dialog security sample.

--

-- This file is part of the Microsoft SQL Server Code Samples.

-- Copyright (C) Microsoft Corporation. All Rights reserved.

-- This source code is intended only as a supplement to Microsoft

-- Development Tools and/or on-line documentation. See these other

-- materials for detailed information regarding Microsoft code samples.

--

-- THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF

-- ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO

-- THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A

-- PARTICULAR PURPOSE.

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

-- The initiator creates a database, queue, service, target route,

-- and certificate for the dialog to the target service.

-- Modify target_host and location of stored certificate in script

-- to suit configuration.

USE master;

GO

-- Create initiator database.

IF EXISTS (SELECT * FROM sys.databases WHERE name = 'initiator_database')

DROP DATABASE initiator_database;

GO

CREATE DATABASE initiator_database;

GO

USE initiator_database;

GO

-- Create a message queue.

CREATE QUEUE initiator_queue;

GO

-- Create a service with a default contract.

CREATE SERVICE initiator_service ON QUEUE initiator_queue ([DEFAULT]);

GO

-- Create a route to the target service.

CREATE ROUTE target_route

WITH SERVICE_NAME = 'target_service',

ADDRESS = 'tcp://target_host:4022';

GO

-- Create a user who is authorized for the initiator service.

IF NOT EXISTS (SELECT * FROM sys.sysusers WHERE name = 'initiator_user')

CREATE USER initiator_user WITHOUT LOGIN;

GO

ALTER AUTHORIZATION ON SERVICE::initiator_service TO initiator_user;

GO

-- A master key is required to use certificates.

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 a certificate and associate it with the user.

IF EXISTS (SELECT * FROM sys.certificates WHERE name = 'initiator_dialog_cert')

DROP CERTIFICATE initiator_dialog_cert;

GO

CREATE CERTIFICATE initiator_dialog_cert

AUTHORIZATION initiator_user

WITH SUBJECT = 'Dialog certificate for initiator';

GO

-- Backup to a file to allow the certificate to be given to the target.

BACKUP CERTIFICATE initiator_dialog_cert

TO FILE = 'c:\initiator_dialog.cert';

GO

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

-- Script for dialog security sample.

--

-- This file is part of the Microsoft SQL Server Code Samples.

-- Copyright (C) Microsoft Corporation. All Rights reserved.

-- This source code is intended only as a supplement to Microsoft

-- Development Tools and/or on-line documentation. See these other

-- materials for detailed information regarding Microsoft code samples.

--

-- THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF

-- ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO

-- THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A

-- PARTICULAR PURPOSE.

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

-- The target creates a database, queue, service, initiator route,

-- and certificate for the dialog to the initiator service.

-- Modify initiator_host and location of stored certificate in script

-- to suit configuration.

USE master;

GO

-- Create target database.

IF EXISTS (SELECT * FROM sys.databases WHERE name = 'target_database')

DROP DATABASE target_database;

GO

CREATE DATABASE target_database;

GO

USE target_database;

GO

-- Create a message queue.

CREATE QUEUE target_queue;

GO

-- Create a service with a default contract.

CREATE SERVICE target_service ON QUEUE target_queue ([DEFAULT]);

GO

-- Create a route to the initiator service.

CREATE ROUTE initiator_route

WITH SERVICE_NAME = 'initiator_service',

ADDRESS = 'tcp://initiator_host:4022';

GO

-- Create a user who is authorized for the target service.

IF NOT EXISTS (SELECT * FROM sys.sysusers WHERE name = 'target_user')

CREATE USER target_user WITHOUT LOGIN;

GO

ALTER AUTHORIZATION ON SERVICE::target_service TO target_user;

GO

-- A master key is required to use certificates.

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 a certificate and associate it with the user.

IF EXISTS (SELECT * FROM sys.certificates WHERE name = 'target_dialog_cert')

DROP CERTIFICATE target_dialog_cert;

GO

CREATE CERTIFICATE target_dialog_cert

AUTHORIZATION target_user

WITH SUBJECT = 'Dialog certificate for target';

GO

-- Backup to a file to allow the certificate to be given to the initiator.

BACKUP CERTIFICATE target_dialog_cert

TO FILE = 'c:\target_dialog.cert';

GO

----------EXCHANGE CERTIFICATES BEFORE PROCEEDING---------------

-- The initiator and target certificates must be exchanged in order for them to

-- authenticate each other. In a production system, this "out of band" exchange

-- should be done with a high level of trust, since a certificate bearer will be

-- able to begin dialogs and send messages to the secured service.However, assuming

-- the sample is being used on a development system, the exchange may be simple

-- remote copies.

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

-- Script for dialog security sample.

--

-- This file is part of the Microsoft SQL Server Code Samples.

-- Copyright (C) Microsoft Corporation. All Rights reserved.

-- This source code is intended only as a supplement to Microsoft

-- Development Tools and/or on-line documentation. See these other

-- materials for detailed information regarding Microsoft code samples.

--

-- THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF

-- ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO

-- THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A

-- PARTICULAR PURPOSE.

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

-- The initiator creates a target user certified by the target certificate,

-- binds it to the target service, and grants it send access to the initiator

-- service.

-- Modify location of stored certificate in script to suit configuration.

USE initiator_database;

GO

-- Create a user for the target.

IF NOT EXISTS (SELECT * FROM sys.sysusers WHERE name = 'target_user')

CREATE USER target_user WITHOUT LOGIN;

GO

IF EXISTS (SELECT * FROM sys.certificates WHERE name = 'target_dialog_cert')

DROP CERTIFICATE target_dialog_cert;

GO

-- Associate the target user with the target certificate.

CREATE CERTIFICATE target_dialog_cert

AUTHORIZATION target_user

FROM FILE = 'c:\target_dialog.cert';

GO

-- Bind the target service to the target user.

IF EXISTS (SELECT * FROM sys.remote_service_bindings WHERE name = 'remote_target_service_binding')

DROP REMOTE SERVICE BINDING remote_target_service_binding;

GO

CREATE REMOTE SERVICE BINDING remote_target_service_binding

TO SERVICE 'target_service'

WITH USER = target_user;

GO

-- Allow the target to send to the initiator service.

GRANT SEND ON SERVICE::initiator_service TO target_user;

GO

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

-- Script for dialog security sample.

--

-- This file is part of the Microsoft SQL Server Code Samples.

-- Copyright (C) Microsoft Corporation. All Rights reserved.

-- This source code is intended only as a supplement to Microsoft

-- Development Tools and/or on-line documentation. See these other

-- materials for detailed information regarding Microsoft code samples.

--

-- THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF

-- ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO

-- THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A

-- PARTICULAR PURPOSE.

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

-- The target creates an initiator user certified by the initiator certificate,

-- binds it to the initiator service, and grants it send access to the target

-- service.

-- Modify location of stored certificate in script to suit configuration.

USE target_database;

GO

-- Create a user for the initiator.

IF NOT EXISTS (SELECT * FROM sys.sysusers WHERE name = 'initiator_user')

CREATE USER initiator_user WITHOUT LOGIN;

GO

-- Associate the initiator user with the initiator certificate.

IF EXISTS (SELECT * FROM sys.certificates WHERE name = 'initiator_dialog_cert')

DROP CERTIFICATE initiator_dialog_cert;

GO

CREATE CERTIFICATE initiator_dialog_cert

AUTHORIZATION initiator_user

FROM FILE = 'c:\initiator_dialog.cert';

GO

-- Bind the initiator service to the initiator user.

IF EXISTS (SELECT * FROM sys.remote_service_bindings WHERE name = 'remote_initiator_service_binding')

DROP REMOTE SERVICE BINDING remote_initiator_service_binding;

GO

CREATE REMOTE SERVICE BINDING remote_initiator_service_binding

TO SERVICE 'initiator_service'

WITH USER = initiator_user;

GO

-- Allow the initiator to send to the target service.

GRANT SEND ON SERVICE::target_service TO initiator_user;

GO

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

-- Script for dialog security sample.

--

-- This file is part of the Microsoft SQL Server Code Samples.

-- Copyright (C) Microsoft Corporation. All Rights reserved.

-- This source code is intended only as a supplement to Microsoft

-- Development Tools and/or on-line documentation. See these other

-- materials for detailed information regarding Microsoft code samples.

--

-- THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF

-- ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO

-- THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A

-- PARTICULAR PURPOSE.

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

-- The initiator creates a dialog and sends a message to the target service.

USE initiator_database;

GO

-- Create a message.

DECLARE @message varchar(max);

SELECT @message = 'Hello from initiator';

-- Create an encrypted dialog to the target service.

DECLARE @handle uniqueidentifier;

BEGIN DIALOG CONVERSATION @handle

FROM SERVICE initiator_service

TO SERVICE 'target_service'

WITH ENCRYPTION = ON;

-- Send the message.

SEND ON CONVERSATION @handle (@message);

PRINT 'Message sent to target_service on conversation:';

PRINT @handle;

GO

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

-- Script for dialog security sample.

--

-- This file is part of the Microsoft SQL Server Code Samples.

-- Copyright (C) Microsoft Corporation. All Rights reserved.

-- This source code is intended only as a supplement to Microsoft

-- Development Tools and/or on-line documentation. See these other

-- materials for detailed information regarding Microsoft code samples.

--

-- THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF

-- ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO

-- THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A

-- PARTICULAR PURPOSE.

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

-- The target waits for and receives a message from the initiator.

USE target_database;

GO

DECLARE @message_body varchar(max);

-- Wait for the message.

WAITFOR(

RECEIVE TOP(1)

@message_body=message_body

FROM target_queue

);

PRINT 'Target received message: ' + @message_body;

GO

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

-- Script for dialog security sample.

--

-- This file is part of the Microsoft SQL Server Code Samples.

-- Copyright (C) Microsoft Corporation. All Rights reserved.

-- This source code is intended only as a supplement to Microsoft

-- Development Tools and/or on-line documentation. See these other

-- materials for detailed information regarding Microsoft code samples.

--

-- THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF

-- ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO

-- THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A

-- PARTICULAR PURPOSE.

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

-- Clean up the initiator.

-- Modify domain_name and target_host in script to suit configuration.

-- The recommended method is for the initiator to inform the target
-- with an "end of stream" message which causes the target to end the
-- conversation.

USE master;

GO

IF EXISTS (SELECT * FROM sys.databases WHERE name = 'initiator_database')

DROP DATABASE initiator_database;

GO

IF EXISTS (SELECT * FROM sys.endpoints WHERE name = 'service_broker_endpoint')

DROP ENDPOINT service_broker_endpoint;

GO

IF EXISTS (SELECT * FROM sys.syslogins WHERE name = 'domain_name\target_host$')

DROP LOGIN [domain_name\target_host$];

GO

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

-- Script for dialog security sample.

--

-- This file is part of the Microsoft SQL Server Code Samples.

-- Copyright (C) Microsoft Corporation. All Rights reserved.

-- This source code is intended only as a supplement to Microsoft

-- Development Tools and/or on-line documentation. See these other

-- materials for detailed information regarding Microsoft code samples.

--

-- THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF

-- ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO

-- THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A

-- PARTICULAR PURPOSE.

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

-- Clean up the target.

-- Modify domain_name and initiator_host in script to suit configuration.

-- For simplicity, the dialog is terminated by dropping the database.
-- The recommended method is for the initiator to inform the target
-- with an "end of stream" message which causes the target to end the
-- conversation.

USE master;

GO

IF EXISTS (SELECT * FROM sys.databases WHERE name = 'target_database')

DROP DATABASE target_database;

GO

IF EXISTS (SELECT * FROM sys.endpoints WHERE name = 'service_broker_endpoint')

DROP ENDPOINT service_broker_endpoint;

GO

IF EXISTS (SELECT * FROM sys.syslogins WHERE name = 'domain_name\initiator_host$')

DROP LOGIN [domain_name\initiator_host$];

GO