/*
  Setup script for server 1 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 = 'masterhello1';
go

--create a certificate in master, the public key of this
--cert will be used for authentication purposes 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 TrpCertServ1
with subject = 'TrpCertServer1',
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 2
BACKUP CERTIFICATE TrpCertServ1 
TO FILE = 'C:\code\ServiceBrokerExamples\certs\TrpCertServ1Pub.cer';
go

--you need to create an endpoint in order to enable communication
--outside of this instance
CREATE ENDPOINT SSB1
STATE = STARTED
AS TCP
(
  LISTENER_PORT = 4022
)
FOR SERVICE_BROKER
(
  AUTHENTICATION = CERTIFICATE TrpCertServ1,
  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 remconnlogin1
with password = 'remserver@1';
go

create user remconnuser1
from login remconnlogin1;
go

--grant connect to the endpoint to the login
grant connect on endpoint::ssb1 to remconnlogin1
go

--now is time to go over to server 2 and do similar tasks
--but first make sure that the cert you dumped out above can be
--available for server 2

--Step 2
-- you have now done the similar setup in server 2, 
--and you should now do the final setup in master
--where you create a certificate from server 2's public
--cert and assigns it to the user created above
--uncomment from here to go and execute
--create certificate TrpCertServ2Pub
--authorization remconnuser1
--from file = 'C:\code\ServiceBrokerExamples\certs\TrpCertServ2Pub.cer';
--go



--you are now doe with transport decurity setup
--go now to the script file 2_setup_objects_server1_db.sql

