SAP Analytics Cloud – Live Connections

YouTube Tutorial Video Playlist

SAP Analytics Cloud Live Connection

These tutorial videos were recorded for SAP Analytics Cloud. However, this configuration applies everywhere a Apache Reverse Proxy is required.

End to End

Video Tutorial

In this overview video, we show how easy it is to install and configure Apache on SUSE Linux to act as reverse proxy for HTTPS (SSL).

Next, we show how easy it is to configure SAP HANA to enable the InA service for SAP Analytics Cloud Live Connections.

Environment

As training environment we used SAP HANA, express edition on Google Cloud Platform. You can access the solution on the GCP Marketplace here:

• SAP HANA, express edition (server + applications)

When using other cloud providers like Microsoft Azure or AWS, there will be some minimal differences (e.g. configuring firewalls).

As we rely on the local hosts file on the client computer for name resolution (hxehost => 192.145.34.8), using a fixed external IP address is recommended (but not required).

When using a local HANA express version, (VM or Docker), you will need to have registered SUSE Linux to activate the software repositories. For this, see

• [1.0 SPS 12] SAP HANA Express Edition: Setup, Register VM – SAP HANA Academy

For tutorial videos about SAP HANA, express edition from the SAP HANA Academy, see

• SAP HANA Express (YouTube)

Note that configuring Reverse Proxy using SSL for Apache is almost identical on any platform (e.g Red Hat Linux, macOS, Windows, etc.)

Prerequisites

1. Install Apache

To get started, we install Apache and test client connectivity.

Video Tutorial

[2019.06] SAP Cloud Analytics Live Connections: Apache Reverse Proxy 1 – SAP Digital Enablement

Notes

For production environments, typically a customised Apache is built from source code. For our test and demo environment, we will use the SUSE Linux system tool YaST > Software Management.

# yast sw_single

Alternatively, install Apache from the command line.

# zypper install apache2

In case YaST does not display correctly (e.g. when using PuTTY to connect to SLES), execute the following command and reconnect:

echo 'export NCURSES_NO_UTF8_ACS=1' >> /etc/bash.bashrc.local

For testing, you can add the external IP address and hostname to your local hosts file:

1.2.3.4    hxehost

If everything went well you should get an error message. 😉

HTTP 403 access denied. 

By default Apache on SLES does not allow directory browsing and has no default web page.

Chrome > Developer Tools is a great resource when working with (Apache) web servers.

Documentation

• The Apache HTTP Server  – SLES Administration Guide
• Apache HTTP Server Version 2.4 Documentation

2. Configure Reverse Proxy

After installation, we will enable Apache as a service (not required) and enable the Proxy module.

Video Tutorial

[2019.06] SAP Cloud Analytics Live Connections: Apache Reverse Proxy 2 – SAP Digital Enablement

Enable Apache as a Service

First configure Apache as a service with YaST Services Manager. This is also the interface to start and stop Apache.

# yast services-manager

Alternatively, we can do this on the command line with systemctl.

# systemctl enable apache2

# systemctl start apache2
# systemctl stop apache2

# systemctl reload apache2
# systemctl restart apache2

# systemctl status apache2

When not running Apache on SLES as a service, you can use the Apache command line tool. With the flag -k start|restartl|stop you can manage the server.

# apachectl -h

Documentation

• apachectl – Apache HTTP Server Control Interface – Apache Documentation

Configure Apache

To configure Apache, use YaST or edit the file /etc/sysconfig/apache2. This will update the configuration files in sysconfig,d like loadmodule.conf. For example, adding a module with YaST will automatically generate an new loadmodule.conf file.

Configuration files (*.conf) in the conf.d and vhost.d directories are automatically loaded. Use these directories for customizations.

Do not edit generic conf files as these will be overwritten during upgrades.

Enable (Reverse) Proxy

With YaST, enable the modules

• proxy
• proxy_connect
• proxy_http

Create a file in conf.d with the .conf extension, e.g. proxy.conf. In this example we redirect all traffic to Apache to the Web Dispatcher listening for HANA XS.

It is optional to use variables.

# Define HDB 104.198.52.157:8090
Define HDB hxehost:8090

ProxyPass / https://${HDB}/
ProxyPassReverse / https://${HDB}/

# Example
# ProxyPass /alias https://server.domain/path
# ProxyPassReverse /alias https://server.domain/path

We can now access the HANA XS (and XSA) environment through Apache (default HTTP port 80).

HTTP traffic is sent unencrypted over the network, and should not be used to log on with username and password. For this we need secure HTTPS.

Documentation

• Reverse Proxy Guide – Apache Documentation

3. Configure SSL

Video Tutorial

[2019.06] SAP Cloud Analytics Live Connections: Apache Reverse Proxy 3 – SAP Digital Enablement

Notes

For reverse proxy over HTTPS, we need to enable SSL for Apache. The easiest way to do this on SUSE Linux is using YaST > Network Services > HTTP Server: Server Modules.

# yast http-server

This will update systconfig, which in turn will generate a new loadmodule.conf in /etc/apache2/sysconfig.d to include the LoadModule ssl_module directive.

All we need to do is create a copy of the /etc/apache2/vhost.d/vhost.template file and update the ServerName and the SSLCertificateFile and KeyFile parameters.

To generate private key files and certificates we a can use a script (specific to SLES for Apache mod_ssl)

# /usr/bin/gensslcert

This will create:

• Certificate Authority (CA) root private key (ca.key)
• CA request/certificate (ca.crt)
• Web Server private key (server.key)
• Web Server Certificate Sign Request (server.csr)
• Web Server certificate (server.crt)

These are self-signed certificates but because there is a chain, when we import the CA certificate on the client and indicate that this CA is to be trusted, the web server certificate will be trusted as well.

For Chrome and Safari on macOS, we need to import the CA.crt file with KeyChain; Firefox keeps its own certificate store. For Internet Explorer (and Chrome) on Windows, we use the Certification utility.

Self-signed root certificate with OU = CA.

Signed server certificate Subject OU = web server, issuer OU = CA.

Of course, we are still printing our own money here but this allows us to test the configuration.

The gensslcert script has copied the certificates and keys to the SSL.* directories under /etc/apache2 and a copy of the CA.crt to the DocumentRoot.

To enable a client download of the CA root certificate, we need to change the properties of the file so that process hosting Apache (wwwrun) can access the file.

# chmod o+r /srv/www/htdocs/CA.crt

And allow directory listing (only to download the certificate)

<Directory "/srv/www/htdocs">
  Options +Indexes
  Require all granted
</Directory>

Then simple download the CA.crt (Certificate Authority root certificate) file to your client and add to the certificate store.

4. Configure SSL including Subject Alternative Name

Video Tutorial

[2019.06] SAP Cloud Analytics Live Connections: Apache Reverse Proxy 3 – SAP Digital Enablement

Notes

The certificate generated with gensslcert provides a secure connection for the Internet Explorer, Firefox, and Safari browsers but not for Chrome. As Chrome is the recommended browser for SAP Analytics cloud, we need to update our certificate with a Subject Alternative Name.

For Chrome 58 and later, only the subjectAlternativeName extension, not commonName, is used to match the domain name and site certificate. For the issue, see Error: “Subject Alternative Name Missing” or NET::ERR_CERT_COMMON_NAME_INVALID or “Your connection is not private”.

Perform the following tasks:

• Create a private key for the CA role
• Create a CA request / certificate with this private key
• Create a private key for the web server
• Create a certificate sign request (CSR) for the web server
• Create the web server certificate with CSR, CA key, and CA certificate

Generate the CA private key.

openssl genrsa -rand /var/log/y2log:/var/log/messages \
        -out /etc/apache2/ssl.key/ca.key 2048

Create a configuration file for the certificate. Below an example. The Common Name (CN) value needs to correspond to URL for the web server. In other words CN = hxehost.localdomain corresponds to https://hxehost.localdomain. The values are not parsed by the browser as we have seen in video 3. C = XY is fine. The gensslcert script uses OU=CA for the CA certificate (ca.crt) and OU=web server for the server certificate (server.crt) for informational purposes.

cat >/root/.mkcert.cfg <<EOT
[ req ]
default_bits  = 2048
default_keyfile  = keyfile.pem
distinguished_name = req_distinguished_name
attributes  = req_attributes
prompt  = no
output_password = mypass

[ req_distinguished_name ]
C  = DE
ST = Baden-Wuerttemberg
L  = Walldorf
O  = SAP
OU  = Digital Partner Engineering
CN  = hxehost.localdomain
emailAddress  = digitalenablement@sap.com

[ req_attributes ]
challengePassword  = 1234
EOT

Generate a CA root authority certificate valid for 1 year using the CA private key.

openssl req -new -x509 -days 365 \
        -config /root/.mkcert.cfg \
        -key /etc/apache2/ssl.key/ca.key \
        -out /etc/apache2/ssl.crt/ca.crt 

Generate the (web) server private key (same command as for the CA, except file name)

openssl genrsa -rand /etc/rc.config:/var/log/messages \
        -out /etc/apache2/ssl.key/server.key 2048

Generate the certificate server request (CSR) using the server private key.

openssl req -new \
        -config /root/.mkcert.cfg \
        -key /etc/apache2/ssl.key/server.key \
        -out /etc/apache2/ssl.csr/server.csr

Create extension file for the server certificate. Note the subjectAltName.

cat >/root/.mkcert.cfg <<EOT
extensions = x509v3
[ x509v3 ]
subjectAltName   = email:copy
nsComment        = SAP Digital Partner Engineering
nsCertType       = server

authorityKeyIdentifier=keyid,issuer
basicConstraints=CA:FALSE
keyUsage = digitalSignature, nonRepudiation, keyEncipherment, dataEncipherment
subjectAltName = @alt_names
[alt_names]
DNS.1 = *.localdomain
DNS.2 = hxehost.localdomain
EOT

Generate the server certificate using the extension file, taking in CA key and CA certificate, and server key.

openssl x509 -req -days 365 \
        -extfile /root/.mkcert.cfg \
        -CAserial /root/.mkcert.serial \
        -CA /etc/apache2/ssl.crt/ca.crt \
        -CAkey /etc/apache2/ssl.key/ca.key \
        -in /etc/apache2/ssl.csr/server.csr  \
        -out /etc/apache2/ssl.crt/server.crt 

You may need to create (or update) the serial.

test -f /root/.mkcert.serial || echo 01 >/root/.mkcert.serial

Appending DH parameters (see https://wiki.openssl.org/index.php/Diffie_Hellman).

openssl dhparam 2048  >> /etc/apache2/ssl.crt/server.crt

Sample SSL Proxy Reverse configuration file, /etc/apache2/vhosts.d/hxehost-ssl.conf

<IfDefine SSL>
<IfDefine !NOSSL>
<VirtualHost _default_:443>
        ServerAdmin digitalenablement@sap.com
        ServerName hxehost.localdomain
        DocumentRoot /srv/www/htdocs
        ErrorLog /var/log/apache2/error_log
        TransferLog /var/log/apache2/access_log
        CustomLog /var/log/apache2/ssl_request_log   ssl_combined
        ProxyPreserveHost on 
        SSLEngine on
        SSLProxyEngine on
        SSLCertificateFile /etc/apache2/ssl.crt/server.crt
        SSLCertificateKeyFile /etc/apache2/ssl.key/server.key
        <Location />
           ProxyPass https://hxehost.localdomain:4390/ 
           ProxyPassReverse https://hxehost.localdomain:4390/ 
        </Location>
</VirtualHost>
</IfDefine>
</IfDefine>

Optionally, make this certificate available for download to the client (copy / paste using cat on the server and Notepad/TextEdit also works).

cp -pv /etc/apache2/ssl.crt/ca.crt  /srv/www/htdocs/CA.crt
chmod o+r /srv/www/htdocs/CA.crt

Add the following section to the virtual host configuration file (default is 403), e.g. /etc/apache2/vhosts.d/hxehost-ssl.conf

<Directory "/srv/www/htdocs">
  Options +Indexes
  Require all granted
</Directory>

5. Configure InA Service

Tutorial Video

Server Time Out

Change the session timeout for the Web Dispatcher (as documented)

hdbsql -i 90 -d SYSTEMDB -u system 
"ALTER SYSTEM ALTER CONFIGURATION ('xsengine.ini', 'database', 'HXE') 
SET ('httpserver', 'sessiontimeout') ='43200' 
WITH RECONFIGURE"

Create InA User

Create a user with the INA_USER role. The example below uses INA_USER but you can use any name you want.

You can create a user store key to avoid entering connection information on the command line:

hdbuserstore -i set HXESYSTEMKEY hxehost:39015@HXE SYSTEM

hdbsql -i 90 -U HXESYSTEMKEY -m <<EOF
CREATE USER ina_user PASSWORD Initial1 NO FORCE_FIRST_PASSWORD_CHANGE;
CALL GRANT_ACTIVATED_ROLE('sap.bc.ina.service.v2.userRole::INA_USER','INA_USER');
EOF

Create XS Admin User

In addition, create a user for XS runtime configuration. The example below uses XS_ADMIN but this can any name you want; alternatively you can grant the roles to an existing users.

hdbsql -i 90 -U HXESYSTEMKEY -m <<EOF
CREATE USER xs_admin PASSWORD  Initial1 NO FORCE_FIRST_PASSWORD_CHANGE;
CALL GRANT_ACTIVATED_ROLE('sap.hana.xs.admin.roles::RuntimeConfAdministrator','XS_ADMIN');
CALL GRANT_ACTIVATED_ROLE('sap.hana.xs.admin.roles::SAMLViewer','XS_ADMIN');
EOF

CORS

Configure the runtime configuration for the SAP.BC.INA.SERVICE.V2 package in the XS Admin tool (connect as XS_ADMIN):

• https://hxehost.localdomain/sap/hana/xs/admin

Reset the configuration in the XS Admin Tool and run the script:

cat > CORS.sql <<EOT
UPDATE "_SYS_XS"."RUNTIME_CONFIGURATION"
SET "CONFIGURATION" = ' {"cors":{
  "enabled":true,
  "allowOrigin":["https://mytenant.myregion.sapanalytics.cloud"],
  "exposeHeaders":["x-csrf-token"],
  "allowHeaders":["accept-language","x-sap-cid","x-request-with","x-csrf-token","content-type","authorization","accept"],
  "allowMethods":["GET","HEAD","POST","OPTIONS"],
  "maxAge":3600}
}'
WHERE "PACKAGE_ID" = 'sap.bc.ina.service.v2';
EOT

Connect with XS_ADMIN user to the tenant database:

hdbsql -i 90 -d HXE -u XS_ADMIN -p Initial1 -m -I CORS.sql

Apache

Add the Header parameters to the virtual host configuration file. Using a variable, e.g. SAC, is advisable for multiple connections.

Define SAC mytenant.myregion.sapanalytics.cloud"

<If "req_novary('ORIGIN') == 'https://${SAC}'">
Header set Access-Control-Allow-Origin "https://${SAC}"
Header set Access-Control-Allow-Credentials true
Header set Access-Control-Allow-Methods "GET, POST, PUT"
Header set Access-Control-Allow-Headers "X-Csrf-Token, x-csrf-token, x-sap-cid, Content-Type, Authorization"
Header set Access-Control-Expose-Headers "x-csrf-token"
</If>

Create Web Dispatcher Admin User and Configure Public URL (Optional)

Create a user for Web Dispatcher administration (or grant the roles to an existing user) in case you want to change the existing configuration, e.g. to install a valid certificate.

This is only required if you want to make changes to the default self-signed server certificate of the web dispatcher. When using Apache Reverse Proxy, this is not required.

hdbsql -i 90 -U HXESYSTEMKEY -m <<EOF
CREATE USER wdisp_admin PASSWORD Initial1 NO FORCE_FIRST_PASSWORD_CHANGE ;
CALL GRANT_ACTIVATED_ROLE('sap.hana.xs.wdisp.admin::WebDispatcherAdmin','WDISP_ADMIN');
EOF

In this case, you need to configure the public URL to point to the tenant to access the Web Dispatcher:

• https://hxehost.localdomain/sap/hana/wdisp/admin

hdbsql -i 90 -d SYSTEMDB -u system 
"ALTER SYSTEM ALTER CONFIGURATION ('xsengine.ini', 'database', 'HXE') 
SET ('public_urls', 'https_url') = 'https://hxehost.localdomain:4390' 
WITH RECONFIGURE;"

Documentation

• SAP Analytics Cloud – Live Connection to SAP HANA using Direct (CORS)

Additional Documentation

• Setting Up a Secure Web Server with SSL – SUSE Linux Enterprise Server 12 SP4 Administration Guide
• The Apache HTTP Server  – SLES Administration Guide
• Apache HTTP Server Version 2.4 Documentation

Thank you for watching

The SAP HANA Academy provides technical enablement, implementation and adoption support for customers and partners with 1000’s of free tutorial videos.

For the full library, see SAP HANA Academy Library – by the SAP HANA Academy

For the full list of blogs, see Blog Posts – by the SAP HANA Academy

• Subscribe to our YouTube channel for updates
• Join us on LinkedIn linkedin.com/in/saphanaacademy
• Follow us on Twitter @saphanaacademy
• Facebook facebook.com/saphanaacademy