Kirito Studio's Home

Service Broker between two machine step by step

2017年10月29日 / kirito / 0 Comments

STEP 1: Create host certificate for login.
HOST A:

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;

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'kirito'

CREATE CERTIFICATE HOST_A_cert

WITH SUBJECT = 'HOST_A certificate',

EXPIRY_DATE = '11/30/2099';

CREATE ENDPOINT InstTargetEndpoint

STATE = STARTED

AS TCP ( LISTENER_PORT = 4022 )

FOR SERVICE_BROKER (AUTHENTICATION = CERTIFICATE HOST_A_cert );

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

HOST B:

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 MASTER KEY ENCRYPTION BY PASSWORD = 'kirito'

CREATE CERTIFICATE HOST_B_cert

WITH SUBJECT = 'HOST_B certificate',

EXPIRY_DATE = '11/30/2099';

CREATE ENDPOINT InstInitiatorEndpoint

STATE = STARTED

AS TCP ( LISTENER_PORT = 4022 )

FOR SERVICE_BROKER (AUTHENTICATION = CERTIFICATE HOST_B_cert );

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

STEP 2: Exchange host certificate
HOST A:

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_B_login

WITH PASSWORD = 'kirito';

--SELECT * FROM sys.server_principals

CREATE USER HOST_B_user FOR LOGIN HOST_B_login;

--SELECT * FROM sys.sysusers;

CREATE CERTIFICATE HOST_B_cert

AUTHORIZATION HOST_B_user

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

--SELECT * FROM sys.certificates

--select * from sys.endpoints

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

HOST B:

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;

CREATE LOGIN HOST_A_login WITH PASSWORD = 'kirito';

CREATE USER HOST_A_user FOR LOGIN HOST_A_login

--drop CERTIFICATE HOST_A_cert

CREATE CERTIFICATE HOST_A_cert

AUTHORIZATION HOST_A_user

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

--select * from sys.endpoints

GRANT CONNECT ON ENDPOINT::InstInitiatorEndpoint TO HOST_A_login

STEP 3: Create Service Broker
HOST A:

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;

CREATE DATABASE InstTargetDB;

USE InstTargetDB;

CREATE MASTER KEY

ENCRYPTION BY PASSWORD = N'kirito';

CREATE USER TargetUser WITHOUT LOGIN;

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

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

VALIDATION = WELL_FORMED_XML;

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

VALIDATION = WELL_FORMED_XML;

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

([//BothDB/2InstSample/RequestMessage]

SENT BY INITIATOR,

[//BothDB/2InstSample/ReplyMessage]

SENT BY TARGET

);

CREATE QUEUE InstTargetQueue;

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

AUTHORIZATION TargetUser

ON QUEUE InstTargetQueue

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

HOST B:

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

CREATE DATABASE InstInitiatorDB;

USE InstInitiatorDB;

CREATE MASTER KEY

ENCRYPTION BY PASSWORD = N'kirito';

CREATE USER InitiatorUser WITHOUT LOGIN;

CREATE CERTIFICATE InstInitiatorCertificate

AUTHORIZATION InitiatorUser

WITH SUBJECT = N'Initiator Certificate',

EXPIRY_DATE = N'12/31/2099';

BACKUP CERTIFICATE InstInitiatorCertificate

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

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

VALIDATION = WELL_FORMED_XML;

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

VALIDATION = WELL_FORMED_XML;

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

([//BothDB/2InstSample/RequestMessage]

SENT BY INITIATOR,

[//BothDB/2InstSample/ReplyMessage]

SENT BY TARGET

);

CREATE QUEUE InstInitiatorQueue;

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

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

AUTHORIZATION InitiatorUser

ON QUEUE InstInitiatorQueue

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

CREATE USER TargetUser WITHOUT LOGIN;

--drop certificate InstTargetCertificate

CREATE CERTIFICATE InstTargetCertificate

AUTHORIZATION TargetUser

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

--select * from sys.certificates

USE InstInitiatorDB;

CREATE ROUTE InstTargetRoute

WITH SERVICE_NAME =

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

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

USE msdb;

CREATE ROUTE InstInitiatorRoute

WITH SERVICE_NAME =

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

ADDRESS = N'LOCAL';

USE InstInitiatorDB;

CREATE REMOTE SERVICE BINDING TargetBinding

TO SERVICE

N'//TgtDB/2InstSample/TargetService'

WITH USER = TargetUser;

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

HOST A:

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

USE InstTargetDB

CREATE USER InitiatorUser WITHOUT LOGIN;

CREATE CERTIFICATE InstInitiatorCertificate

AUTHORIZATION InitiatorUser

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

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;

CREATE REMOTE SERVICE BINDING InitiatorBinding

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

WITH USER = InitiatorUser;

TEST Service Broker:
HOST A:

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

HOST B:

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

HOST A:

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

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;

For more information about Service broker, see service broker help and certificate help

For debugging Service Broker, see ssbdiagnose. Checking Configuration example will help you to find the configuration issue.

BEST Win10 Chinese IME: PIME

2017年3月15日 / kirito / 0 Comments

Download link: https://github.com/osfans/PRIME/releases

LOL IME NOT displayed solution:

Add the following lines to ..\GameData\Apps\LoLTW\Game\DATA\Menu\IMEConfig.xml in ChineseSimplified section:

		<imeName>中州韻輸入法 (PIME)</imeName>
		<displayName>PIME</displayName>
		<Tag>GFxIME_SogouPinyin</Tag>

<imeName>中州韻輸入法 (PIME)</imeName>

<Tag>GFxIME_SogouPinyin</Tag>

VisualStudio error: terminal prompts disabled

2017年3月9日 / kirito / 1 Comment

When we meet the error in visual studio like the following below:
fatal: could not read Username for ‘https://github.com’: terminal prompts disabled

Solution: Open the command line, navigate to the folder and git push origin master. We can find there will be a prompt to ask you to input the github username and password. Just do it!