SQL Server | Kirito Studio's Home

use master;
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'kirito'
GO

CREATE CERTIFICATE HOST_A_cert   
   WITH SUBJECT = 'HOST_A certificate',   
   EXPIRY_DATE = '11/30/2099';  
GO  

CREATE ENDPOINT InstTargetEndpoint
STATE = STARTED
AS TCP ( LISTENER_PORT = 4022 )
FOR SERVICE_BROKER (AUTHENTICATION = CERTIFICATE HOST_A_cert );
GO

BACKUP CERTIFICATE HOST_A_cert TO FILE = 'C:\ssd_shared\tmp\HOST_A_cert.cer';  
GO

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

use master;

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'kirito'

GO

CREATE CERTIFICATE HOST_A_cert

WITH SUBJECT = 'HOST_A certificate',

EXPIRY_DATE = '11/30/2099';

GO

CREATE ENDPOINT InstTargetEndpoint

STATE = STARTED

AS TCP ( LISTENER_PORT = 4022 )

FOR SERVICE_BROKER (AUTHENTICATION = CERTIFICATE HOST_A_cert );

GO

BACKUP CERTIFICATE HOST_A_cert TO FILE = 'C:\ssd_shared\tmp\HOST_A_cert.cer';

GO

USE master;  
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'kirito'
GO

CREATE CERTIFICATE HOST_B_cert   
   WITH SUBJECT = 'HOST_B certificate',   
   EXPIRY_DATE = '11/30/2099';  
GO  

CREATE ENDPOINT InstInitiatorEndpoint
STATE = STARTED
AS TCP ( LISTENER_PORT = 4022 )
FOR SERVICE_BROKER (AUTHENTICATION = CERTIFICATE HOST_B_cert );
GO

BACKUP CERTIFICATE HOST_B_cert TO FILE = 'C:\ssd_shared\tmp\HOST_B_cert.cer';  
GO

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

USE master;

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'kirito'

GO

CREATE CERTIFICATE HOST_B_cert

WITH SUBJECT = 'HOST_B certificate',

EXPIRY_DATE = '11/30/2099';

GO

CREATE ENDPOINT InstInitiatorEndpoint

STATE = STARTED

AS TCP ( LISTENER_PORT = 4022 )

FOR SERVICE_BROKER (AUTHENTICATION = CERTIFICATE HOST_B_cert );

GO

BACKUP CERTIFICATE HOST_B_cert TO FILE = 'C:\ssd_shared\tmp\HOST_B_cert.cer';

GO

USE master;  
CREATE LOGIN HOST_B_login   
   WITH PASSWORD = 'kirito';  
GO  

--SELECT * FROM sys.server_principals  

CREATE USER HOST_B_user FOR LOGIN HOST_B_login;  
GO  

--SELECT * FROM sys.sysusers;  

CREATE CERTIFICATE HOST_B_cert  
   AUTHORIZATION HOST_B_user  
   FROM FILE = 'C:\ssd_shared\tmp\HOST_B_cert.cer'
GO  

--SELECT * FROM sys.certificates  

--select * from sys.endpoints

GRANT CONNECT ON ENDPOINT::InstTargetEndpoint TO [HOST_B_login];  
GO

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

USE master;

CREATE LOGIN HOST_B_login

WITH PASSWORD = 'kirito';

GO

--SELECT * FROM sys.server_principals

CREATE USER HOST_B_user FOR LOGIN HOST_B_login;

GO

--SELECT * FROM sys.sysusers;

CREATE CERTIFICATE HOST_B_cert

AUTHORIZATION HOST_B_user

FROM FILE = 'C:\ssd_shared\tmp\HOST_B_cert.cer'

GO

--SELECT * FROM sys.certificates

--select * from sys.endpoints

GRANT CONNECT ON ENDPOINT::InstTargetEndpoint TO [HOST_B_login];

GO

USE master;  

CREATE LOGIN HOST_A_login WITH PASSWORD = 'kirito';  
GO  

CREATE USER HOST_A_user FOR LOGIN HOST_A_login  
GO  
--drop CERTIFICATE HOST_A_cert
CREATE CERTIFICATE HOST_A_cert  
   AUTHORIZATION HOST_A_user  
   FROM FILE = 'C:\ssd_shared\tmp\HOST_A_cert.cer';  
GO  

--select * from sys.endpoints 
GRANT CONNECT ON ENDPOINT::InstInitiatorEndpoint TO HOST_A_login  
GO

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

USE master;

CREATE LOGIN HOST_A_login WITH PASSWORD = 'kirito';

GO

CREATE USER HOST_A_user FOR LOGIN HOST_A_login

GO

--drop CERTIFICATE HOST_A_cert

CREATE CERTIFICATE HOST_A_cert

AUTHORIZATION HOST_A_user

FROM FILE = 'C:\ssd_shared\tmp\HOST_A_cert.cer';

GO

--select * from sys.endpoints

GRANT CONNECT ON ENDPOINT::InstInitiatorEndpoint TO HOST_A_login

GO

USE master;
GO

CREATE DATABASE InstTargetDB;
GO

USE InstTargetDB;
GO

CREATE MASTER KEY
       ENCRYPTION BY PASSWORD = N'kirito';
GO

CREATE USER TargetUser WITHOUT LOGIN;
GO

--select * from sys.sysusers
--drop certificate InstTargetCertificate
CREATE CERTIFICATE InstTargetCertificate 
     AUTHORIZATION TargetUser
     WITH SUBJECT = 'Target Certificate',
          EXPIRY_DATE = N'12/31/2099';

BACKUP CERTIFICATE InstTargetCertificate
  TO FILE = N'C:\ssd_shared\tmp\InstTargetCertificate.cer';
GO

CREATE MESSAGE TYPE [//BothDB/2InstSample/RequestMessage]
       VALIDATION = WELL_FORMED_XML;
CREATE MESSAGE TYPE [//BothDB/2InstSample/ReplyMessage]
       VALIDATION = WELL_FORMED_XML;
GO

CREATE CONTRACT [//BothDB/2InstSample/SimpleContract]
      ([//BothDB/2InstSample/RequestMessage]
         SENT BY INITIATOR,
       [//BothDB/2InstSample/ReplyMessage]
         SENT BY TARGET
      );
GO

CREATE QUEUE InstTargetQueue;

CREATE SERVICE [//TgtDB/2InstSample/TargetService]
       AUTHORIZATION TargetUser
       ON QUEUE InstTargetQueue
       ([//BothDB/2InstSample/SimpleContract]);
GO

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

USE master;

GO

CREATE DATABASE InstTargetDB;

GO

USE InstTargetDB;

GO

CREATE MASTER KEY

ENCRYPTION BY PASSWORD = N'kirito';

GO

CREATE USER TargetUser WITHOUT LOGIN;

GO

--select * from sys.sysusers

--drop certificate InstTargetCertificate

CREATE CERTIFICATE InstTargetCertificate

AUTHORIZATION TargetUser

WITH SUBJECT = 'Target Certificate',

EXPIRY_DATE = N'12/31/2099';

BACKUP CERTIFICATE InstTargetCertificate

TO FILE = N'C:\ssd_shared\tmp\InstTargetCertificate.cer';

GO

CREATE MESSAGE TYPE [//BothDB/2InstSample/RequestMessage]

VALIDATION = WELL_FORMED_XML;

CREATE MESSAGE TYPE [//BothDB/2InstSample/ReplyMessage]

VALIDATION = WELL_FORMED_XML;

GO

CREATE CONTRACT [//BothDB/2InstSample/SimpleContract]

([//BothDB/2InstSample/RequestMessage]

SENT BY INITIATOR,

[//BothDB/2InstSample/ReplyMessage]

SENT BY TARGET

);

GO

CREATE QUEUE InstTargetQueue;

CREATE SERVICE [//TgtDB/2InstSample/TargetService]

AUTHORIZATION TargetUser

ON QUEUE InstTargetQueue

([//BothDB/2InstSample/SimpleContract]);

GO

USE master;
GO

CREATE DATABASE InstInitiatorDB;
GO
USE InstInitiatorDB;
GO

CREATE MASTER KEY
       ENCRYPTION BY PASSWORD = N'kirito';
GO
CREATE USER InitiatorUser WITHOUT LOGIN;
GO

CREATE CERTIFICATE InstInitiatorCertificate
     AUTHORIZATION InitiatorUser
     WITH SUBJECT = N'Initiator Certificate',
          EXPIRY_DATE = N'12/31/2099';
GO

BACKUP CERTIFICATE InstInitiatorCertificate
  TO FILE = N'C:\ssd_shared\tmp\InstInitiatorCertificate.cer';
GO

CREATE MESSAGE TYPE [//BothDB/2InstSample/RequestMessage]
       VALIDATION = WELL_FORMED_XML;
CREATE MESSAGE TYPE [//BothDB/2InstSample/ReplyMessage]
       VALIDATION = WELL_FORMED_XML;
GO

CREATE CONTRACT [//BothDB/2InstSample/SimpleContract]
      ([//BothDB/2InstSample/RequestMessage]
         SENT BY INITIATOR,
       [//BothDB/2InstSample/ReplyMessage]
         SENT BY TARGET
      );
GO


CREATE QUEUE InstInitiatorQueue;
--drop service [//InstDB/2InstSample/InitiatorService]
CREATE SERVICE [//InstDB/2InstSample/InitiatorService]
       AUTHORIZATION InitiatorUser
       ON QUEUE InstInitiatorQueue
	   ([//BothDB/2InstSample/SimpleContract]);
GO

CREATE USER TargetUser WITHOUT LOGIN;
GO

--drop certificate InstTargetCertificate
CREATE CERTIFICATE InstTargetCertificate 
   AUTHORIZATION TargetUser
   FROM FILE = N'C:\ssd_shared\tmp\InstTargetCertificate.cer'
GO

--select * from sys.certificates

USE InstInitiatorDB;
CREATE ROUTE InstTargetRoute
WITH SERVICE_NAME =
       N'//TgtDB/2InstSample/TargetService',
     ADDRESS = N'TCP://192.168.2.150:4022';
GO

USE msdb;
CREATE ROUTE InstInitiatorRoute
WITH SERVICE_NAME =
       N'//InstDB/2InstSample/InitiatorService',
     ADDRESS = N'LOCAL';
GO

USE InstInitiatorDB;
CREATE REMOTE SERVICE BINDING TargetBinding
      TO SERVICE
         N'//TgtDB/2InstSample/TargetService'
      WITH USER = TargetUser;

GO

GRANT SEND ON SERVICE::[//InstDB/2InstSample/InitiatorService] to TargetUser

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

USE master;

GO

CREATE DATABASE InstInitiatorDB;

GO

USE InstInitiatorDB;

GO

CREATE MASTER KEY

ENCRYPTION BY PASSWORD = N'kirito';

GO

CREATE USER InitiatorUser WITHOUT LOGIN;

GO

CREATE CERTIFICATE InstInitiatorCertificate

AUTHORIZATION InitiatorUser

WITH SUBJECT = N'Initiator Certificate',

EXPIRY_DATE = N'12/31/2099';

GO

BACKUP CERTIFICATE InstInitiatorCertificate

TO FILE = N'C:\ssd_shared\tmp\InstInitiatorCertificate.cer';

GO

CREATE MESSAGE TYPE [//BothDB/2InstSample/RequestMessage]

VALIDATION = WELL_FORMED_XML;

CREATE MESSAGE TYPE [//BothDB/2InstSample/ReplyMessage]

VALIDATION = WELL_FORMED_XML;

GO

CREATE CONTRACT [//BothDB/2InstSample/SimpleContract]

([//BothDB/2InstSample/RequestMessage]

SENT BY INITIATOR,

[//BothDB/2InstSample/ReplyMessage]

SENT BY TARGET

);

GO

CREATE QUEUE InstInitiatorQueue;

--drop service [//InstDB/2InstSample/InitiatorService]

CREATE SERVICE [//InstDB/2InstSample/InitiatorService]

AUTHORIZATION InitiatorUser

ON QUEUE InstInitiatorQueue

([//BothDB/2InstSample/SimpleContract]);

GO

CREATE USER TargetUser WITHOUT LOGIN;

GO

--drop certificate InstTargetCertificate

CREATE CERTIFICATE InstTargetCertificate

AUTHORIZATION TargetUser

FROM FILE = N'C:\ssd_shared\tmp\InstTargetCertificate.cer'

GO

--select * from sys.certificates

USE InstInitiatorDB;

CREATE ROUTE InstTargetRoute

WITH SERVICE_NAME =

N'//TgtDB/2InstSample/TargetService',

ADDRESS = N'TCP://192.168.2.150:4022';

GO

USE msdb;

CREATE ROUTE InstInitiatorRoute

WITH SERVICE_NAME =

N'//InstDB/2InstSample/InitiatorService',

ADDRESS = N'LOCAL';

GO

USE InstInitiatorDB;

CREATE REMOTE SERVICE BINDING TargetBinding

TO SERVICE

N'//TgtDB/2InstSample/TargetService'

WITH USER = TargetUser;

GO

GRANT SEND ON SERVICE::[//InstDB/2InstSample/InitiatorService] to TargetUser

USE InstTargetDB
GO
CREATE USER InitiatorUser WITHOUT LOGIN;
GO

CREATE CERTIFICATE InstInitiatorCertificate
   AUTHORIZATION InitiatorUser
   FROM FILE = N'C:\ssd_shared\tmp\InstInitiatorCertificate.cer';
GO

USE InstTargetDB;
CREATE ROUTE InstInitiatorRoute
WITH SERVICE_NAME = N'//InstDB/2InstSample/InitiatorService',
     ADDRESS = N'TCP://192.168.159.130:4022';


USE msdb;
CREATE ROUTE InstTargetRoute
WITH SERVICE_NAME =
        N'//TgtDB/2InstSample/TargetService',
     ADDRESS = N'LOCAL';

USE InstTargetDB;
GRANT SEND
      ON SERVICE::[//TgtDB/2InstSample/TargetService]
      TO InitiatorUser;
GO

CREATE REMOTE SERVICE BINDING InitiatorBinding
      TO SERVICE N'//InstDB/2InstSample/InitiatorService'
      WITH USER = InitiatorUser;
GO

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

USE InstTargetDB

GO

CREATE USER InitiatorUser WITHOUT LOGIN;

GO

CREATE CERTIFICATE InstInitiatorCertificate

AUTHORIZATION InitiatorUser

FROM FILE = N'C:\ssd_shared\tmp\InstInitiatorCertificate.cer';

GO

USE InstTargetDB;

CREATE ROUTE InstInitiatorRoute

WITH SERVICE_NAME = N'//InstDB/2InstSample/InitiatorService',

ADDRESS = N'TCP://192.168.159.130:4022';

USE msdb;

CREATE ROUTE InstTargetRoute

WITH SERVICE_NAME =

N'//TgtDB/2InstSample/TargetService',

ADDRESS = N'LOCAL';

USE InstTargetDB;

GRANT SEND

ON SERVICE::[//TgtDB/2InstSample/TargetService]

TO InitiatorUser;

GO

CREATE REMOTE SERVICE BINDING InitiatorBinding

TO SERVICE N'//InstDB/2InstSample/InitiatorService'

WITH USER = InitiatorUser;

GO

DECLARE @InitDlgHandle UNIQUEIDENTIFIER;
DECLARE @RequestMsg NVARCHAR(100);

BEGIN TRANSACTION;

BEGIN DIALOG @InitDlgHandle
     FROM SERVICE [//InstDB/2InstSample/InitiatorService]
     TO SERVICE N'//TgtDB/2InstSample/TargetService'
     ON CONTRACT [//BothDB/2InstSample/SimpleContract]
     WITH
         ENCRYPTION = ON;

SELECT @RequestMsg = N'<RequestMsg>Message for Target service.</RequestMsg>';

SEND ON CONVERSATION @InitDlgHandle
     MESSAGE TYPE [//BothDB/2InstSample/RequestMessage]
     (@RequestMsg);

SELECT @RequestMsg AS SentRequestMsg;

COMMIT TRANSACTION;
GO

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

DECLARE @InitDlgHandle UNIQUEIDENTIFIER;

DECLARE @RequestMsg NVARCHAR(100);

BEGIN TRANSACTION;

BEGIN DIALOG @InitDlgHandle

FROM SERVICE [//InstDB/2InstSample/InitiatorService]

TO SERVICE N'//TgtDB/2InstSample/TargetService'

ON CONTRACT [//BothDB/2InstSample/SimpleContract]

WITH

ENCRYPTION = ON;

SELECT @RequestMsg = N'<RequestMsg>Message for Target service.</RequestMsg>';

SEND ON CONVERSATION @InitDlgHandle

MESSAGE TYPE [//BothDB/2InstSample/RequestMessage]

(@RequestMsg);

SELECT @RequestMsg AS SentRequestMsg;

COMMIT TRANSACTION;

GO

DECLARE @RecvReqDlgHandle UNIQUEIDENTIFIER;
DECLARE @RecvReqMsg NVARCHAR(100);
DECLARE @RecvReqMsgName sysname;

BEGIN TRANSACTION;

WAITFOR
( RECEIVE TOP(1)
    @RecvReqDlgHandle = conversation_handle,
    @RecvReqMsg = message_body,
    @RecvReqMsgName = message_type_name
  FROM InstTargetQueue
), TIMEOUT 1000;

SELECT @RecvReqMsg AS ReceivedRequestMsg;

IF @RecvReqMsgName = N'//BothDB/2InstSample/RequestMessage'
BEGIN
     DECLARE @ReplyMsg NVARCHAR(100);
     SELECT @ReplyMsg =
        N'<ReplyMsg>Message for Initiator service.</ReplyMsg>';

     SEND ON CONVERSATION @RecvReqDlgHandle
          MESSAGE TYPE [//BothDB/2InstSample/ReplyMessage]
          (@ReplyMsg);

     END CONVERSATION @RecvReqDlgHandle;
END

SELECT @ReplyMsg AS SentReplyMsg;

COMMIT TRANSACTION;
GO

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

DECLARE @RecvReqDlgHandle UNIQUEIDENTIFIER;

DECLARE @RecvReqMsg NVARCHAR(100);

DECLARE @RecvReqMsgName sysname;

BEGIN TRANSACTION;

WAITFOR

( RECEIVE TOP(1)

@RecvReqDlgHandle = conversation_handle,

@RecvReqMsg = message_body,

@RecvReqMsgName = message_type_name

FROM InstTargetQueue

), TIMEOUT 1000;

SELECT @RecvReqMsg AS ReceivedRequestMsg;

IF @RecvReqMsgName = N'//BothDB/2InstSample/RequestMessage'

BEGIN

DECLARE @ReplyMsg NVARCHAR(100);

SELECT @ReplyMsg =

N'<ReplyMsg>Message for Initiator service.</ReplyMsg>';

SEND ON CONVERSATION @RecvReqDlgHandle

MESSAGE TYPE [//BothDB/2InstSample/ReplyMessage]

(@ReplyMsg);

END CONVERSATION @RecvReqDlgHandle;

END

SELECT @ReplyMsg AS SentReplyMsg;

COMMIT TRANSACTION;

GO

DECLARE @RecvReplyMsg NVARCHAR(100);
DECLARE @RecvReplyDlgHandle UNIQUEIDENTIFIER;

BEGIN TRANSACTION;

WAITFOR
( RECEIVE TOP(1)
    @RecvReplyDlgHandle = conversation_handle,
    @RecvReplyMsg = message_body
  FROM InstInitiatorQueue
), TIMEOUT 1000;

END CONVERSATION @RecvReplyDlgHandle;

-- Display recieved request.
SELECT @RecvReplyMsg AS ReceivedReplyMsg;

COMMIT TRANSACTION;
GO

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

DECLARE @RecvReplyMsg NVARCHAR(100);

DECLARE @RecvReplyDlgHandle UNIQUEIDENTIFIER;

BEGIN TRANSACTION;

WAITFOR

( RECEIVE TOP(1)

@RecvReplyDlgHandle = conversation_handle,

@RecvReplyMsg = message_body

FROM InstInitiatorQueue

), TIMEOUT 1000;

END CONVERSATION @RecvReplyDlgHandle;

-- Display recieved request.

SELECT @RecvReplyMsg AS ReceivedReplyMsg;

COMMIT TRANSACTION;

GO

Service Broker between two machine step by step