SAP HANA Express VM Getting Started

This page contains instructions on how to set up SAP HANA Express Edition virtual machine for development and get started with the SAP training material.

Applies to SAP HANA Express Edition 2.0 SPS03 and SPS03

Most important step in order to prevent build errors afterwards is to Activate Tenant Database

Prerequisites

Install SAP Java VM for Windows

Download SAPJVM from: https://tools.hana.ondemand.com/#cloud-sapjvm

Extract it to C:\Program Files\sapjvm_8

Go to Windows System Environment Variables and create a new environment variable:

• Name: JAVA_HOME
• Value: C:\Program Files\sapjvm_8\bin

Download HXEDownloadManager

Register at https://www.sap.com/cmp/td/sap-hana-express-edition.html and download HXEDownloadManager and start it.

Follow instructions in the provided Getting_Started_HANAexpress_VM.pdf

Setup HANA Environment

Install Server's SSL Certificate

In order to prevent security warnings in the Chrome browser when logging in into Web IDE, XS Advanced Cockpit or HANA Cockpit the servers self signed ssl certificate has to be installed in the local trusted root certificate authority storage.

The servers self signed ssl certificate default.root.crt.pem can be found at:

<installation_path>/<SID>/xs/controller_data/controller/ssl-pub/router

For example, where <installation_path> is /hana/shared and <SID> is HXE the certificate location would be:

/hana/shared/HXE/xs/controller_data/controller/ssl-pub/router/default.root.crt.pem

Download default.root.crt.pem from the virtual machine to the local machine, i.e. with WinSCP or SCP.

Install the certificate to the trusted root certificate authorities store in Chrome:

• Open Settings
• Open section Security and click on Manage certificates
• Click on import and follow the assistant until storage selection for the certificate
• Select trusted root certificate authority as storage for the certificate
• Finish assistant

After that now security warnings should disappear when logging in next time. Perhaps restarting Chrome browser is required.

Activate Tenant Database

• Login to SAP HANA XS Advanced Cockpit, probably at https://hxehost:51036/cockpit#/xsa/overview
• Go to Tenant Databases
• For Tenant Database HXE Click on Button Activate -> Enter Master Password
• Map Database to Organization HANAExpress and Space development. Activate default checkbox
• Click Save

Database Explorer

Find database SQL port numbers starting from the system database:

SELECT 
  DATABASE_NAME, 
  SERVICE_NAME, 
  PORT, 
  SQL_PORT, 
  (PORT + 2) HTTP_PORT 
FROM SYS_DATABASES.M_SERVICES 
WHERE DATABASE_NAME='<DBNAME>' 
  and ((SERVICE_NAME='indexserver' and COORDINATOR_TYPE= 'MASTER') 
  or (SERVICE_NAME='xsengine'))

Source: https://help.sap.com/viewer/6b94445c94ae495c83a19646e7c3fd56/2.0.04/en-US/440f6efe693d4b82ade2d8b182eb1efb.html

Add System Database to Database Explorer

• Login to SAP Web IDE (Use XSA_DEV)
• Go to Database Explorer and click on "+"
• Select Database Type SAP HANA Database (Multitenant)
• Enter values as follows:
  • Host: localhost or server name
  • Instance Number: 90
  • Port Number: 39013
  • Database: System Database
  • User: System
  • Passwort: Master Password
• Click on OK

Add Tenant Database

• Login to SAP Web IDE (Use XSA_DEV)
• Go to Database Explorer and click on "+"
• Select Database Type SAP HANA Database (Multitenant)
• Enter values as follows:
  • Host: localhost or server name
  • Instance Number: 90
  • Port Number: 39015
  • Database: Tenant Database HXE
  • User: System
  • Passwort: Master Password
• Click on OK

Web IDE

Clone Project

• Clone from desired repository
• Open Project Settings -> Open section Space
• Set space to development
• Click on Save

Install Demodata

SFLIGHT

• Download https://github.com/SAP/hana-xsa-opensap-hana7/raw/snippets_2.3.2/ex2/sflight_hana.tar.gz
• Open Web IDE
• Go to Database Explorer
• Open System Database
• Right-Click on Catalog and select Import Catalog Objects
• Enter values as follows:
  • Import objects from -> local computer
  • Local archive (*.tar.gz) -> Select file downloaded above
  • Activate Include dependencies and Include table data
  • Number of parallel threads -> 2
• Click on OK

SQL Commands in Tenant (Needed for Exercise 2.6):

CREATE USER CUPS_SFLIGHT PASSWORD "HanaRocks01" SET PARAMETER CLIENT = '001';
ALTER USER CUPS_SFLIGHT DISABLE PASSWORD LIFETIME;
GRANT SELECT ON SCHEMA SFLIGHT TO CUPS_SFLIGHT WITH GRANT OPTION;
GRANT SELECT METADATA ON SCHEMA SFLIGHT to CUPS_SFLIGHT WITH GRANT OPTION;

CREATE ROLE SFLIGHT_CONTAINER_ACCESS;
GRANT SELECT, SELECT METADATA ON SCHEMA SFLIGHT TO SFLIGHT_CONTAINER_ACCESS WITH GRANT OPTION;
GRANT SFLIGHT_CONTAINER_ACCESS TO CUPS_SFLIGHT WITH ADMIN OPTION;

Source: https://blogs.sap.com/2018/12/18/howto-import-sflight-sample-data-into-sap-hana-from-a-local-computer/

Central DB

Install CentralDB MTAR (Needed for Exercise 2.8): In this exercise, we will configure access and synonyms between our local project container and an already existing container deployed on this system. In order to perform this exercise in your own system, you will have to first download and deploy (with the –use-namespaces option) this MTA into your development space from: https://github.com/SAP/hana-xsa-opensap-hana7/raw/snippets_2.3.2/ex2/openSAP.CentralDB_1.1.2019.mtar

xs deploy help: https://help.sap.com/viewer/4505d0bdaf4948449b7f7379d24d0f0d/2.0.03/en-US/308f2041995c41a88bbb197cf2a984f7.html#loio308f2041995c41a88bbb197cf2a984f7__section_irt_3dc_zs

xs deploy openSAP.CentralDB_1.0.2019.mtar --use-namespaces

Install HANA7 openSAPHANA Training Application

The following statements are based on my implementation of the HANA7 exercises. Repository:

https://github.com/mattxdev/opensap-hana7

This repository is based on the original HANA7 repository from SAP located at:

https://github.com/SAP-samples/hana-xsa-opensap-hana7

Create User Provided Services

XSA Commands:

xs cups CROSS_SCHEMA_SFLIGHT_00 -p "{\"host\":\"localhost\",\"port\":\"39015\",\"user\":\"CUPS_SFLIGHT\",\"password\":\"HanaRocks01\",\"driver\":\"com.sap.db.jdbc.Driver\",\"tags\":[\"hana\"] , \"schema\" : \"SFLIGHT\" }"

xs cups sap.hana.democontent.epm.services.images.00 -p "{\"host\":\"www.loc.gov\",\"port\":\"80\",\"tags\":[\"xshttpdest\"] }"

Create Additional Services

xs create-service xsuaa space openSAPHANA_00-uaa -c xs-security.json
xs create-service auditlog free openSAP-ex-log
xs create-service sapui5_sb sapui5-1.52 openSAPHANA_00-ui5

Create XS Advanced Roles

xs create-role openSAPHANA-00 Viewer Viewer_300 -a "{\"client\":{\"value\":\"300\",\"source\":\"static\"},\"country\":{\"value\":\"US\",\"source\":\"static\"}}"

xs create-role openSAPHANA-00 Editor Editor_300 -a "{\"client\":{\"value\":\"300\",\"source\":\"static\"}}"

Build DB sequence

The databases modules of the training application have to be build in the following order:

1. user-db
2. Project -> Build CDS
3. db (db-hdi-container)

After that there is a procedure called initTestUsers which creates a specified number of test records in the UserData.User table.

Run Sequence

Run XSJS as Node.js Application Run SRV as Node.js Application Run WEB as Web Application

Trouble Shooting

See also https://developers.sap.com/tutorials/hxe-ua-troubleshooting.html

Build of DB fails with invalid user name

Error Message

Using grantor service "ServiceName_1" of type "sql" Error: Error executing: GRANT "SFLIGHT_CONTAINER_ACCESS" TO "OPENSAPHANA_OPENSAPHANA_HDI_CONTAINER_1#OO"; (nested message: invalid user name: OPENSAPHANA_OPENSAPHANA_HDI_CONTAINER_1#OO: line 1 col 37 (at pos 36)) grantor service: "ServiceName_1", type: "sql", user: "CUPS_SFLIGHT" file name: cfg/SFLIGHT.hdbgrants

Root Cause

User name OPENSAPHANA_OPENSAPHANA_HDI_CONTAINER_1#OO was created in a different tenant database, i.e. SYSTEM instead of HXE.

The HXE tenant database has to be activated and mapped as default in the VM first. If this is not the case any created projects will create users in SYSTEM database instead of the development tenant database.

Fix

No fix known besides to dropping the VM entirely and setup again and following the steps above Setup Environment

Build of DB fails with invalid role name

Error Message

20:27:30 (DIBuild) Processing "cfg/central.hdbgrants"... ok Processing "cfg/user.hdbgrants"... Using grantor service "hdi-user-service" of type "hdi" Granting schema roles in the container "OPENSAP_HANA_USER_00_1" and the parameters "[]"... Granting the schema role "user#" to the principal "OPENSAPHANA_OPENSAPHANA_DB_HDI_CONTAINER_1#OO" in the schema "" with grant option = "false" Error: Database error 389: : invalid role name: user#: line 1 col 7 (at pos 6) [8201003] Error: Granting schema roles in the container "OPENSAP_HANA_USER_00_1" and the parameters "[]"... failed [8214221]Error: Error executing: CALL "OPENSAP_HANA_USER_00_1#DI".GRANT_CONTAINER_SCHEMA_ROLES(#CONTAINER_ROLES, #CONTAINER_ROLES_PARAMETERS, ?, ?, ?); (nested message: HDI call failed)grantor service: "hdi-user-service", type: "hdi", user: "OPENSAP_HANA_USER_00_1_BVB0FJGXOQE559VFZERVZC3S1_DT" (hdi_user)file name: cfg/user.hdbgrants

Root Cause

Missing User DB.

Fix

Build User DB first.

Incorrect Login When Logging in to Web IDE

Certificates can cause trouble. Renew the self-signed server side certificates with:

XSA reset-certificate

Source: https://answers.sap.com/questions/191748/xs-advanced-internal-server-error.html?page=2&pageSize=10&sort=votes

Internal Server Error After Login to Web IDE

Error Message

Internal Server Error

Root Cause

Server side certificate has expired

Fix

Login to server with ssh and execute (case sensitive):

XSA reset-certificate

Memory Shortage / HANA Express Garbage Collection

If you are short on memory perhaps the HANA garbage collection can help you out:

cd /usr/sap/HXE/home/bin
./hxe_gc.sh

Source: https://developers.sap.com/tutorials/hxe-ua-troubleshooting.html#320c1b50-9dec-4c5a-91a1-72917e5f0a4c