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> 메시지를 받았는지 수행

• Source Server : 192.168.89.79
  
• Target Server : 192.168.89.80
  
• Active Directory 환경이면 쉽게 구현이 가능하지만, 어디 환경이 그리 녹녹한가? 그래서 이 예제는 인증서 기반의 메시지 주고 받기를 해 본다.
  

"주의사항" 반드시 순서에 맞게 구현을 해야 한다. 인증서 기반의 원격 서버간 메시지를 주고 받기가 생각보다 쉽지 않다.

물리적으로 분리된 원격 서버 Source Server와 Target Server간에 메시지를 주고 받는다. 두 서버는 Active Directory에 Join되지 않은 환경으로 인증서 기반의 인증으로 원격 서버간의 메시지를 주고 받는다.

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

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

각각의 서버에 인증서가 생성되었을 것임. 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

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

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

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

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

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
*/

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