/*
  Setup script for server 2 database 1 for SSB sample communicating
  between two remote servers

  Run this script to set up transport security on the master db

  Any questions etc.: nielsb@develop.com

*/

--Step 1

use [master];
go

--make sure master had master key
create master key encryption by password = 'masterhello2';
go

--create a certificate in master, the public key of this
--cert will be used for authentication purposes when the
--remote intance tries to connect to this server. Here you
--create a self signed cert. Make sure you set the start date
--to a little while before now(), otherwise you may run into
--troubles when creating the EndPoint below (due to UTC dates)
create certificate TrpCertServ2
with subject = 'Transport Certificate for Server2',
start_date = '06/01/2005';
go

--make sure the cert exist
select * from sys.certificates;
go

--dump out the public key of the cert to a file
--this will then be exchanged with the other instance
--make sure that the path you define below can be accessed
--by sql server. The file needs to be copied over to server 1
BACKUP CERTIFICATE TrpCertServ2 
TO FILE = 'C:\code\ServiceBrokerExamples\certs\TrpCertServ2Pub.cer';
go

--you need to create an endpoint in order to enable communication
--outside of this instance

CREATE ENDPOINT SSB2
STATE = STARTED
AS TCP
(
  LISTENER_PORT = 4022
)
FOR SERVICE_BROKER
(
  AUTHENTICATION = CERTIFICATE TrpCertServ2,
  ENCRYPTION = REQUIRED
);
go

--check that the endpoint has been created
select * from sys.endpoints;
go

--create a login and a user which you eventually will assign a public
--key from the cert in the remote master db to
create login remconnlogin2
with password = '';
go

create user remconnuser2
from login remconnlogin2;
go

--grant connect on the endpoint to the login
grant connect on endpoint::SSB2 to remconnlogin2
go

--copy in the public cert from server 1 to somewhere on this server

--create a certificate from the public cert from server 1
create certificate TrpCertServ1Pub
authorization remconnuser2
from file = 'C:\code\ServiceBrokerExamples\certs\TrpCertServ1Pub.cer';

--go back to server 1 and step 2 in the script 1_setup_sec_master_server1.sql
--make sure the public cert 'TrpCertServ2Pub.cer' is available
--from server 1 

