[Service Broker] 인증서를 사용한 인스턴스 간의 대화

BOL의 자습서를 마치셨다면 인증서를 사용하는 방법을 맛 보셨으리라 생각합니다.
아마 인증서를 SERVICE 개체에 매핑하는 것을 보셨을텐데요.

SSB에서 인증서를 사용할 수 있는 곳이 하나 더 있습니다.
그것은 END POINT의 인증 방식으로 Windows 인증 대신 인증서를 선택하는 것이죠.

실제 환경에서 Windows 인증을 사용하기 어려운 경우가 존재하기 때문에, 인증서를 사용하는 것을 권해드리고 싶습니다.

이번 글에서는 END POINT의 인증 방식으로 CERTIFICATE를 사용하는 방법과 함께.. 인스턴스 간의 대화에 필요한 SSB 개체를 하나 하나 살펴보겠습니다.

(스크립트와 주석으로 대부분의 설명을 대신합니다. 꼼꼼히 읽어 주세요.)


사전 준비

- 2개의 SQL Server 인스턴스를 준비합니다. 분리된 머신이어도 좋고 Named Instance여도 좋습니다.

- C:\ 드라이브에 "SSBCert" 폴더를 생성하고, 아래 스크립트를 실행합니다.

- 스크립트 실행 후 C:\SSBCert 폴더에 생성된 파일을 각각의 SQL  Server 인스턴스가 설치된 머신의 C:\SSBCert 폴더에 복사합니다.

(인증서마다 1개의 public key와 1개의 private key 파일이 생성됩니다. 총 8개 파일)

USE master;
GO
 
CREATE DATABASE TempCert;
GO
 
USE TempCert;
GO
 
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'pass1234!';
GO
 
CREATE CERTIFICATE InitDBServiceCertPriv
WITH SUBJECT = 'ForInitDBService';
GO
 
CREATE CERTIFICATE InitDBAuthCertPriv
WITH SUBJECT = 'ForInitDBAuth';
GO
 
CREATE CERTIFICATE TargetDBServiceCertPriv
WITH SUBJECT = 'ForTargetDBService';
GO
 
CREATE CERTIFICATE TargetDBAuthCertPriv
WITH SUBJECT = 'ForTargetDBAuth';
GO
 
BACKUP CERTIFICATE InitDBServiceCertPriv
    TO FILE = 'C:\SSBCert\InitDBServiceCertPub.cer'
    WITH PRIVATE KEY (
           FILE = 'C:\SSBCert\InitDBSeviceCertPriv.pvk',
           ENCRYPTION BY PASSWORD = '63DD083E-E1CB-44D1-B8AE-357F85D6A26A'
    );
GO
 
BACKUP CERTIFICATE InitDBAuthCertPriv TO FILE = 'C:\SSBCert\InitDBAuthCertPub.cer'
    WITH PRIVATE KEY (
           FILE = 'C:\SSBCert\InitDBAuthCertPriv.pvk',
           ENCRYPTION BY PASSWORD = '63DD083E-E1CB-44D1-B8AE-357F85D6A26A'
    );
GO
 
BACKUP CERTIFICATE TargetDBServiceCertPriv
    TO FILE = 'C:\SSBCert\TargetDBServiceCertPub.cer'
    WITH PRIVATE KEY (
           FILE = 'C:\SSBCert\TargetDBServiceCertPriv.pvk',
           ENCRYPTION BY PASSWORD = '63DD083E-E1CB-44D1-B8AE-357F85D6A26A'
    );
 
BACKUP CERTIFICATE TargetDBAuthCertPriv
    TO FILE = 'C:\SSBCert\TargetDBAuthCertPub.cer'
    WITH PRIVATE KEY (
           FILE = 'C:\SSBCert\TargetDBAuthCertPriv.pvk',
           ENCRYPTION BY PASSWORD = '63DD083E-E1CB-44D1-B8AE-357F85D6A26A'
    );
GO
 
USE master;
GO
 
DROP DATABASE TempCert;
GO

INITIATOR 인스턴스 설정

/* Section 1. END POINT에대한연결인증방식으로CERTIFICATE를사용하기위한설정 */
 
USE master;
GO
 
-- CERTIFICATE [InitDBAuthCertPriv] 생성(private key 포함)
-- Target 인스턴스에서Initiator 인스턴스의END POINT [InitDBEndpoint]에연결할때,
-- LOGIN [InitDBProxy]를인증하기위한목적
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'InitMasterDBPass!@#$';
GO
 
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'InitMasterDBPass!@#$';
 
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
 
CREATE CERTIFICATE InitDBAuthCertPriv
FROM FILE = 'C:\SSBCert\InitDBAuthCertPub.cer'
WITH PRIVATE KEY (
    FILE = 'C:\SSBCert\InitDBAuthCertPriv.pvk',
    DECRYPTION BY PASSWORD = '63DD083E-E1CB-44D1-B8AE-357F85D6A26A'
);
 
CLOSE MASTER KEY;
GO
 
-- END POINT [InitDBEndpoint] 생성
-- TCP 통신을하며4000번포트를listen합니다.
-- CERTIFICATE [InitDBAuthCertPriv]를사용하여Target 인스턴스로부터의연결을인증합니다.
CREATE ENDPOINT InitDBEndpoint STATE = STARTED
AS TCP (LISTENER_PORT = 4000)
FOR SERVICE_BROKER (AUTHENTICATION = CERTIFICATE InitDBAuthCertPriv);
GO
 
-- Initiator 인스턴스의END POINT [InitDBEndpoint]를통해Target 인스턴스로접속할LOGIN [TargetDBProxy] 생성
CREATE LOGIN TargetDBProxy WITH PASSWORD = 'pass^(*&*()';
GO
 
CREATE USER TargetDBProxy;
GO
 
-- CERTIFICATE [TargetDBAuthCertPub] 생성(public key only)
-- CERTIFICATE의소유자를USER [TargetDBProxy]로지정
CREATE CERTIFICATE TargetDBAuthCertPub
AUTHORIZATION TargetDBProxy
FROM FILE = 'C:\SSBCert\TargetDBAuthCertPub.cer';
GO
 
-- END POINT [InitDBEndpoint]에대한CONNECT 권한을LOGIN [TargetDBProxy]에게부여
-- [TargetDBProxy]는CERTIFICATE [TargetDBAuthCertPub]에매핑되어있는SQL LOGIN 입니다.
GRANT CONNECT ON ENDPOINT :: InitDBEndpoint TO TargetDBProxy
GO

/* Section 2. Initiator 인스턴스설정 */
 
-- Initiator 인스턴스에데이터베이스[InitDB] 생성
CREATE DATABASE InitDB;
GO
 
ALTER DATABASE InitDB SET ENABLE_BROKER;
GO
 
USE InitDB;
GO
 
-- 미리Queue 활성화SP를더미로생성합니다. (이후에ALTER하여로직을구현합니다)
CREATE PROCEDURE dbo.P_InitDBQueueActivate
AS
RETURN 0;
GO
 
-- MESSAGE TYPE [MsgToTargetDB], [MsgFromTargetDB], [MsgEndOfStream]을생성합니다.
-- VALIDATION을NONE으로지정하면성능이향상됩니다.
-- VALIDATION이EMPTY이면메시지본문이없다는의미입니다.
-- 동일한MESSAGE TYPE이Initiator와Target에생성되어야합니다.
CREATE MESSAGE TYPE MsgToTargetDB VALIDATION = NONE;
 
CREATE MESSAGE TYPE MsgFromTargetDB VALIDATION = NONE;
 
CREATE MESSAGE TYPE MsgEndOfStream VALIDATION = EMPTY;
GO
 
-- CONTRACT [LoggingContract]를생성합니다.
-- 동일한이름과내용의CONTRACT가Initiator와Target에생성되어야합니다.
CREATE CONTRACT LoggingContract (
    MsgEndOfStream SENT BY INITIATOR,
    MsgToTargetDB SENT BY INITIATOR,
    MsgFromTargetDB SENT BY TARGET
);
 
-- QUEUE [InitDBQueue]를생성합니다.
-- QUEUE 활성화프로시저로[P_InitDBQueueActivate]를지정합니다.
CREATE QUEUE dbo.InitDBQueue
WITH ACTIVATION (
    STATUS = ON,
    PROCEDURE_NAME = dbo.P_InitDBQueueActivate,
    MAX_QUEUE_READERS = 16,
    EXECUTE AS SELF
);
 
-- USER [InitDBServiceUser]를생성합니다.
CREATE USER InitDBServiceUser WITHOUT LOGIN;
GO
 
-- SERVICE [InitDBService]를생성합니다.
--[InitDBService] 서비스로전송되는모든메시지는QUEUE [InitDBQueue]에들어갑니다.
CREATE SERVICE InitDBService ON QUEUE dbo.InitDBQueue;
 
-- USER [InitDBServiceUser]를SERVICE [initDBService]의소유자로지정합니다.
ALTER AUTHORIZATION ON SERVICE :: InitDBService TO InitDBServiceUser;
GO
 
-- CERTIFICATE [InitDBServiceCertPriv]의소유자를USER [InitDBServiceUser]로지정하여생성합니다.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'InitDBPass!@#$';
GO
 
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'InitDBPass!@#$';
 
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
 
CREATE CERTIFICATE InitDBServiceCertPriv
    AUTHORIZATION InitDBServiceUser
    FROM FILE = 'C:\SSBCert\InitDBServiceCertPub.cer'
    WITH PRIVATE KEY (
           FILE = 'C:\SSBCert\InitDBSeviceCertPriv.pvk',
           DECRYPTION BY PASSWORD = '63DD083E-E1CB-44D1-B8AE-357F85D6A26A'
    );
 
CLOSE MASTER KEY;
GO
 
-- USER [TargetDBServiceUser]를생성합니다.
CREATE USER TargetDBServiceUser WITHOUT LOGIN;
GO
 
-- CERTIFICATE [TargetDBServiceCertPub]의소유자를USER [TargetDBServiceUser]로지정하여생성합니다.
CREATE CERTIFICATE TargetDBServiceCertPub
    AUTHORIZATION TargetDBServiceUser
    FROM FILE = 'C:\SSBCert\TargetDBServiceCertPub.cer';
GO
 
-- REMOTE SERVICE BINDING [TargetBinding]을생성합니다.
CREATE REMOTE SERVICE BINDING TargetBinding
    TO SERVICE 'TargetDBService'
    WITH USER = TargetDBServiceUser;
GO
 
-- ROUTE [TargetDBRoute]를생성합니다.
-- 대화할서비스의이름으로부터, 상대인스턴스의END POINT를찾을때사용합니다.
CREATE ROUTE TargetDBRoute
WITH SERVICE_NAME = 'TargetDBService',
ADDRESS = 'TCP://127.0.0.1:4100';
GO
 
-- InitDB의Service Broker GUID를조회합니다.
-- 조회한값은TargetDB에서ROUTE를생성할때필요합니다.
SELECT service_broker_guid
FROM sys.databases
WHERE name = N'InitDB';
GO

TARGET 인스턴스 설정

/* Section 1. END POINT에대한연결인증방식으로CERTIFICATE를사용하기위한설정*/
USE master;
GO
 
-- CERTIFICATE [TargetDBAuthCertPriv] 생성(private key 포함)
-- Initiator 인스턴스에서Target 인스턴스의END POINT [TargetDBEndpoint]에연결할때,
-- LOGIN [TargetDBProxy]를인증하기위한목적
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'TargetDBPass)(*&^';
GO
 
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'TargetDBPass)(*&^';
 
CREATE CERTIFICATE TargetDBAuthCertPriv
FROM FILE = 'C:\SSBCert\TargetDBAuthCertPub.cer'
WITH PRIVATE KEY (
    FILE = 'C:\SSBCert\TargetDBAuthCertPriv.pvk',
    DECRYPTION BY PASSWORD = '63DD083E-E1CB-44D1-B8AE-357F85D6A26A'
);
 
CLOSE MASTER KEY;
GO
 
-- END POINT [TargetDBEndpoint] 생성
-- TCP 통신을하며4100번포트를listen합니다.
-- CERTIFICATE [TargetDBAuthCertPriv]를사용하여Initiator 인스턴스로부터의연결을인증합니다.
CREATE ENDPOINT TargetDBEndpoint STATE = STARTED
AS TCP (LISTENER_PORT = 4100)
FOR SERVICE_BROKER (AUTHENTICATION = CERTIFICATE TargetDBAuthCertPriv);
GO
 
-- Target 인스턴스의END POINT [TargetDBEndpoint]를통해Initiator 인스턴스로접속할LOGIN [InitDBProxy] 생성
CREATE LOGIN InitDBProxy WITH PASSWORD = 'pass^$%^**(';
GO
 
CREATE USER InitDBProxy;
GO
 
-- CERTIFICATE [InitDBAuthCertPub] 생성(public key only)
-- CERTIFICATE의소유자를USER [InitDBProxy]로지정
CREATE CERTIFICATE InitDBAuthCertPub
AUTHORIZATION InitDBProxy
FROM FILE = 'C:\SSBCert\InitDBAuthCertPub.cer';
GO
 
-- END POINT [TargetDBEndpoint]에대한CONNECT 권한을LOGIN [InitDBProxy]에게부여
-- [InitDBProxy]는CERTIFICATE [InitDBAuthCertPub]에매핑되어있는SQL LOGIN 입니다.
GRANT CONNECT ON ENDPOINT :: TargetDBEndpoint TO InitDBProxy;
GO

/* Section 2. Target 인스턴스설정*/
-- Target 인스턴스에데이터베이스[TargetDB] 생성
USE master;
GO
 
CREATE DATABASE TargetDB;
GO
 
ALTER DATABASE TargetDB SET ENABLE_BROKER;
GO
 
USE TargetDB;
GO
 
-- 미리Queue 활성화SP를더미로생성합니다. (이후에ALTER하여로직을구현합니다)
CREATE PROCEDURE dbo.P_TargetDBQueueActivate
AS
RETURN 0;
GO
 
-- MESSAGE TYPE [MsgToTargetDB], [MsgFromTargetDB], [MsgEndOfStream]을생성합니다.
-- VALIDATION을NONE으로지정하면성능이향상됩니다.
-- VALIDATION이EMPTY이면메시지본문이없다는의미입니다.
-- 동일한MESSAGE TYPE이Initiator와Target에생성되어야합니다.
CREATE MESSAGE TYPE MsgToTargetDB VALIDATION = NONE;
 
CREATE MESSAGE TYPE MsgFromTargetDB VALIDATION = NONE;
 
CREATE MESSAGE TYPE MsgEndOfStream VALIDATION = EMPTY;
 
-- CONTRACT [LoggingContract]를생성합니다.
-- 동일한이름과내용의CONTRACT가Initiator와Target에생성되어야합니다.
CREATE CONTRACT LoggingContract (
    MsgEndOfStream SENT BY INITIATOR,
    MsgToTargetDB SENT BY INITIATOR,
    MsgFromTargetDB SENT BY TARGET
);
 
-- QUEUE [InitDBQueue]를생성합니다.
-- QUEUE 활성화프로시저로[P_TargetDBQueueActivate]를지정합니다.
CREATE QUEUE dbo.TargetDBQueue
WITH ACTIVATION (
    STATUS = ON,
    PROCEDURE_NAME = dbo.P_TargetDBQueueActivate,
    MAX_QUEUE_READERS = 16,
    EXECUTE AS SELF
);
 
-- USER [TargetDBServiceUser]를생성합니다.
CREATE USER TargetDBServiceUser WITHOUT LOGIN;
GO
 
-- SERVICE [TargetDBService]를생성합니다.
--[TargetDBService] 서비스로전송되는모든메시지는QUEUE [TargetDBQueue]에들어갑니다.
CREATE SERVICE TargetDBService ON QUEUE dbo.TargetDBQueue (LoggingContract);
GO
 
-- USER [TargetDBServiceUser]를SERVICE [TargetDBService]의소유자로지정합니다.
ALTER AUTHORIZATION ON SERVICE :: TargetDBService TO TargetDBServiceUser;
GO
 
-- CERTIFICATE [TargetDBServiceCertPriv]의소유자를USER [TargetDBServiceUser]로지정하여생성합니다.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'TargetDBPass)(*&^';
GO
 
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'TargetDBPass)(*&^';
 
CREATE CERTIFICATE TargetDBServiceCertPriv
AUTHORIZATION TargetDBServiceUser
FROM FILE = 'C:\SSBCert\TargetDBServiceCertPub.cer'
WITH PRIVATE KEY (
    FILE = 'C:\SSBCert\TargetDBServiceCertPriv.pvk',
    DECRYPTION BY PASSWORD = '63DD083E-E1CB-44D1-B8AE-357F85D6A26A'
);
 
CLOSE MASTER KEY;
GO
 
-- USER [InitDBServiceUser]를생성합니다.
CREATE USER InitDBServiceUser WITHOUT LOGIN;
GO
 
-- CERTIFICATE [InitDBServiceCertPub]의소유자를USER [InitDBServiceUser]로지정하여생성합니다.
CREATE CERTIFICATE InitDBServiceCertPub
AUTHORIZATION InitDBServiceUser
FROM FILE = 'C:\SSBCert\InitDBServiceCertPub.cer';
GO
 
-- SERVICE [TargetDBService]에대한SEND 권한을USER [InitDBServiceUser]에게부여합니다.
GRANT SEND ON SERVICE :: TargetDBService TO InitDBServiceUser;
GO
 
-- ROUTE [InitDBRoute01]를생성합니다.
-- 대화할서비스의이름으로부터, 상대인스턴스의END POINT를찾을때사용합니다.
-- BROKER_INSTANCE 값은Initiator 인스턴스에서[InitDB]의Service Broker GUID 값을조회하여대입합니다.
CREATE ROUTE InitDBRoute01
WITH SERVICE_NAME = 'InitDBService',
ADDRESS = 'TCP://127.0.0.1:4000',
BROKER_INSTANCE = '8E956A74-3C00-4D7A-BA34-A0B4CAF136ED';
GO

Conversation 을 시작해 봅시다!!

먼저, TARGET 인스턴스에서 아래 스크립트를 실행하여 QUEUE 활성화 프로시져를 수정합니다.

이제 QUEUE 들어오는 메시지는 GameLogs 테이블에 INSERT 될 것입니다.

USE TargetDB;
GO
 
CREATE TABLE dbo.GameLogs (
    LogDate datetime NOT NULL,
    LogCode tinyint NOT NULL,
    LogString nvarchar(100) NULL
);
GO
 
ALTER PROCEDURE dbo.P_TargetDBQueueActivate
AS
DECLARE @message_body xml
    , @response xml
    , @message_type sysname
    , @dialog uniqueidentifier
    , @intDoc int;
 
WHILE (1 = 1)
BEGIN
    BEGIN TRANSACTION;
 
    WAITFOR (
           RECEIVE TOP (1)
               @message_type = message_type_name,
               @message_body = message_body,
               @dialog = conversation_handle
           FROM dbo.TargetDBQueue
    ), TIMEOUT 2000;
 
    IF (@@ROWCOUNT = 0)
    BEGIN
           COMMIT TRANSACTION;
           BREAK;
    END
 
    IF (@message_type = N'http://schemas.microsoft.com/SQL/ServiceBroker/Error')
           END CONVERSATION @dialog;
 
    ELSE IF (@message_type = N'MsgEndOfStream')
           END CONVERSATION @dialog;
 
    ELSE IF (@message_type = N'MsgToTargetDB')
    BEGIN
           EXEC sp_xml_preparedocument @intDoc OUTPUT, @message_body;
 
           INSERT dbo.GameLogs (LogDate, LogCode, LogString)
           SELECT LogDate, LogCode, LogString
           FROM OPENXML (@intDoc, N'/root/GameLog')
           WITH (
               LogDate datetime N'@LogDate',
               LogCode tinyint N'@LogCode',
               LogString nvarchar(100) N'@LogString'
           );
 
           EXEC sp_xml_removedocument @intDoc;
 
           END CONVERSATION @dialog;
    END
 
    COMMIT TRANSACTION;
END
GO

이번에는 INITIATOR 인스턴스의 QUEUE 활성화 프로시저를 아래와 같이 수정합니다.

여기서는 대화를 마치는 역할만 해줍니다.

USE InitDB;
GO
 
/**
version : 1
author : 김도열
e-mail : purumae@eyedentitygames.com
created date : 2011-06-21
description : InitDBQueue의활성화프로시저
return value :
0 = 에러가없습니다.
1 = 트랜잭션을Commit할수없는상태입니다. 트랜잭션을Rollback합니다.
100 = 시스템에러가발생하였습니다. dbo.ErrorLogs 테이블을조회하세요.
**/
 
ALTER PROCEDURE dbo.P_InitDBQueueActivate
AS
SET NOCOUNT ON;
SET XACT_ABORT ON;
 
DECLARE @nvcMessageType sysname
DECLARE @uidDialogHandle uniqueidentifier;
 
WHILE (1 = 1)
BEGIN
    BEGIN TRANSACTION;
 
    WAITFOR (
           RECEIVE TOP (1)
               @nvcMessageType = message_type_name,
               @uidDialogHandle = [conversation_handle]
           FROM dbo.InitDBQueue
    ), TIMEOUT 3000;
 
    IF @@ROWCOUNT = 0
    BEGIN
           COMMIT TRANSACTION;
           BREAK;
    END
 
    IF @nvcMessageType = N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
           END CONVERSATION @uidDialogHandle;
 
    ELSE IF @nvcMessageType = N'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
           END CONVERSATION @uidDialogHandle;
 
    COMMIT TRANSACTION;
END
 
RETURN 0;
GO

이제 INITIATOR 인스턴스에서 메시지를 전송합니다.

DECLARE @uidDialogHandle uniqueidentifier;
DECLARE @xmlMessage xml;
 
SET @xmlMessage = '<root><GameLog LogDate="' + CONVERT(varchar(30), GETDATE(), 121) + '" LogCode="123" LogString="TEST LOG" /></root>';
 
BEGIN DIALOG CONVERSATION @uidDialogHandle
    FROM SERVICE InitDBService
    TO SERVICE 'TargetDBService'
    ON CONTRACT LoggingContract
    WITH ENCRYPTION = OFF;
 
SEND ON CONVERSATION @uidDialogHandle
    MESSAGE TYPE MsgToTargetDB (@xmlMessage);
GO

전송된 메시지가 TARGET 인스턴스에 잘 전달되었는지 확인합니다.

SELECT * FROM dbo.GameLogs;
GO

앞에서도 말씀드렸지만, 주석을 참조하여 소스를 꼼꼼히 읽어 보시기 바랍니다. ^^
SSB의 개체들이 우리 SQLer들에게는 생소하기 때문에 무엇이 필요하고 어떻게 사용해야하는지 한번에 이해하기 어려우니까요!!

어쨌든, 여기까지 SSB로 인스턴스 간에 메시지를 전달하는 기본적인 방법에 대해 알아봤습니다.

그런데.. 이대로 Product 소스에 사용할 수 있을까요? ^^
아마 안될 것 같습니다. 이대로는 성능 문제가 있거든요.

다음 포스트에선 Dialog Pool을 구현하는 방법에 대해 알아보도록 하겠습니다.