Skip to Content
Technical Articles
Author's profile photo Witalij Rudnicki

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)

Assigned tags

      29 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Denys van Kempen
      Denys van Kempen

      Great post, Vitaliy. Thanks for sharing.

      Author's profile photo Benjamin Houttuin
      Benjamin Houttuin

      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

      Author's profile photo Lars Breddemann
      Lars Breddemann

      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.

      Author's profile photo Lars Breddemann
      Lars Breddemann

      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!

      Author's profile photo Witalij Rudnicki
      Witalij Rudnicki
      Blog Post Author

      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.

      Author's profile photo Lin Murong
      Lin Murong

      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

      Author's profile photo Witalij Rudnicki
      Witalij Rudnicki
      Blog Post Author

      What is your complete DSN entry?

      Btw:

      1. I just published another post - specifically about ODBC (but with Windows OS used): https://blogs.sap.com/2020/04/21/connect-to-sap-hana-from-windows-powershell-using-odbc/
      2. It is better to publish questions like this in https://answers.sap.com/index.html where more people can see and answer.

      Regards,
      -Vitaliy

      Author's profile photo Anand Tigadikar
      Anand Tigadikar

      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??

      Author's profile photo Henrique Pinto
      Henrique Pinto

      Just notice you're not adding the HANA Cloud server certificates to the store, but rather the CA/root certificate. It's described in this page:
      https://help.sap.com/viewer/db19c7071e5f4101837e23f06e576495/cloud/en-US/030a162d380b4ec0bc6a284954c8256d.html (just look at the certificate, 2nd bullet).

      Author's profile photo Henrique Pinto
      Henrique Pinto

      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!

      Author's profile photo Luca Toldo
      Luca Toldo

      excellent, works on mac perfectly, thankyou

      Author's profile photo John Madison
      John Madison

      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#?

      Author's profile photo Lava Kumar Rayapati
      Lava Kumar Rayapati

      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.

      Author's profile photo Lava Kumar Rayapati
      Lava Kumar Rayapati

      it's working fine after adding encrypt=True;sslValidateCertificate=False to connection string

      Author's profile photo Gregory Misiorek
      Gregory Misiorek

      Hi Witalij,

      just wanted to acknowledge your blog and its continuing validity as of today. even though i could not ultimately resolve the error listed on top of your troubleshooting guide, i was able to perform certain steps as per your recommendations.

      so, thanks for sharing your knowledge of this not so obvious domain.

      rgds,

      greg

      Author's profile photo Maxime Simon
      Maxime Simon

      It seems to be working for everyone but I cannot make it work for my HANA Cloud instance.

      I tried first using the windows latest client 2.6.54 following your syntax, but I get this error:

      -10709: Connection failed (RTE:[300015] SSL certificate validation failed: The certificate chain was issued by an authority that is not trusted.

      Then I tried with the linux HANA Client after installing the certificate at the same path that you described, with the different methods (certificate path, or pasting the certificate directly) but I get this error :

      * -10709: Connection failed (RTE:[300010] Cannot create SSL context: SSL trust store cannot be found: /root/.ssl/trust.pem (a5a4211-3e3b-4b7c-9a4c-ea75aeeba55f.hana.prod-eu10.hanacloud.ondemand.com:443))

      I must be missing something...

      Author's profile photo Maxime Simon
      Maxime Simon

      I tried again to connect from the same windows PC to the same HANA Cloud instance after a few weeks, and now it works fine. I did not change any SSL related setting...

      Author's profile photo Jeremy Yu
      Jeremy Yu

      I'm getting that error when connecting to the HC instance from a DWC Academy tenant.   I am able to connect to an HC tenant I create myself in SCP but not via the DB user in DWC.

      Error: (-10709, 'Connection failed (RTE:[89008] Socket closed by peer (<my-tenant>.hana.prod-us10.hanacloud.ondemand.com:443)
      
      Thanks in advance for any suggestions
      
      
      
      Author's profile photo Shivam Shukla
      Shivam Shukla

      Hi Witalij Rudnicki -  i was working on Data Lake Connectivity and this blog really helped me , hence adding my thank you note sir here.

       

      Thanks,

      Shivam

      Author's profile photo Witalij Rudnicki
      Witalij Rudnicki
      Blog Post Author

      Thank you for adding this comment Shivam Shukla!

      I hope you have seen https://developers.sap.com/group.hana-cloud-clients-data-lake.html when it comes to connectivity to a data lake in SAP HANA Cloud.

      Regards.

      Author's profile photo Shivam Shukla
      Shivam Shukla

      Yes Witalij Rudnicki  - I am searching and exploring almost everything in data lake connectivity possibility.

      I will be sharing my learning soon for the feedback .

      thanks,

      shivam

      Author's profile photo Leszek Bednarz
      Leszek Bednarz

      Hi Witalij,

      I am trying to connect to my HANA Cloud trial instance, but I am receiving the following error:

      * 10: authentication failed SQLSTATE: 28000

      I am using this command on Mac:

      /Applications/sap/hdbclient/hdbsql -e -u T1 -p “Password” \

      -n xxxxxx-xxxxxx-xxxx-xxxxxxx-xxxxxdb1.hana.trial-eu10.hanacloud.ondemand.com:443 \

      -ssltruststore "-----BEGIN CERTIFICATE-----MIIDrzCCApegAwIBAgIQCDvgVpBCRrGhdWrJWZ

       

      I was trying to search internet using  the error message, but it seams it is not giving any relevant results. Do you have any idea what's wrong with my connection?

       

      Regards,

      Leszek

      Author's profile photo Witalij Rudnicki
      Witalij Rudnicki
      Blog Post Author

      Leszek Bednarz, the error says your user+password combo is incorrect? It T1 user the one created by you? Can you log on as a DBADMIN user and change T1's password?

      Author's profile photo Leszek Bednarz
      Leszek Bednarz

      Hi Witalij,

      User T1 has been created by me (password known and correct), but to make sure I changed it again using SQL console. I also tried to connect using DBADMIN. I failed with both users.

      The credentials should be fine since I was able to add this HANA instance in SAP HANA Database Explorer using both users.

      Do you have any other idea about potential issues?

      Regards,

      Leszek

      Author's profile photo Leszek Bednarz
      Leszek Bednarz

      Hi Witalij,

      Problem solved. I had an old version of the HANA Tools.

      Installation of the latest version solved the issue: HDBSQL version 2.9.19.1622741887.

      Thanks for the great post.

      Regards,

      Leszek

      Author's profile photo Witalij Rudnicki
      Witalij Rudnicki
      Blog Post Author
      1. Try removing `-p “Password”` and enter the password when the prompt requests it.
      2. Do you need quotes around the password? If yes, then replace it from " to ' (signle qoutes).
      3. Check if your user has "ODBC/JDBC Enabled" in user's settings in the HANA Cockpit -> User Management.

      Regards.

      Author's profile photo Ritesh Singh
      Ritesh Singh

      Thanks for sharing this Witalij Rudnicki , Great post

      Author's profile photo Srdjan Boskovic
      Srdjan Boskovic

      Thank you Witalij Rudnicki for this excellent post.

      I would like query one HANA db in SAP HANA Cloud, using node-hdb client running on ,my notebook.

      How/where to request the IP of my notebook to be whitelisted?

      Author's profile photo Witalij Rudnicki
      Witalij Rudnicki
      Blog Post Author

      Здраво Srdjan Boskovic

      In this case, accordingly to https://help.sap.com/viewer/9ae9104a46f74a6583ce5182e7fb20cb/hanacloud/en-US/770d34deb86d4eb49dc944ce9921228c.html you need to know who has the BTP role SPACE DEVELOPER in the Cloud Foundry space where you SAP HANA db instance resides. That person should be able to add your laptop's IP to the list of allowed addresses.