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.vanleeuwen and are trying to connect to SAP HANA Cloud instance from Linux (Ubuntu 18), MacOS X or Windows 10.
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:
Now, let's assume...
...and you are trying to establish a connection from HDBSQL to a DB instance. You must include the flag
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.
I have a machine with Ubuntu 18.04 on my desk that I want to use. But here I have a problem
when connecting using HDBSQL.
On Linux by default HDBSQL uses OpenSSL for a secure connection, and looks for a file
One of the solutions -- provided in the tutorial https://developers.sap.com/tutorials/hana-clients-hdbsql.html:
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
OpenSSL is used by HDBSQL by default. Let's check there first.
So,
Accordingly to http://manpages.ubuntu.com/manpages/bionic/man8/update-ca-certificates.8.html:
So far, so good.
...using the desired certificate:
...using the bundle of all CA certificates:
...using just the content of the DigiCert_Global_Root_CA certificate (in case you still cannot find or access certificates on the client machine):
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:
How cool is that?
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
The HDBSQL by default uses OpenSSL on MacOS too. So, what's about OpenSSL's certificates on this machine?
A few things to notice:
Because I do have OpenSSL installed with
A few things to notice:
...geeky way (by passing the content of the certificate found in the keychain):
...using LibreSSL root certificates bundle file:
...using OpenSSL root certificates bundle file:
...the proper way by finally creating
... and ignoring
And what about Windows?
There was no need to add any
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)
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 foundwhen 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 -dSo,
/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/certsholds SSL certificates,/usr/share/ca-certificatesis the directory of CA certificates,/etc/ssl/certs/ca-certificates.crtis 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.crtSo 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.keychainThe 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.pemA few things to notice:
- MacOS's implementation of
opensslis a fork based on LibreSSL - Its folder of individual certificates
/private/etc/ssl/certs/is empty, but... - ...there is a single file
cert.pemwith 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/certsA few things to notice:
- OpenSSL
certsfolder is empty too, but... - ...there is a single file
cert.pemjust 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)
- SAP Managed Tags:
34 Comments
