Skip to Content

The SAP HANA Service is now available, and there have been some questions about how to connect to a HANA database in the cloud from a local SQL client. It’s a bit complicated because all connections to HANA Service must be encrypted. In this post I’ll go through some of the common scenarios. You don’t have to read it all: just find the bits you need.

Note: if you are building an XSA or Cloud Foundry application that binds your application to a HANA service broker you can stop reading here. The service broker provides you with a logical connection and you don’t need to do anything else. This blog is intended only for standalone clients using SQL client/server connections.

The sequence of scenarios is:

  • TCP/IP connections without certificate validation (for testing)
  • TCP/IP connections for production use
  • WebSocket connections, for use in organizations that block outgoing TCP/IP connections.

Thanks go to Bjoern Brencher, Akshay Nayak, Tom Turchioe for contributing.

For a more in-depth treatment of how to connect using the SAP Common Crypto library, I recommend Philip Mugglestone’s clear and detailed step-by-step video at the SAP HANA Academy YouTube channel.

Let’s start with the identifiers you need. The SAP HANA Service Dashboard shows the basic information about ta HANA database. In particular, notice the endpoint and the ID.

– The endpoint is the host and port you need for TCP/IP connections. It is usually of the form zeus.hana.prod….ondemand.com:port and here I’ll write it as zeus.hana…ondemand.com:port.
– The ID of the database is a GUID, such as 45fx7b7a7-a2d9-ad49-84ab-89106146b944.

In addition you need to know the user ID and password. I’ll indicate these by HANA_USER and hana_password.

I’l walk through three cases, each of which have other cases included. To start with, I’ll look at TCP/IP connections that do not require specification of a local certificate (test connections and connections on Windows). Then I’ll show how you can use a WebSocket to connect. And then I’ll walk through how to manage certificates for properly secure connections. One word of warning: I’m no security expert and I sometimes get the names wrong for certificates, keys, and so on. This is a blog post to meet a short-term need, and not official documentation!

In each case I’ll show an hdbsql command to start with, and then show a connection example from another interface to show how the keywords work.

To use a WebSocket, you’ll need a version of the HANA Clients at least 2.3.106. It’s downloadable from https://tools.hana.ondemand.com/#hanatools and from SAP Software Downloads.

First steps: TCP/IP connections without certificate validation

Here is a connection from hdbsql using TCP/IP. It shows where the endpoint appears, and that you need to specify an encrypted connection. The example is split over several lines for readability.

> hdbsql -n zeus.hana....ondemand.com:20058 \
-u HANA_USER \
-p hana_password \
-e \
-ssltrustcert

The -n option specifies the host and port, -u and -p are for the user name and password, and the -e option specifies an encrypted connection. The -ssltrustcert option skips the validation of the host certificate and is not recommended for production use. Here we just use it for testing purposes, to verify that we have the other connection parameters specified properly.

If you are using Windows, the HANA Client uses the Microsoft encryption library by default, and the Windows certificate store contains, by default, the certificate needed to verify the HANA Service key, so for Windows users you can use the following both for testing and in production:

> hdbsql -n zeus.hana....ondemand.com:20058 \
-u HANA_USER \
-p hana_password \
-e

For the programming interfaces, just use the keywords corresponding to each of the hdbsql options, which are all documented in the HANA Client Interface Programming Reference. Here is a node.js example:

var hana = require('@sap/hana-client');

var conn = hana.createConnection();

var conn_parms_tcp_test = {
    serverNode : "zeus.hana....ondemand.com:port",
    encrypt : true,
    sslValidateCertificate: false,
    uid : "HANA_USER",
    pwd : "hana-password"
};

conn.connect(conn_parms_tcp_test, function(err) {
    if (err) throw err;
    conn.exec("SELECT DATABASE_NAME FROM M_DATABASES", function(err, result) {
        if (err) throw err;
        console.log("Database name", result[0].DATABASE_NAME);
        conn.disconnect();
     })
});

And here is a python example. Notice that the values for the **encrypt** and **sslValidateCertificate** keywords are strings, not boolean values.

from hdbcli import dbapi
conn = dbapi.connect(
    address="zeus.hana...dbaas.ondemand.com",
    port=port,
    user="HANA_USER",
    password="hana_password",
    encrypt='true',
    sslValidateCertificate='false'
)
print("Connected") if conn.isconnected() else print("Not connected") 
conn.close()

Again, if you are on Windows and the application has access to the certificate store, you can leave out the sslValidateCertificate keyword, and the client will look in the Microsoft certificate store to find the right certificate.

Managing certificates

SAP Cloud Platform uses Digicert certificates. If your certificate store does not have the correct certificate, you may need to download it and store it in a file. Here are two ways to get an appropriate certificate.

Downloading a certificate from Digicert

You can get Digicert certificates from https://www.digicert.com/digicert-root-certificates.htm. The one to use is the DigiCert Global Root CA, Serial #: 08:3B:E0:56:90:42:46:B1:A1:75:6A:C9:59:91:C7:4A, Thumbprint: A8985D3A65E5E5C4B2D7D66D40C6DD2FB19C5436.

If you are using the OpenSSL encryption library, which is default on Linux and Mac OS, you need to convert this file to a “pem” format which the client can use. Copy the file DigiCertGlobalRootCA.crt to ~/.ssl and then run this command to generate a pem format file (split across multiple lines for readability):

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

Obtaining a certificate from the SAP Cloud Platform Cockpit

Alternatively, you can get a certificate in the proper “PEM” format from the SAP Cloud Platform Cockpit. The CA Digicert certificate is stored in the Cloud Foundry service binding for usage in CF applications. You can see the certificate here: just select the text for the certificate itself (the whole string) and store it in a file: say ~/.ssl/DigiCertGlobalRootCA.pem. Newline characters can be left in or deleted; it doesn’t make any difference.


Connecting, using a certificate to validate the server

Here is an hdbsql connection string that uses this certificate:

> hdbsql -n zeus.hana...ondemand.com:20058 \
-u HANA_USER -p hana_password \
-e -sslprovider openssl \
-ssltruststore ~/.ssl/DigiCertGlobalRootCA.pem

And here are the keywords in a node.js connection method:

var conn_parms_tcp = {
    serverNode : "zeus.hana...ondemand.com:<port>",
    encrypt : true,
    sslCryptoProvider : "openssl",
    sslTrustStore : "/path/to/home/.ssl/DigiCertGlobalRootCA.pem",
    uid : "HANA_USER",
    pwd : "hana_password"
};

The connection parameters for other languages are similar.

If you are running a node.js application on Cloud Foundry, you cannot access the file system directly and so you cannot specify a file in the the sslTrustStore keyword. Instead of the path given above, you can specify the certificate as a string, like this (the body of the certificate has been replaced by “…” here: you should include the entire certificate as a string).

var conn_parms_tcp_string = {
    serverNode : "zeus.hana....ondemand.com:<port>",
    encrypt : true,
    sslCryptoProvider : "openssl",
    sslTrustStore : "-----BEGIN CERTIFICATE----- MIIDr...bd4= -----END CERTIFICATE-----",
    uid : "HANA_USER",
    pwd : "hana_password"
};

 

WebSocket connections

Some organizations block TCP/IP ports for outgoing connections. A solution in these cases is to use WebSocket connections, which run TCP/IP over an HTTP connection.

To specify a WebSocket connection given the information on the HANA Service Dashboard you need to make the following changes to the TCP/IP examples above:

  • In the hostname or address parameter, replace *zeus* at the beginning of the hostname with *wsproxy*.
  • Replace the TCP/IP port with port 80.
  • Add the WebSocketURL connection parameter, providing the address /service/service-id, where service-id is the ID value shown in the HANA Service Dashboard.

You may also need to provide parameters to specify a proxy server, as environments that limit outgoing ports usually require proxy server information. Here I’ll assume the proxy host is just “proxy” and that the port is the default 8080.

Here is an hdbsql connection string using a WebSocket:

> hdbsql -n wsproxy.hana...ondemand.com:80 \
-wsurl /service/<service-id> \
-u HANA_USER -p hana-password \
-e -sslprovider openssl \
-ssltruststore ~/.ssl/DigiCertGlobalRootCA.pem \
-proxyhost proxy -proxyport 8080

The proxyhost and port values can be left off if you are not behind a firewall. Also, if you are on Windows, the SSLTrustStore and SSLProvider may not be needed.

As before, you can look up the connection parameters for your programming language in the HANA Client Interfaces Programming Reference. Here is a Python example (note the proxy_port is a string, not an integer):

conn = dbapi.connect (
    address="wsproxy.hana...ondemand.com",
    port=80,
    user="HANA_USER",
    password="hana-password",
    websocketurl='/service/<service-id>',
    encrypt='true',
    sslCryptoProvider='openssl',
    sslTrustStore='/path/to/home/.ssl/DigiCertGlobalRootCA.pem',
    proxy_host='proxy',
    proxy_port='8080'
)

And here is a node.js example:

var conn_parms_ws = {
   serverNode : "wsproxy.hana...ondemand.com:80",
   encrypt : true,
   proxy_host : "proxy",
   proxy_port : 8080,
   webSocketURL : "/service/<service-id>",
   sslCryptoProvider : "openssl",
   sslTrustStore : "/path/to/home/.ssl/DigiCertGlobalRootCA.pem",
   uid : "HANA_USER",
   pwd : "hana-password"
};

For node.js applications in Cloud Foundry, which do not have access to the file system, you can supply the SSL Trust store as a string instead of a path. Here it is with the middle of the string cut out:

var conn_parms_ws = {
    serverNode : "wsproxy.hana...ondemand.com:80",
    encrypt : true,
    proxy_host : "proxy",
    proxy_port : 8080,
    webSocketURL : "/service/<service-id>",
    sslCryptoProvider : "openssl",
    sslTrustStore : "-----BEGIN CERTIFICATE----- MIID...Ths3p= -----END CERTIFICATE-----",
    uid : "HANA_USER",
    pwd : "hana-password"
};

This set of examples does not yet cover the use of SAP Common Crypto Library, but I hope it provides enough to get you through most cases.

To report this post you need to login first.

8 Comments

You must be Logged on to comment or reply to a post.

  1. Tom Turchioe

    Tom,

    This is great information and will save folks a lot of time in taking advantage of HANA as a Service (HaaS or DBaaS).  I’ve spent time using HaaS with ArcGIS and we know it is in their roadmap to support HaaS…  HANA only when you need it and fast to get started…this will open up another avenue for folks looking to exploit HANA specific advantages with ArcGIS.

    (0) 
  2. Andrew Lunde

    NOTE:  a newer version(XS_PYTHON00_1-70003433.ZIP) has updated hdbcli libraries that provide for encrypted connections.

    If you are using the SAP provided python libraries(XS_PYTHON00_0-70003433.ZIP) found by searching the software center.

    https://launchpad.support.sap.com/#/softwarecenter/search/XS_PYTHON

    The enclosed hdbcli-2.3.14 whl file doesn’t provide for encrypted connections(so connections to HaaS instances will fail).

    You can correct this by finding a more up to date hdbcli in the HANA Client libraries found here.

    https://launchpad.support.sap.com/#/softwarecenter/search/HANA%2520CLIENT%25202

    Click “Maintenance Software Component” -> Pick “LINUX ON X86_64 64BIT” in the architecture pull-down and select the highest “Patch Level” SAR file.

    Note: Pick Linux on X86 even if you’re on a windows machine or mac.  You’re looking for the library that will be packaged up in your application and sent to the CloudFoundry system for execution there, not locally.

    UnSAR and find the hdbcli-2.3.112 tar.gz file (as of this writing).

    Replace the 2.3.14 whl package file with the hdbcli-2.3.112 tar.gz file where you unzipped the XS_PYTHON…ZIP file.

    Your python’s code should now look like this.

     

    hana = env.get_service(label='hana')
    
    
    
    
        schema = hana.credentials['schema']
    
        host = hana.credentials['host']
    
        port = hana.credentials['port']
    
        user = hana.credentials['user']
    
        password = hana.credentials['password']
    
        haascert = hana.credentials['certificate']
    
    
    
    
        connection = dbapi.connect(
    
            address=host,
    
            port=int(port),
    
            user=user,
    
            password=password,
    
            currentSchema=schema,
    
            encrypt="true",
    
            sslValidateCertificate="true",
    
            sslCryptoProvider="openssl",
    
            sslTrustStore=haascert
    
        )
    
    

    Hope this helps somebody out there.

    -Andrew

    (0) 
  3. Prem Roshan Madhusudhan Nair

    Hi,

    I had a question. Is the IP address of a HaaS instance be mapped to a security group in the space?  How is the HANA IP whitelisted from within the application container running in the space?

    Best regards,

    Prem

    (0) 
    1. Tom Slee
      Post author

      Hi Prem, and sorry for being slow.

      Currently Security Groups don’t affect HaaS instances. The only whitelisting I’m aware of is apparently a choice you can make as you set up a HANA Service instance. On the HANA Service dashboard you will see a whitelist of IP addresses. I believe that right now you need to open a ticket to get that changed.

      (0) 
  4. PAVAN S

    How to consume the HaaS in CAP via WebIDE, usually we specify the database_id as parameter under hdi_container resource, but this seems to not work. Can you please help?

     

    (0) 
    1. Tom Slee
      Post author

      Sorry Pavan, I don’t have an answer to that question. CAP runs in Cloud Foundry and uses its own connection methods rather than straight SQL connection parameters.

      (0) 
  5. PAVAN S

    Thanks for the prompt reply, Tom. I actually got to know how to consume –

     

    After creating HaaS container in cf-cockpit, we have to open the dashboard and here we can get the GUID of our DBaaS, now we need to add this as key value pair :- database_id: “<GUID>”

    (1) 

Leave a Reply