Secure connection from Python to SAP HANA

[Vitaliy-R]

While continuing with the tutorials mission Use Clients to Query an SAP HANA Database published by daniel.vanleeuwen I finally arrived at the Python tutorial. I had left it as the last one to do.

I used the program from that tutorial to connect to the SAP HANA Cloud instance. As discussed before this communication has to be secure. The same requirement applies as well if connecting to an HDI container in SAP Cloud Platform trial, as I showed in our live streaming with iinside recently.

So, let's just slightly modify the program pythonQuery.py and make sure sslValidateCertificate=True is set and try it on Windows 10, Linux (Ubuntu), and MacOS Catalina.

#Import your dependencies

import platform

from hdbcli import dbapi



#verify that this is a 64 bit version of Python

print ("Platform architecture: " + platform.architecture()[0])



#Initialize your connection

conn = dbapi.connect(

    #address='hxehost',

    #port='39015',

    #user='User1',

    #password='Password1',

    key='HCUSER1UserKey', # address, port, user and password are retreived from the hdbuserstore

    encrypt=True, # must be set to True when connecting to HANA Cloud

    sslValidateCertificate=True # True HC, False for HANA Express.

)

#If no errors, print connected

print('connected\n')



cursor = conn.cursor()

sql_command = "select TITLE, FIRSTNAME, NAME from HOTEL.CUSTOMER;"

cursor.execute(sql_command)

rows = cursor.fetchall()

for row in rows:

    for col in row:

        print ("%s" % col, end =" ")

    print ("")

cursor.close()

conn.close()

In my case I use HCUSER1UserKey from HANA Clients User Store, which is the one connecting to my HANA Cloud instance. But -- again -- the same would apply to connect to an HDI Container in SCP Trial.

On Windows...

...the program executes ok.



But -- as we've already seen with HDBSQL -- a HANA Client uses default Windows's mscrypto. So, not much new fun here.

On Linux...

...the first run of python3 pythonQuery.py fails with hdbcli.dbapi.Error: (-10709, 'Connection failed (RTE:[300010] Cannot create SSL context: SSL trust store cannot be found: /home/vitaliy/.ssl/trust.pem error.

One way of fixing that is to finally set ~/.ssl/trust.pem file 🙂 Another way is to search the required certificate, if already available on the OS.

"But what would be the Python way?"I thought ?

Well, some of you remember I used to work with IoT a lot in the past. There in Python, I would often use requests package for HTTP communication, which requires CA certificates for secure communication too. Therefore it comes with one other package dependency: certifi -- package for providing Mozilla's CA Bundle.

Let's check with pip search certifi and indeed I have it already... the version from 2018, when I stopped actively working on IoT examples. Ah, nostalgia... But, let's get back to reality.

certifi (2020.4.5.1)                        - Python package for providing Mozilla's CA Bundle.

  INSTALLED: 2018.1.18

  LATEST:    2020.4.5.1

Now, before you jump on upgrading it (just the way I did at first), please read the Erratum part at the bottom of the blog. At least if you are at Ubuntu 18.04, as I was.

But if your Linux machine is missing this Python package, then install it.

pip3 install certifi

Let's update the program to use certifi now. I saved it as pythonQuerySecure.py now.

#Import your dependencies

import platform

from hdbcli import dbapi

import certifi



#verify that this is a 64 bit version of Python

print ("Platform architecture: " + platform.architecture()[0])



#Initialize your connection

conn = dbapi.connect(

    #address='10.7.168.11',

    #port='39015',

    #user='User1',

    #password='Password1',

    key='HCUSER1UserKey', # address, port, user and password are retreived from the hdbuserstore

    encrypt=True, # must be set to True when connecting to HANA Cloud

    sslValidateCertificate=True, # True HC, False for HANA Express.

    sslTrustStore=certifi.where()

)

#If no errors, print connected

print('connected\n')



cursor = conn.cursor()

sql_command = "select TITLE, FIRSTNAME, NAME from HOTEL.CUSTOMER;"

cursor.execute(sql_command)

rows = cursor.fetchall()

for row in rows:

    for col in row:

        print ("%s" % col, end =" ")

    print ("")

cursor.close()

conn.close()

And now let's execute it.

python3 pythonQuerySecure.py

Everything ran Ok this time.

On macOS...

...I used to run some IoT stuff too, so the certifi Python package was there as well...



...but here the upgrade was not a problem (as on Ubuntu)...

pip install --upgrade certifi

pip show certifi

python3 -m certifi

python3 -m certifi -c | grep "DigiCert Global Root CA"

...the problem was Abort trap: 6 error...

...with following details, when clicked on Report...:

...

Path:                  /usr/local/Cellar/python/3.7.7/Frameworks/Python.framework/Versions/3.7/Resources/Python.app/Contents/MacOS/Python

...

OS Version:            Mac OS X 10.15.4 (19E287)

Report Version:        12

...

Exception Type:        EXC_CRASH (SIGABRT)

Exception Codes:       0x0000000000000000, 0x0000000000000000

Exception Note:        EXC_CORPSE_NOTIFY



Application Specific Information:

/usr/lib/libssl.dylib

abort() called

Invalid dylib load. Clients should not load the unversioned libssl dylib as it does not have a stable ABI.

...

A few hours of investigation and finally down to the root cause (partially discussed previously😞

MacOS's openssl is a version based on LibreSSL fork.

...and the workaround is bringing original OpenSSL to the rescue (assuming you have Homebrew already on your macOS).

brew install openssl

export DYLD_FALLBACK_LIBRARY_PATH=/usr/local/opt/openssl/lib:$DYLD_FALLBACK_LIBRARY_PATH

Now on to debugging...

...using MS Code on macOS, as described in the tutorial.

Unfortunately here I was running into the same problem, as the environment variable set in the shell was not applicable to the debugging environment.

The solution I used (and I'll be glad to hear how would you solve it too) was to edit the VS workspace's .vscode/launch.json file and add to configurations:

"env": {

   "DYLD_FALLBACK_LIBRARY_PATH": "/usr/local/opt/openssl/lib"

}

And debugging works as expected now too.

Erratum (at least for Ubuntu)

So, if you have Ubuntu too, then you might run into the same issue I had done. And hopefully, this section will save you hours of investigation, if you are using certifi too.

So, as I have mentioned pip search certifi showed me I had some 2018 version on my computer with Ubuntu 18.04 as an OS.

INSTALLED: 2018.1.18

"Ok, let's install/upgrade the package and use it!" I thought.

pip install --upgrade certifi

pip show certifi

python3 -m certifi

python3 -m certifi -c | grep "DigiCert Global Root CA"

What we can get from the output is:

1. The package created a file ~/.local/lib/python3.6/site-packages/certifi/cacert.pem with the up-to-date CA certificates bundle.


2. This package indeed contains DigiCert Global Root CA to authenticate HANA instances in SAP Cloud Platform.

So far, so good. I did not pay special attention to "Defaulting to user installation because normal site-packages is not writeable" message from pip install ....

Let's run the program python3 pythonQuerySecure.py and ... for hours, I stuck with that "hdbcli.dbapi.Error: (-10709, 'Connection failed (RTE:[89008] Socket closed by peer" error.

I tried OpenSUSE machine -- everything worked. I tried replacing certifi's ~/.local/lib/python3.6/site-packages/certifi/cacert.pem with /etc/ssl/certs/ca-certificates.crt -- everything worked too. I really stuck until I ran the following next to each other:

dpkg -l | grep python3-certifi

pip3 search certifi

Two different versions? ? That was a clue!

So, it happens Ubuntu has its own package python3-certifi installed together with Python3, and its latest version on my Ubuntu 18.04 is indeed 2018.1.18-2: https://packages.ubuntu.com/bionic/python3-certifi.

To fix the configuration:

pip3 uninstall certifi

sudo apt-get install --reinstall python3-certifi

We are back at home!

Conclusion

If you tell me that I should just install and use the SAP Common Crypto library in the first place to avoid all these issues, then I say you are right. But the idea of this exercise is learning stuff the hard way because it is the only way to understand it ?

Plus, if you are reading this, then most probably you ran into one of these issues, and web search brought you to this page. And I hope it will help you solve those problems too.

---

Stay healthy,
-Vitaliy (aka @Sygyzmundovych)