• 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://msdn2.microsoft.com/en-us/library/bfsktky3(vs.71).aspx (http://msdn2.microsoft.com/en-us/library/bfsktky3(vs.71).aspx)
• 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.

Back to the top

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.

Back to the top

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';

Back to the top

Test the remote security for SQL Server 2005 Service Broker

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

SELECT CONVERT(NVARCHAR(MAX),message_body) FROM myQueue
GO

Hi, from ServerSrc

Back to the top

• Managing security (Service Broker)
• Networking and remote security
• Service Broker dialog security
• Determining the dialog security type
• Remote service bindings

Back to the top