You are currently offline, waiting for your internet to reconnect

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

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:
  • 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 MASTERgo--Create a master key in the master database.CREATE MASTER KEY ENCRYPTION BY password = 'MasterKeyPassword'Go--Create a certificate for transport security.CREATE CERTIFICATE ctfSourceServerMasterFROM FILE = 'C:\Certificates\SourceServer.cer'WITH PRIVATE KEY ( FILE = 'C:\Certificates\SourceServer.pvk' , DECRYPTION BY PASSWORD = 'PrivateKeyPassword' )ACTIVE FOR BEGIN_DIALOG = ONGO--Create the login and the user to own a certificate.CREATE LOGIN remcert WITH PASSWORD = 'LoginPassword'GOCREATE USER remcert FOR LOGIN remcertGOCREATE CERTIFICATE ctftTargetServerMasterAUTHORIZATION remcertFROM FILE = 'C:\Certificates\TargetServer.cer'ACTIVE FOR BEGIN_DIALOG = ONGO--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 remcertGRANT CONNECT ON ENDPOINT::BrokerEndpoint to remcertGO--Create a new database for testing.CREATE DATABASE SourceDBGOUSE SourceDBGO--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 ctfDlgSourceServerFROM FILE = 'C:\certificates\DlgSourceServer.cer'WITH PRIVATE KEY (FILE='C:\certificates\DlgSourceServer.pvk',decryption by password='PrivateKeyPassword')ACTIVE FOR BEGIN_DIALOG = ONGO--Create a user for the remcert login that owns a certificate for the dialog security.CREATE USER remcert for LOGIN remcertGOCREATE CERTIFICATE ctfDlgTargetServerAUTHORIZATION remcertFROM 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 MASTERgo--Create a master key in the master database.CREATE MASTER KEY ENCRYPTION BY password = 'MasterKeyPassword'Go--Create a certificate for transport security.CREATE CERTIFICATE ctfTargetServerMasterFROM FILE = 'c:\certificates\TargetServer.cer'WITH PRIVATE KEY (FILE='c:\certificates\TargetServer.pvk',decryption by password='PrivateKeyPassword')ACTIVE FOR BEGIN_DIALOG = ONGO--Create the login and the user to own a certificate.CREATE LOGIN remcert WITH PASSWORD = 'LoginPassword'GOCREATE USER remcert FOR LOGIN remcertGOCREATE CERTIFICATE ctfSourceServerMasterAUTHORIZATION remcertFROM FILE = 'c:\certificates\SourceServer.cer'ACTIVE FOR BEGIN_DIALOG = ONGO--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 remcertGRANT CONNECT ON ENDPOINT::BrokerEndpoint to remcertGO--Create a new database for testing.CREATE DATABASE TargetDBGOUSE TargetDBGO--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 ctfDlgTargetServerFROM FILE = 'c:\certificates\DlgTargetServer.cer'WITH PRIVATE KEY (FILE='c:\certificates\DlgTargetServer.pvk',decryption by password='PrivateKeyPassword')ACTIVE FOR BEGIN_DIALOG = ONGO--Create a user for the remcert login that owns a certificate for the dialog security.CREATE USER remcert for LOGIN remcertGOCREATE CERTIFICATE ctfDlgSourceServerAUTHORIZATION remcertFROM 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 remcertGO--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 SourceDBSET NOCOUNT ONDECLARE @conversationHandle uniqueidentifierBEGIN 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 myQueueGO
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
Properties

Article ID: 915852 - Last Review: 05/31/2007 07:27:52 - Revision: 1.3

  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Workgroup Edition
  • kbexpertiseadvanced kbsql2005servicebroker kbinfo KB915852
Feedback