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

  Run this script to set up a new database and dialog security on the db

  Any questions etc.: nielsb@develop.com

*/

--Step 1

use master;
go

--create the database
create database [rem_ssb1];
go

use [rem_ssb1];
go

--set master key
create master key
encryption by password = 'hellodb1';

--create certificate for transport security
--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 due to UTC dates.
create certificate DlgCertServ1Db1
with subject = 'DlgCertServ1Db1',
start_date = '06/01/2005'
active for begin_dialog = on;
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 DlgCertServ1Db1 
TO FILE = 'C:\code\ServiceBrokerExamples\certs\DlgCertServ1Db1Pub.cer';
go

--create a user which you eventually will assign a public
--key from the cert in the remote db to
create user remdlguser1
without login;
go

--now is time to go over to server 2 and do similar tasks in the file
--2_setup_objects_server2_db.sql
--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 this local db
--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 DlgCertServ2Db2Pub
--authorization remdlguser1
--from file = 'C:\code\ServiceBrokerExamples\certs\DlgCertServ2Db2Pub.cer';
--go



--you are now done with dialog security setup
--go now to the script file 3_setup_ssb_server1_db.sql

