How to use certificates for SQL Server 2005 Service Broker remote security on multiple instances of SQL Server 2005 (915852)


The information in this article applies to:

  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Workgroup

INTRODUCTION

This article describes how to use certificates for Microsoft SQL Server 2005 Service Broker remote security on multiple instances of SQL Server 2005. By using certificates in this situation, you can provide secure connections.

MORE INFORMATION

SQL Server 2005 Service Broker remote security includes operations that involve more than one instance of SQL Server when those operations use either dialog security or transport security. The following example demonstrates how to use certificates for SQL Server 2005 Service Broker remote security on two instances of SQL Server 2005. This example assumes that the following conditions are true:
  • You created four certificates and four private key files by using the Certificate Creation tool (Makecert.exe). This example assumes that those files have been copied to the C:\Certificates folder on both servers and that the files are named the following:
    • SourceServer.cer
    • SourceServer.pvk
    • TargetServer.cer
    • TargetServer.pvk
    • DlgSourceServer.cer
    • DlgSourceServer.pvk
    • DlgTargetServer.cer
    • DlgTargetServer.pvk
    For more information about how to create certificates for testing, visit the following Microsoft Developer Network (MSDN) Web site:

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cptools/html/cpgrfcertificatecreationtoolmakecertexe.asp

  • You installed two instances of SQL Server 2005 on different servers in the same network. This example assumes that the first server is named ServerSrc and that the second server is named ServerTag.
  • You connect to the two instances by using logins that are members of the SQL Server sysadmin fixed server role.
  • You made sure that the TCP port 4022 is available. In this example, the port will be used by the two instances to connect to each other.
When all the previous conditions are met, use the following procedures.

Configure SQL Server 2005 Service Broker for the instance of SQL Server on the ServerSrc server

  1. Connect to the instance on the ServerSrc server by using SQL Server Management Studio.
  2. Run the following Transact-SQL statements in the query editor:
    --Configure the transport security.
USE MASTER
go

--Create a master key in the master database.
CREATE MASTER KEY ENCRYPTION BY password = 'MasterKeyPassword'
Go

--Create a certificate for transport security.
CREATE CERTIFICATE ctfSourceServerMaster
FROM FILE = 'C:\Certificates\SourceServer.cer'
WITH PRIVATE KEY ( FILE = 'C:\Certificates\SourceServer.pvk' , DECRYPTION BY PASSWORD = 'PrivateKeyPassword' )
ACTIVE FOR BEGIN_DIALOG = ON
GO

--Create the login and the user to own a certificate.
CREATE LOGIN remcert WITH PASSWORD = 'LoginPassword'
GO
CREATE USER remcert FOR LOGIN remcert
GO
CREATE CERTIFICATE ctftTargetServerMaster
AUTHORIZATION remcert
FROM FILE = 'C:\Certificates\TargetServer.cer'
ACTIVE FOR BEGIN_DIALOG = ON
GO

--Create a new endpoint for SQL Server 2005 Service Broker, and set the AUTHENTICATION option to use the ctfSourceServerMaster certificate.
CREATE ENDPOINT BrokerEndpoint
	STATE = STARTED
	AS TCP
	(
		LISTENER_PORT = 4022
	)
	FOR SERVICE_BROKER (AUTHENTICATION = CERTIFICATE ctfSourceServerMaster)
GO

--Grant the required permissions to the remcert login.
GRANT CONNECT TO remcert
GRANT CONNECT ON ENDPOINT::BrokerEndpoint to remcert
GO

--Create a new database for testing.
CREATE DATABASE SourceDB
GO
USE SourceDB
GO

--Configure the dialog security.

--Create a master key in the SourceDB database.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MasterKeyPassword'

--Create a certificate for the SourceDB database.
CREATE CERTIFICATE ctfDlgSourceServer
FROM FILE = 'C:\certificates\DlgSourceServer.cer'
WITH PRIVATE KEY 
(FILE='C:\certificates\DlgSourceServer.pvk',decryption by password='PrivateKeyPassword')
ACTIVE FOR BEGIN_DIALOG = ON
GO

--Create a user for the remcert login that owns a certificate for the dialog security.
CREATE USER remcert for LOGIN remcert
GO
CREATE CERTIFICATE ctfDlgTargetServer
AUTHORIZATION remcert
FROM FILE = 'C:\certificates\DlgTargetServer.cer'
ACTIVE FOR BEGIN_DIALOG = ON

--Create a message type, a contract, a queue, and a service.

CREATE MESSAGE TYPE [mymsg] VALIDATION = NONE 
CREATE CONTRACT [mycon] ([mymsg] SENT BY ANY)
CREATE QUEUE [myQueue]
CREATE SERVICE [SourceService] ON QUEUE [myQueue]([mycon])
GO

--Grant the send permission to the user.
GRANT SEND ON SERVICE::[SourceService] TO remcert

--Create a remote service binding for the target service. 
CREATE REMOTE SERVICE BINDING [Certificate_Binding_on_server]
   TO SERVICE 'TargetService'
   WITH  USER = remcert,
   ANONYMOUS=Off  

--Create a route for the target service.
CREATE ROUTE [myRoute]
    WITH 
    SERVICE_NAME = 'TargetService',
    address = 'TCP://ServerTag:4022';
    Note MasterKeyPassword is a placeholder for the password of the master key that you must specify for the database. PrivateKeyPassword is a placeholder for the password of the private key that you have specified for the .pvk private key file by using the Certificate Creation tool. LoginPassword is a placeholder for the password of the newly created login.

Configure SQL Server 2005 Service Broker for the instance of SQL Server on the ServerTag server

  1. Connect to the instance on the ServerTag server by using SQL Server Management Studio.
  2. Run the following Transact-SQL statements in the query editor:
    --Configure the transport security.
USE MASTER
go

--Create a master key in the master database.
CREATE MASTER KEY ENCRYPTION BY password = 'MasterKeyPassword'
Go

--Create a certificate for transport security.
CREATE CERTIFICATE ctfTargetServerMaster
FROM FILE = 'c:\certificates\TargetServer.cer'
WITH PRIVATE KEY (FILE='c:\certificates\TargetServer.pvk',decryption by password='PrivateKeyPassword')
ACTIVE FOR BEGIN_DIALOG = ON
GO

--Create the login and the user to own a certificate.
CREATE LOGIN remcert WITH PASSWORD = 'LoginPassword'
GO
CREATE USER remcert FOR LOGIN remcert
GO
CREATE CERTIFICATE ctfSourceServerMaster
AUTHORIZATION remcert
FROM FILE = 'c:\certificates\SourceServer.cer'
ACTIVE FOR BEGIN_DIALOG = ON
GO

--Create a new endpoint for SQL Server 2005 Service Broker, and set the AUTHENTICATION option to use the ctfSourceServerMaster certificate.
CREATE ENDPOINT BrokerEndpoint
	STATE = STARTED
	AS TCP
	(
		LISTENER_PORT = 4022
	)
	FOR SERVICE_BROKER (AUTHENTICATION = CERTIFICATE ctfTargetServerMaster)
GO

--Grant the required permissions to the remcert login.
GRANT CONNECT TO remcert
GRANT CONNECT ON ENDPOINT::BrokerEndpoint to remcert
GO

--Create a new database for testing.
CREATE DATABASE TargetDB
GO
USE TargetDB
GO

--Configure the dialog security.

--Create a master key in the TargetDB database.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MasterKeyPassword'

--Create a certificate for the TargetDB database.
CREATE CERTIFICATE ctfDlgTargetServer
FROM FILE = 'c:\certificates\DlgTargetServer.cer'
WITH PRIVATE KEY 
(FILE='c:\certificates\DlgTargetServer.pvk',decryption by password='PrivateKeyPassword')
ACTIVE FOR BEGIN_DIALOG = ON
GO

--Create a user for the remcert login that owns a certificate for the dialog security.
CREATE USER remcert for LOGIN remcert
GO
CREATE CERTIFICATE ctfDlgSourceServer
AUTHORIZATION remcert
FROM FILE = 'C:\certificates\DlgSourceServer.cer'
ACTIVE FOR BEGIN_DIALOG = ON

--Create a message type, a contract, a queue, and a service.
CREATE MESSAGE TYPE [mymsg] VALIDATION = NONE 
CREATE CONTRACT [mycon] ([mymsg] SENT BY ANY)
CREATE QUEUE [myQueue]
CREATE SERVICE [TargetService] ON QUEUE [myQueue]([mycon])
GO

--Grant the send permission to the user.
GRANT SEND ON SERVICE::[TargetService] TO remcert
GO

--Create a remote service binding for the target service. 
CREATE REMOTE SERVICE BINDING [Certificate_Binding_on_server]
   TO SERVICE 'SourceService'
   WITH  USER = remcert,
   ANONYMOUS=Off  
--Create a route for the target service.
CREATE ROUTE [myRoute]
    WITH 
    SERVICE_NAME = 'SourceService',
    address = 'TCP://ServerSrc:4022';

Test the remote security for SQL Server 2005 Service Broker

After you configure the two instances, connect to the instance on the ServerSrc server, and then run the following statements to test the SQL Server 2005 Service Broker service:
USE SourceDB
SET NOCOUNT ON
DECLARE @conversationHandle uniqueidentifier
BEGIN TRANSACTION
	-- Start dialog.
	BEGIN DIALOG  @conversationHandle
	FROM SERVICE    [SourceService]
	TO SERVICE      'TargetService'
	ON CONTRACT     [mycon]
	WITH ENCRYPTION = ON, LIFETIME = 600;

	-- Send message.
	SEND ON CONVERSATION @conversationHandle 
	MESSAGE TYPE [mymsg] (N'Hi, from '+@@ServerName)
COMMIT
After you run these statements, connect to the instance on the ServerTag server, and then run the following statement:
SELECT CONVERT(NVARCHAR(MAX),message_body) FROM myQueue
GO
You will receive the following result:
Hi, from ServerSrc

REFERENCES

For more information about SQL Server 2005 Service Broker, see the following topics in SQL Server 2005 Books Online:
  • Managing security (Service Broker)
  • Networking and remote security
  • Service Broker dialog security
  • Determining the dialog security type
  • Remote service bindings
Modification Type:MinorLast Reviewed:5/16/2006
Keywords:kbExpertiseAdvanced kbsql2005servicebroker kbinfo KB915852 kbAudITPRO kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.