Skip to Content
Technical Articles

Secure connection from HDBSQL to SAP HANA Cloud

This post is a collection of tips that might be helpful if you are following new tutorials mission Use Clients to Query an SAP HANA Database published recently by Daniel Van Leeuwen and are trying to connect to SAP HANA Cloud instance from Linux (Ubuntu 18), MacOS X or Windows 10.

First things first

By default an SAP HANA Cloud instance allows access to the instance only from SAP Cloud Platform. All other IP addresses must be explicitly whitelisted by an administrator.

If the IP address of a machine running your HDBSQL is not whitelisted, then you get the error while trying to connect:

-10709: Connection failed (RTE:[89008] Socket closed by peer (<your-HC-instance-tech-id>.hana.prod-eu20.hanacloud.ondemand.com:443)).

Now, let’s assume…

your HDBSQL machine’s IP address is whitelisted

…and you are trying to establish a connection from HDBSQL to a DB instance. You must include the flag -e into hdbsql execution, because only secure communication with SAP HANA Cloud is allowed.

The first step in secure communication is server authentication using the validation of a server’s certificate. This reduces the risk of man-in-the-middle attacks and fake servers gaining information from clients, like your user’s password.

Currently, SAP HANA Cloud server certificates are signed using DigiCert Global Root CA root certificate.

Connect from Linux (Ubuntu)

I have a machine with Ubuntu 18.04 on my desk that I want to use. But here I have a problem

* -10709: Connection failed (RTE:[300010] Cannot create SSL context: SSL trust store cannot be found

when connecting using HDBSQL.

On Linux by default HDBSQL uses OpenSSL for a secure connection, and looks for a file ~/.ssl/trust.pem to provide the root certificate for the authentication of an SAP HANA server.

One of the solutions — provided in the tutorial https://developers.sap.com/tutorials/hana-clients-hdbsql.html:

A public root certificate … can be downloaded from Download PEM, renamed to trust.pem and saved to the specified location.

Where are certificates on Linux?

This root certificate is quite common not just for SAP HANA usage, so it should be already available on my Ubuntu machine. And if I find it, then I could use it with the option -ssltruststore in hdbsql. Let’s check.

OpenSSL is used by HDBSQL by default. Let’s check there first.

openssl version -d

So, /usr/lib/ssl/certs/ links to /etc/ssl/certs.

Accordingly to http://manpages.ubuntu.com/manpages/bionic/man8/update-ca-certificates.8.html:

  • /etc/ssl/certs holds SSL certificates,
  • /usr/share/ca-certificates is the directory of CA certificates,
  • /etc/ssl/certs/ca-certificates.crt is a single-file version of CA certificates.
ll /etc/ssl/certs/DigiCert*Global*Root*CA*

lrwxrwxrwx ... /etc/ssl/certs/DigiCert_Global_Root_CA.pem -> /usr/share/ca-certificates/mozilla/DigiCert_Global_Root_CA.crt

So far, so good.

Let’s connect…

…using the desired certificate:

hdbsql -e -u dbadmin \
-n <your-HC-instance-tech-id>.hana.prod-eu20.hanacloud.ondemand.com:443 \
-ssltruststore /etc/ssl/certs/DigiCert_Global_Root_CA.pem \
"SELECT CURRENT_USER FROM DUMMY"

…using the bundle of all CA certificates:

hdbsql -e -u dbadmin \
-n <your-HC-instance-tech-id>.hana.prod-eu20.hanacloud.ondemand.com:443 \
-ssltruststore /etc/ssl/certs/ca-certificates.crt \
"SELECT CURRENT_USER FROM DUMMY"

…using just the content of the DigiCert_Global_Root_CA certificate (in case you still cannot find or access certificates on the client machine):

hdbsql -e -u dbadmin \
-n <your-HC-instance-tech-id>.hana.prod-eu20.hanacloud.ondemand.com:443 \
-ssltruststore "-----BEGIN CERTIFICATE-----MIIDrzCCApegAwIBAgIQCDvgVpBCRrGhdWrJWZHHSjANBgkqhkiG9w0BAQUFADBhMQswCQYDVQQGEwJVUzEVMBMGA1UEChMMRGlnaUNlcnQgSW5jMRkwFwYDVQQLExB3d3cuZGlnaWNlcnQuY29tMSAwHgYDVQQDExdEaWdpQ2VydCBHbG9iYWwgUm9vdCBDQTAeFw0wNjExMTAwMDAwMDBaFw0zMTExMTAwMDAwMDBaMGExCzAJBgNVBAYTAlVTMRUwEwYDVQQKEwxEaWdpQ2VydCBJbmMxGTAXBgNVBAsTEHd3dy5kaWdpY2VydC5jb20xIDAeBgNVBAMTF0RpZ2lDZXJ0IEdsb2JhbCBSb290IENBMIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEA4jvhEXLeqKTTo1eqUKKPC3eQyaKl7hLOllsBCSDMAZOnTjC3U/dDxGkAV53ijSLdhwZAAIEJzs4bg7/fzTtxRuLWZscFs3YnFo97nh6Vfe63SKMI2tavegw5BmV/Sl0fvBf4q77uKNd0f3p4mVmFaG5cIzJLv07A6Fpt43C/dxC//AH2hdmoRBBYMql1GNXRor5H4idq9Joz+EkIYIvUX7Q6hL+hqkpMfT7PT19sdl6gSzeRntwi5m3OFBqOasv+zbMUZBfHWymeMr/y7vrTC0LUq7dBMtoM1O/4gdW7jVg/tRvoSSiicNoxBN33shbyTApOB6jtSj1etX+jkMOvJwIDAQABo2MwYTAOBgNVHQ8BAf8EBAMCAYYwDwYDVR0TAQH/BAUwAwEB/zAdBgNVHQ4EFgQUA95QNVbRTLtm8KPiGxvDl7I90VUwHwYDVR0jBBgwFoAUA95QNVbRTLtm8KPiGxvDl7I90VUwDQYJKoZIhvcNAQEFBQADggEBAMucN6pIExIK+t1EnE9SsPTfrgT1eXkIoyQY/EsrhMAtudXH/vTBH1jLuG2cenTnmCmrEbXjcKChzUyImZOMkXDiqw8cvpOp/2PV5Adg06O/nVsJ8dWO41P0jmP6P6fbtGbfYmbW0W5BjfIttep3Sp+dWOIrWcBAI+0tKIJFPnlUkiaY4IBIqDfv8NZ5YBberOgOzW6sRBc4L0na4UU+Krk2U886UAb3LujEV0lsYSEY1QSteDwsOoBrp+uvFRTp2InBuThs4pFsiv9kuXclVzDAGySj4dzp30d8tbQkCAUw7C29C79Fv1C5qfPrmAESrciIxpg0X40KPMbp1ZWVbd4=-----END CERTIFICATE-----" \
"SELECT CURRENT_USER FROM DUMMY"

Connect from MacOS X

My usual everyday workstation is MacBook, and — if you use a one too — things are not always that straightforward on MacOS.

One thing is certain — the method with the content of the DigiCert_Global_Root_CA certificate will work, because it does not rely on a physical file with the certificate:

hdbsql -e -u dbadmin \
-n <your-HC-instance-tech-id>.hana.prod-eu20.hanacloud.ondemand.com:443 \
-ssltruststore "-----BEGIN CERTIFICATE-----MIIDrzCCApegAwIBAgIQCDvgVpBCRrGhdWrJWZHHSjANBgkqhkiG9w0BAQUFADBhMQswCQYDVQQGEwJVUzEVMBMGA1UEChMMRGlnaUNlcnQgSW5jMRkwFwYDVQQLExB3d3cuZGlnaWNlcnQuY29tMSAwHgYDVQQDExdEaWdpQ2VydCBHbG9iYWwgUm9vdCBDQTAeFw0wNjExMTAwMDAwMDBaFw0zMTExMTAwMDAwMDBaMGExCzAJBgNVBAYTAlVTMRUwEwYDVQQKEwxEaWdpQ2VydCBJbmMxGTAXBgNVBAsTEHd3dy5kaWdpY2VydC5jb20xIDAeBgNVBAMTF0RpZ2lDZXJ0IEdsb2JhbCBSb290IENBMIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEA4jvhEXLeqKTTo1eqUKKPC3eQyaKl7hLOllsBCSDMAZOnTjC3U/dDxGkAV53ijSLdhwZAAIEJzs4bg7/fzTtxRuLWZscFs3YnFo97nh6Vfe63SKMI2tavegw5BmV/Sl0fvBf4q77uKNd0f3p4mVmFaG5cIzJLv07A6Fpt43C/dxC//AH2hdmoRBBYMql1GNXRor5H4idq9Joz+EkIYIvUX7Q6hL+hqkpMfT7PT19sdl6gSzeRntwi5m3OFBqOasv+zbMUZBfHWymeMr/y7vrTC0LUq7dBMtoM1O/4gdW7jVg/tRvoSSiicNoxBN33shbyTApOB6jtSj1etX+jkMOvJwIDAQABo2MwYTAOBgNVHQ8BAf8EBAMCAYYwDwYDVR0TAQH/BAUwAwEB/zAdBgNVHQ4EFgQUA95QNVbRTLtm8KPiGxvDl7I90VUwHwYDVR0jBBgwFoAUA95QNVbRTLtm8KPiGxvDl7I90VUwDQYJKoZIhvcNAQEFBQADggEBAMucN6pIExIK+t1EnE9SsPTfrgT1eXkIoyQY/EsrhMAtudXH/vTBH1jLuG2cenTnmCmrEbXjcKChzUyImZOMkXDiqw8cvpOp/2PV5Adg06O/nVsJ8dWO41P0jmP6P6fbtGbfYmbW0W5BjfIttep3Sp+dWOIrWcBAI+0tKIJFPnlUkiaY4IBIqDfv8NZ5YBberOgOzW6sRBc4L0na4UU+Krk2U886UAb3LujEV0lsYSEY1QSteDwsOoBrp+uvFRTp2InBuThs4pFsiv9kuXclVzDAGySj4dzp30d8tbQkCAUw7C29C79Fv1C5qfPrmAESrciIxpg0X40KPMbp1ZWVbd4=-----END CERTIFICATE-----" \
"SELECT CURRENT_USER FROM DUMMY"

How cool is that?

Where are my certificates on MacOS?

MacOS uses keychains — encrypted containers — to store passwords, keys and certificates. This is where we can find the required certificate using the native Keychain Utility.

Physically on my Mac this System Roots keychain is in the binary file /System/Library/Keychains/SystemRootCertificates.keychain. I can work with it using the security command from the shell. E.g. I can find the entry of required certificate and can output its text content using -p option.

security find-certificate -c "DigiCert Global Root CA" \
/System/Library/Keychains/SystemRootCertificates.keychain

security find-certificate -c "DigiCert Global Root CA" -p \
/System/Library/Keychains/SystemRootCertificates.keychain

The HDBSQL by default uses OpenSSL on MacOS too. So, what’s about OpenSSL’s certificates on this machine?

openssl version -a
ls -l /private/etc/ssl
ls -l /private/etc/ssl/certs/
grep "DigiCert Global Root CA" /private/etc/ssl/cert.pem

A few things to notice:

  1. MacOS’s implementation of openssl is a fork based on LibreSSL
  2. Its folder of individual certificates /private/etc/ssl/certs/ is empty, but…
  3. …there is a single file cert.pem with CA certificates, incl. DigiCert Global Root CA.

Because I do have OpenSSL installed with brew as well, let’s check this one too.

brew info openssl | grep PATH
ls -l /usr/local/etc/openssl@1.1
ls -l /usr/local/etc/openssl@1.1/certs

A few things to notice:

  1. OpenSSL certs folder is empty too, but…
  2. …there is a single file cert.pem just like in the case with LibreSSL.

Let’s connect…

geeky way (by passing the content of the certificate found in the keychain):

hdbsql -e -u dbadmin \
-n <your-HC-instance-tech-id>.hana.prod-eu20.hanacloud.ondemand.com:443 \
-ssltruststore "`security find-certificate -c "DigiCert Global Root CA" -p /System/Library/Keychains/SystemRootCertificates.keychain`" \
"SELECT CURRENT_USER FROM DUMMY"

…using LibreSSL root certificates bundle file:

hdbsql -e -u dbadmin \
-n <your-HC-instance-tech-id>.hana.prod-eu20.hanacloud.ondemand.com:443 \
-ssltruststore /private/etc/ssl/cert.pem \
"SELECT CURRENT_USER FROM DUMMY"

…using OpenSSL root certificates bundle file:

hdbsql -e -u dbadmin \
-n <your-HC-instance-tech-id>.hana.prod-eu20.hanacloud.ondemand.com:443 \
-ssltruststore /usr/local/etc/openssl@1.1/cert.pem \
"SELECT CURRENT_USER FROM DUMMY"

…the proper way by finally creating ~/.ssl/trust.pem file…

mkdir -p ~/.ssl

security find-certificate -c "DigiCert Global Root CA" \
-p /System/Library/Keychains/SystemRootCertificates.keychain \
>> ~/.ssl/trust.pem

… and ignoring -ssltruststore flag all together in future calls!

hdbsql -e -u dbadmin \
-n <your-HC-instance-tech-id>.hana.prod-eu20.hanacloud.ondemand.com:443 \
"SELECT CURRENT_USER FROM DUMMY"

Connect from MS Windows

And what about Windows?

hdbsql -e -u dbadmin ^
-n <your-HC-instance-tech-id>.hana.prod-eu20.hanacloud.ondemand.com:443 ^
"SELECT CURRENT_USER FROM DUMMY"

There was no need to add any -ssltruststore in Windows, because in this OS by default HDBSQL uses mscrypto (not openssl) and the default Windows certificate store.

As long as you can see DigiCert Global Root CA among root certificates in the Certificate Manager, everything should work Ok.

I hope you find these tips useful too — no matter OS used running HDBSQL.


Stay healthy,
-Vitaliy (aka @Sygyzmundovych)

14 Comments
You must be Logged on to comment or reply to a post.
  • Great blog!

    And to all Windows ‘haters’ 😜… “There was no need to add any….” is one reason why Windows still rules the user desktop OS with >88%.

    PS I love Unix/Linux ServerSide

    • That’s a bit of a circular argument. SAP chose to implement it in a more user friendly way on Windows and didn’t pay all that much effort for Linux and macOS users; that was likely because there are fewer end users on the later systems.

      User desktop share – in professional settings – is largely dominated by what’s already known (no/little training required) and what incurs the smallest change costs. Device management for macOS/Linux is still not on par with Windows, even though support call rate for macOS is lower.

      Windows is not dominating by technological merit but on ecosystem.

  • Nice post, indeed! Thanks for that.

    Just reading through this should make any product owner/manager think “isn’t there a way to make that less of a hassle?”

    While I don’t consider this a product fault, the many hoops one have to jump through here to get it just right do call out for improvement!

    • Thanks, Lars.

      While this post may look overloaded, the setup is not that difficult: one just can set the required trust.pem in the default location as per documentation and forget about it 🙂 I just wanted to dig a bit more and to share some more options.

      The reason I think there are more steps on Linux/MacOS is because — differently from Windows — it is one HDBSQL client while each Linux OS plus MacOS uses different places and mechanisms for CA certificates.

      That’s why SAP provides own “common crypto” way, which is available for customers and partners on SAP Marketplace.

      Regards.

  • Hi,

    In my hanabox (suse machine), I can run “hdbsql  -e -u dbadmin…..” to connect to hana cloud with 443 port.

    However, I use exactly same DSN setting in odbc.ini, after that, I run “isql -v <DSN> dbadmin <password>” , I always got this “…..Socket closed by peer”  error.

    Any idea?

    Thanks,Lin

  • Thanks a lot for sharing this , it’s a excellent blog 😉

    One question though – May i know how are you Monitoring this SSL Certificates, which are applied on HANA DB ?

    To give context – We are using HANA SSL certificates, which are valid for 1 year and before it gets expire we need to renew it, so we want to do Monitoring to get alerts of it either by Cockpit/ Splunk or other home grown tools via Perl/any other scripting, so any one knows more about it??

  • Great blog!
    The sslTrustStore property also works for node.js and python (hdbcli) connections.
    Adding the cert to ~/.ssl/trust.pem fixes it for everything, including java/jdbc (if you don’t do it, you have to manually add the cert tto the java cacerts keystore).

    Cheers!

  • I was able to connect on Windows via hdbsql, but I cannot make it work in C# with Sap.Data.Hana.HanaConnection. I am setting encrypt=true to the connection string, but that doesn’t seem to change anything. What do I need to do to connect to Hana Cloud via C#?

  • Hi Witalij Rudnicki, I was able to import the certificate as explained above for Windows, however I am still facing the issue “Connection failed (RTE:[89008] Socket closed by peer….. “. Kindly suggest.