Contents

1
2
3 <Source Server> 襷ろ壱 , 語 蠍磯 語 語
4 <Target Server> 襷ろ壱 , 語 蠍磯 語 語
5 語 覯螳 覲旧
6 <Source Server> れ伎朱蠏 覲伎 語
7 <Target Server> れ伎朱蠏 覲伎 語
8 <Source Server> Service Broker 覃讌 /
9 <Target Server> Service Broker 覃讌 /
10 <Source Server> ろ ろ
11 <Target Server> 覃讌襯 覦讌


1 #

  • Source Server : 192.168.89.79
  • Target Server : 192.168.89.80
  • Active Directory 蟆曙企 所 蟲 螳ロ讌襷, 企 蟆曙 蠏碁Μ 豪麹螳? 蠏碁 語 蠍磯 覃讌 譯手 覦蠍磯ゼ 覲碁.
"譯殊" 覦 襷蟆 蟲 伎 . 語 蠍磯 蟆 覯螳 覃讌襯 譯手 覦蠍郁 螳覲企 曙 .

2 #

覓朱Μ朱 覿襴 蟆 覯 Source Server Target Server螳 覃讌襯 譯手 覦. 覯 Active Directory Join讌 蟆曙朱 語 蠍磯 語朱 蟆 覯螳 覃讌襯 譯手 覦.

3 <Source Server> 襷ろ壱 , 語 蠍磯 語 語 #

--Configure the transport security.
USE MASTER
go

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

CREATE CERTIFICATE ctfSourceServerMaster
WITH SUBJECT = 'SourceServerAuth'
ACTIVE FOR BEGIN_DIALOG = ON

BACKUP CERTIFICATE ctfSourceServerMaster
TO FILE = 'C:\Certificates\SourceServer.cer';
GO

4 <Target Server> 襷ろ壱 , 語 蠍磯 語 語 #

--Configure the transport security.
USE MASTER
go

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

CREATE CERTIFICATE ctfTargetServerMaster
WITH SUBJECT = 'TargetServerAuth'
ACTIVE FOR BEGIN_DIALOG = ON;

BACKUP CERTIFICATE ctfTargetServerMaster
TO FILE = 'c:\certificates\TargetServer.cer';

5 語 覯螳 覲旧 #

螳螳 覯 語螳 焔 蟆. SourceServer.cer襯 TargeServer c:\certificates 启 覲旧螻, TargetServer.cer襯 Source覯 c:\certificates 启 覲旧. 蠏碁Μ螻 れ ろ襴渚

Source Server
--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

Target Server
--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

6 <Source Server> れ伎朱蠏 覲伎 語 #

C:\Certificates\DlgSourceServer.cer殊 Target 覯 C:\Certificates襦 覲旧
--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 CERTIFICATE ctfDlgSourceServer
WITH SUBJECT = 'DlgSourceServer';

BACKUP CERTIFICATE ctfDlgSourceServer
TO FILE = 'C:\Certificates\DlgSourceServer.cer';
GO

7 <Target Server> れ伎朱蠏 覲伎 語 #

C:\Certificates\DlgTargetServer.cer殊 Source 覯 C:\Certificates襦 覲旧

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 CERTIFICATE ctfDlgTargetServer
WITH SUBJECT = 'DlgTargetServer';

BACKUP CERTIFICATE ctfDlgTargetServer
TO FILE = 'c:\certificates\DlgTargetServer.cer';

8 <Source Server> Service Broker 覃讌 / #

--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://192.168.89.80:4022';

9 <Target Server> Service Broker 覃讌 / #

--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://192.168.89.79:4022';

10 <Source Server> ろ ろ #

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 = 60;

	-- Send message.
	SEND ON CONVERSATION @conversationHandle 
	MESSAGE TYPE [mymsg] (N'Hi, from '+@@ServerName)

	--END CONVERSATION @conversationHandle
COMMIT

/*
select * from sys.transmission_queue
select * from sys.dm_broker_connections
select * from sys.dm_exec_connections
select * from sys.dm_broker_queue_monitors
select * from sys.routes
select * from myQueue

alter database SourceDB
set enable_broker

select * from sys.databases
where is_broker_enabled = 1
*/

11 <Target Server> 覃讌襯 覦讌 #

use TargetDB;
select convert(nvarchar(max), message_body)
from myQueue;

sssb08.jpg