World of Hyatt Implementation

Opera Application/Database Servers

Table of Contents

Record of Changes1

Introduction2

Hyatt Corporate/Hotel IT Responsibilities2

Deploy SSL Certificates3

Apply ACL Permissions4

Importing the Certificate6

WOH/GP Webservice Configuration11

Troubleshooting14

Record of Changes

Introduction

This document outlines the steps to deploy World of Hyatt (formerly known as Hyatt Gold Passport) in Opera version 5.5.x with Oracle database 11.2.0.4, which includes configuration changes to the WOH/GP Web services interface. Also covered is how to import the Gold Passport SSL Certificate required for communication and assign the correct database privileges. User will need access to the Opera database server to complete the setup. Required passwords include Oracle database SYS user password, OPERA schema password and the Oracle Wallet Password (if using an existing wallet). Troubleshooting steps are provided at the end of the document.

Hyatt Corporate/Hotel IT Responsibilities

The below are requirements prior to implementation

-Administrative access to servers

-Schema/DB Passwords

-Wallet Password

-Up to date SSL certificates

-Network troubleshooting and support

-Windows/Server (non-Oracle software) support

Deploy Hyatt SSL Certificates

Please note that providing the correct certificates is the responsibility of Hyatt IT, however the certificate can be downloaded directly from the WOH endpoint. This step is listed in the troubleshooting section of this document.

Confirm Wallet Path and Database Version:

1. Log onto the database server as local admin.
2. Log into SQLPLUS and connect to OPERA schema

Credentials to be provided by the customer

1. Confirm the Opera default wallet directory with the below script

select o_http_client.get_wallet_directory from dual;

• 1. This is where the WOH SSL certificate needs to be imported on the database server using the Oracle Wallet Manager
  2. Path is usually as shown below, but can vary per site

1. KNOWN ISSUE: If the above query returns a path that begins with double backslashes (d:\\oracle\admin\opera\wallets) then the below solution must be implemented
   1. On the database server remove one of the backslashes from the initopera.ora file located in D:\ORACLE\11204\database (open with notepad)
      1. Remove one of the backslashes so the base wallet path is correct and save (Example: IFILE=D:\\ORACLE\admin\opera\pfile\initOPERA.ora becomes IFILE=D:\ORACLE\admin\opera\pfile\initOPERA.ora)
      2. YOU MUST BOUNCE THE DATABASE for this change to take affect causing site downtime!!! (5- 10 minutes).
         1. Log into SQLPLUS as SYS as SYSDBA
         2. shutdown immediate;
         3. startup;

Apply ACL Permissions for 11G Databases

1. Log into SQLPLUS with sys as sysdba
   1. Run the following scripts in blue italics (steps 2-5)
   2. Variables that are site specific have been highlighted
   3. Assumption that default wallet directory is D:\oracle\admin\OPERA\wallets otherwise that path should be modified in applicable areas of the below scripts
      1. PATH IS CASE SENSITIVE!!!!!
      2. You can find the correct case by opening Explorer and browsing to this location.
   4. Assumption that OPERA and OXI schema names are just standard OPERA and OXI otherwise those values should be modified in applicable areas of the below scripts
2. Assigning the Wallet Path: This path should be the default wallet location determined in the previous section
   1. IMPORTANT: Browse to that location on the DB server to retrieve the exact path as it shows in Windows. It is CASE sensitive, and the SQL query is not always accurate with regard to case.

BEGIN

DBMS_NETWORK_ACL_ADMIN.ASSIGN_WALLET_ACL(

acl => 'open_acl_file.xml',

wallet_path => 'file:D:\ORACLE\admin\OPERA\wallets');

END;

/

1. Assigning Connection Privileges to the Schema(s): Must be run for each schema individually.

BEGIN

DBMS_NETWORK_ACL_ADMIN.add_privilege (

acl => 'open_acl_file.xml',

principal => 'OPERA',

is_grant => TRUE,

privilege => 'connect',

position => NULL,

start_date => SYSTIMESTAMP,

end_date => NULL);

COMMIT;

END;

/

BEGIN

DBMS_NETWORK_ACL_ADMIN.add_privilege (

acl => 'open_acl_file.xml',

principal => 'OXI',

is_grant => TRUE,

privilege => 'connect',

position => NULL,

start_date => SYSTIMESTAMP,

end_date => NULL);

COMMIT;

END;

/

1. Assigning Use Client Cert Privileges to the Schema(s): Must be run for each schema individually

BEGIN

DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(

acl => 'open_acl_file.xml',

principal => 'OPERA',

is_grant => TRUE,

privilege => 'use-client-certificates');

COMMIT;

END;

/

exit

Importing the World of Hyatt SSL Certificate

1. Copy the below certificate file to the default wallet directory on the DB server as defined in previous sections. Only the root certificate should be imported. Importing more than the root certificate may cause certificate errors in Opera. Refer to troubleshooting section 4 for steps to download and verify the certificate if Hyatt has not already provided it.
   1. thawte_primary_root_ca_g3.cer (name of cert is example only, the exact file name and provider is subject to change over time).

1. Open the Oracle Wallet Manager as administrator
   1. Go to Start>All Programs>Oracle-OraDb11g_home1>Integrated Management Tools
      1. Path may differ based on database version and or install
   2. Right Click on Wallet Manager and select “Run as administrator”
      1. If there are multiple versions of the database installed ensure that the Wallet Manager for the correct database version is run. The version can be seen in the Help – About context menu.

1. Open the Opera default wallet
   1. Wallet>Open

1. Click Yes through the below popup

1. Browse to the default Wallet directory and click OK

1. Enter the wallet passwprd
   1. If this continues to fail confirm windows user permissions are set correctly on the ewallet.p12 and cwallet.sso files (described in troubleshooting section at end of document)

1. Once the wallet is open go to Operations>Import Trusted Certificate

1. Choose “Select a file that contains the certificate”

1. Choose the “thawte_Primary_Root_ca_g3.cer” file provided by Hyatt

1. Once all three files have been imported you should see them in the wallet screen below.

1. Ensure Auto Login ins enabled on the wallet

1. Save the wallet and exit the Wallet Manager

WOH Web Service Configuration

• In Opera Application Settings, Activate Parameter General>Subscription Management (needed to configure WOH Web services)

• New WOH web service Configuration- This on a per property basis
  • Application Settings> parameters General>”SUBSCRIPTION MANAGEMENT” must be active to access External Databases configuration below
  • Configure new WOH URL’s and username/password credentials in OPERA>Configuration>Setup>External Databases>Spirit>Web service

• • Configure WOH URL and Username/Pass for all 4 web services :

https://hyatt-gp-pms-ws.hyatt.com/PMSService.svc/Soap12Addr10

• • Confirm UN/PW with Hyatt prior to Rollout!
    • Vendor ID: PMS_OPERA
    • Password: Mc39Cpx4

After adding the URL to each, click on the TEST button. Should return as successful

KNOWN ISSUE

Currently in Opera version 5.5.0.7-5.5.0.16 there is a bug with setting/changing the database webservice username and password. Fixes were released in versions 5.5.0.16.1+/5.5.0.17.1+. This issue does not occur when upgrading or importing a working schema that already contains the login and password values. A workaround exists of setting the username and password directly in the database. Log in to sql*plus as Opera user as outlined at the beginning of the document and run the following two commands exactly as written:

update databases set OPERAUSERNAME='PMS_OPERA' where database_id='SPIRIT’;

update databases set OPERAUSERPASSWORD=’@38@E4574B0B8E34D451’ where database_id='SPIRIT’;

commit;

Troubleshooting

1. When importing a certificate file the below error is received

This is caused by one or more of the certificates already being installed, and is likely to occur for existing sites.

• 1. Error can be ignored if all certificates are confirmed present in the wallet as according to the screen shot in step #10 above

1. When Testing WOH web service the below error is received

a: Solution 1 : Confirm Windows Permissions are correctly assigned to the ewallet.p12 file and cwallet.sso files in the default wallet directory on the database server

• • 1. Both files should have “full control” privileges for “administrators” users and “Read” and Read and Execute” for “SYSTEM” user
    2. If the above does not resolve the error ensure with the customer’s local IT that any service account users are members of the correct user groups

• 1. Solution 2: Confirm ACL Privileges and Path have been correctly assigned
     1. Below query shows ACL Paths (Case sensitive)

select * from dba_wallet_acls;

• • 1. Below Query Shows ACL privileges. Should see “connect” and “cli” = true for both Opera and OXI schemas (principal)

select acl,principal,privilege,is_grant,to_date(end_date,'mm/dd/yy') End_Date from dba_network_acl_privileges order by principal;

• • 1. If these are not shown run the ACL scripts at beginning of document
    2. If multiple similar ACL paths have been added in troubleshooting (e.g. D:\ORACLE\admin\OPERA\wallets and also D:\oracle\admin\opera\wallets) remove the extra entry with the incorrect case. In this instance D:\oracle\admin\opera\wallets is the incorrect case.

BEGIN

DBMS_NETWORK_ACL_ADMIN.UNASSIGN_WALLET_ACL(

acl => 'open_acl_file.xml',

wallet_path => 'file:D:\oracle\admin\opera\wallets');

END;

/

The database will need to be bounced for this fix to take effect.

Receive ORA-20222 Error on Profile Lookup

This error can occur after an upgrade if synonyms are not refreshed. To resolve this open Opera_SMT, log in to the Opera schema and refresh the synonyms for the OXI schema.

1. Receive ORA-28829 error on Profile Lookup

This error can occur if the wrong wallet password is stored in the database. To resolve it run the following command in SQLPLUS:

update oxi.int_parameters set parameter_value='' where parameter_name='WALLET_PASSWORD';

commit;

Log in to OXI and navigate to Interface Configuration, then Interface Parameters, then OXI_GENERIC and ensure ‘Wallet_Password’ is empty.

1. Verify connectivity / download endpoint certificate
   1. Navigate to the endpoint url in Firefox web browser (https://hyatt-gp-pms-ws.hyatt.com/PMSService.svc/Soap12Addr10). Similar steps can be followed in Google Chrome, however this functionality is not available currently in Internet Explorer. Only steps/screenshots for Firefox provided.

KNOWN ISSUE

Some hotels have been known to hardcode the Windows host file World of Hyatt DNS record to an IP address. This should not be done, as the IP addresses used have changed over time and doing so could result in going to an incorrect endpoint.

• 1. Verify the lock symbol is showing as green. Click on the lock symbol to the left of the url, green indicates a valid certificate and red indicates a problem. Click the right arrow, and select more information. In the past, issues where the endpoint certificate showed as invalid during this step was due to incorrect hardcoded DNS configuration pointing this url to a different invalid endpoint.

• 1. Click the right arrow and on the following screen click “More Information”.

• 1. On the security tab click “View Certificate”.
  2. Click details, select the Primary Root certificate and click Export.
  3. Save the certificate to a folder where you can easily find it. It can now be imported into Oracle Wallet Manager. It can also be opened to verify the expiration date.