SAP HANA Tutorial, Material and Certification Guide

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.

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.

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

SAP HANA Cloud, SAP HANA Learning, SAP HANA Tutorial and Material, SAP HANA Certifications

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

◉ /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 \

"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 and ignoring -ssltruststore flag all together!)

mkdir -p ~/.ssl

security find-certificate -c "DigiCert Global Root CA" \

-p /System/Library/Keychains/SystemRootCertificates.keychain \

>> ~/.ssl/trust.pem

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.

SAP HANA Central

Pages

Wednesday, 15 April 2020

Secure connection from HDBSQL to SAP HANA Cloud

No comments:

Post a Comment