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

Article translations Article translations
Article ID: 915852 - View products that this article applies to.
Expand all | Collapse all

On This Page

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://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.
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

Properties

Article ID: 915852 - Last Review: May 31, 2007 - Revision: 1.3
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 Edition
Keywords: 
kbexpertiseadvanced kbsql2005servicebroker kbinfo KB915852

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com