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)