Microsoft KB Archive/915852

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

Article ID: 915852

Article Last Modified on 5/31/2007

-

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

-





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</li> DlgTargetServer.cer</li> DlgTargetServer.pvk</li></ul>

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

</li> 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.</li> You connect to the two instances by using logins that are members of the SQL Server sysadmin fixed server role.</li> 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.</li></ul>

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
<ol> Connect to the instance on the ServerSrc server by using SQL Server Management Studio.</li>  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  is a placeholder for the password of the master key that you must specify for the database. 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. is a placeholder for the password of the newly created login. </li></ol>

Configure SQL Server 2005 Service Broker for the instance of SQL Server on the ServerTag server
<ol> Connect to the instance on the ServerTag server by using SQL Server Management Studio.</li>  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'; </li></ol>

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

<div class="references_section">