Service Broker: 원격서버간 메시지 주고 받기

 

 

환경(Virtual Server 이용)

SourceServer : 192.168.89.79

TargetServer : 192.168.89.80

 

1. [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 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 CERTIFICATE ctfSourceServerMaster

WITH SUBJECT = 'SourceServerAuth'

ACTIVE FOR BEGIN_DIALOG = ON

 

BACKUP CERTIFICATE ctfSourceServerMaster

TO FILE = 'C:\Certificates\SourceServer.cer';

GO

 

2. [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 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 CERTIFICATE ctfTargetServerMaster

WITH SUBJECT = 'TargetServerAuth'

ACTIVE FOR BEGIN_DIALOG = ON;

 

BACKUP CERTIFICATE ctfTargetServerMaster

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

 

3. [Source Server]각각의 서버에 인증서가 생성되었을 것임. SourceServer.cer를 TargeServer의 c:\certificates 폴더에 복사하고, TargetServer.cer를 Source서버의 c:\certificates 폴더에 복사한다. 그리고 다음 스크립트 수행

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

 

4. [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

 

5. [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 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 CERTIFICATE ctfDlgSourceServer

WITH SUBJECT = 'DlgSourceServer';

 

BACKUP CERTIFICATE ctfDlgSourceServer

TO FILE = 'C:\Certificates\DlgSourceServer.cer';

GO

 

6. [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 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 CERTIFICATE ctfDlgTargetServer

WITH SUBJECT = 'DlgTargetServer';

 

BACKUP CERTIFICATE ctfDlgTargetServer

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

 

7. [Source Server]

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

 

8. [Target Server]

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

 

9. [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.