Connecting to SAP HANA as a Service using websockets

SAP HANA as a Service (HaaS) is a fully managed translytical database service. There are existing resources on how to provision it,  connect using a direct TCP connection or install the CommonCryptoLib.

However, corporate firewall often block outgoing TCP connections, so another connection option must be used. That’s where the websocket protocol comes in to save the day ! If outgoing TCP connections are blocked, but it is possible to browse the web via a proxy, then you could connect using the websocket protocol. A websocket connection begins just like a regular HTTP connection but after being established, tadaaa, it gracefully morphs into a TCP connection over the proxy.

Now the not so fun part was to setup certificate for the mandatory network traffic encryption on my mac. Hopefully those commands will speed things up for you.

Prepare your certificate store

cd
mkdir .ssl
chmod 700 .ssl
cd .ssl

Fetch the root certificate used by SAP to encrypt traffic.

curl https://dl.cacerts.digicert.com/DigiCertGlobalRootCA.crt > DigiCertGlobalRootCA.crt

Then convert it

openssl x509 -inform der -in DigiCertGlobalRootCA.crt -out DigiCertGlobalRootCA.pem

To test the connection, I’ll use python. If you haven’t done so already, install HANA Client 2 SP 3 and install the drivers for python 2 and 3.

sudo easy_install-3.7 ./hdbcli-2.3.134.tar.gz
sudo easy_install ./hdbcli-2.3.134.tar.gz

Now open your instance dashboard and copy paste the instance ID and the websocket URL

Here’s an sample program, replace all the many variables !

from hdbcli import dbapi

conn = dbapi.connect(
                address='<Websocket URL>',
                port=80,
                user='SYSTEM',
                password='<Password>',
                webSocketURL='/service/<instance ID>',
                encrypt='true',
                sslCryptoProvider='openssl',
                sslTrustStore='/Users/<your user>/.ssl/DigiCertGlobalRootCA.pem'
                ,proxy_host='<your proxy host>',
                proxy_port='<your proxy port>'
)

print("Connected") if conn.isconnected() else print("Not connected")

cursor = conn.cursor()

cursor.execute("select * from DUMMY")

for row in cursor:
    print(row)

cursor.close()
conn.close()