Connect to SAP HANA with a Secure Connection from Python

Before proceeding, test out the connection parameters. Knowing that these parameters are correct can make debugging in the coming steps much easier.

Use the following code and substitute in your connection parameters.

from hdbcli import dbapi

conn = dbapi.connect(
    address="<host name>",
    port=<port>,
    user="<username>",
    password="<password>",
#   cryptographic providers
#   sslCryptoProvider='openssl',        #default for Linux/Mac
#   sslCryptoProvider='mscrypto',       #default for Windows
#   sslCryptoProvider='commoncrypto',   #SAP Common Crypto Library

#   OpenSSL trust store location containing the CA cert that signed the HANA server's cert
#   sslTrustStore='/home/<username>/.ssl/trust.pem',

#   sslKeyStore='C:\SAP\hdbclient\sapcli.pse',  #commoncrypto
#   sslKeyStore='/home/<username>/sap/hdbclient/sapcli.pse',

    ENCRYPT=True,
    sslValidateCertificate=False
)


with conn.cursor() as cursor:
    sql = "SELECT SYSTEM_ID, DATABASE_NAME, VERSION FROM M_DATABASE"
    cursor.execute(sql)
    result = cursor.fetchall()
print("Connection to SAP HANA Service successful.")
print("SID =", result[0][0])
print("Database Name =", result[0][1])
print("Version =", result[0][2])
conn.close()

You’ve disabled sslValidateCertificate temporarily to restrict scope and test
other connection parameters. Don’t do this in production.

Let’s briefly discuss the connection parameters. To connect to a SAP HANA as a Service or HANA Cloud instance you must specify ENCRYPT=True in your connection parameters to enable TLS encryption as these services do not allow unencrypted connections. Different cryptographic providers are available depending on the platform. Windows uses mscrypto by default and can be configured to use commoncrypto. Linux and Mac use openssl by default and can be configured to use commoncrypto.

sslValidateCertificate=False indicates to not validate the certificate authority that signed the database’s certificate. On Windows, the certificate authority’s public certificate should be available so this can be optionally set to True.
For additional details see the Encrypted Communication section of the documentation titled Connecting to an SAP HANA Service Instance Directly from SAP HANA Clients.
For additional details on the connection parameters see Connect Method and Python Connection Properties.

If you run the code in the current state, you’ll see something similar to the output below.

python pythonQuery.py
Connection to SAP HANA Service successful.
SID = H00
Database Name = H00
Version = 2.00.040.00.1554459575

On a Mac, if you encounter an issue where the crypto library is not loading, then this article Python crashing on MacOS 10.15 Beta may help.

With the sslValidateCertificate parameter set to True, the SAP HANA client attempts to validate the server’s certificate when connecting. To do so, the client needs to have access to the root certificate of the certificate authority that signed the server’s certificate.

If it is not already set to True, change the sslValidateCertificate parameter True.

If you run your code now, you may see something similar to the output below. Note that when connecting to HANA as a Service on Windows, the certificate authority’s root certificate is installed by default and available to the SAP HANA Client.

Traceback (most recent call last):
  File "secure_conn.py", line 8, in <module>
    ENCRYPT=True
hdbcli.dbapi.Error: (-10709, 'Connection failed (RTE:[300010]
  Cannot create SSL context:   SSL trust store cannot be found:
  /home/userX/.ssl/trust.pem (zeus.SAP HANA.prod.us-east-1.whitney.dbaas.ondemand.com:208xx))')

If you see this error, specify where to find the root certificate from the certificate authority that signed the database server’s certificate. For SAP HANA as a Service, the certificate authority is DigiCert.

The process differs between Windows and Mac/Linux.

Check to see if the DigiCert Global Root CA is installed on Windows.

If it isn’t, download DigiCertGlobalRootCA.crt and then right-click to install it.

If you see a security warning, click Open.

You can install the certificate for current user or the local machine.

Install the certificate under Trusted Root Certificate Authorities.

Complete installing by clicking Finish.

After installing the certificate and enabling SSL certificate validation you should see something similar to the output below.

python pythonQuery.py
Connection to SAP HANA Service successful.
SID = H00
Database Name = H00
Version = 2.00.040.00.1554459575

To specify an encryption provider, the parameter below can be used. Note the default value is mscrypto on Windows. In the next section, this will be changed to use the SAP Common Crypto Library.

sslCryptoProvider='mscrypto',

openssl x509 -inform der -in DigiCertGlobalRootCA.crt -out trust.pem

Connection to SAP HANA Service successful.
SID = H00
Database Name = H00
Version = 2.00.040.00.1554459575

sslTrustStore='<Path to certificate>'

sslCryptoProvider='openssl'

The SAP Common Crypto Library provides another library that can be used to securely connect to HANA. Additionally, it is required for LDAP authentication or client-side encryption. This tutorial serves as a general overview for using this library. See also Configuring the Client for Client-Side Encryption and LDAP.

The following steps describe how to use the SAP Common Crypto Library instead of OpenSSL or Windows-provided software.

Make sure you’ve installed the SAP HANA Clients from the SAP Software Downloads as opposed to SAP Development Tools. The version downloaded from SAP Software Downloads uses a different license and contains additional cryptographic libraries. If you are unsure which version you have, the manifest.mf file in the SAP HANA Client install can be consulted. If it says keycaption: SAP HANA CLIENT W/O CRYPTO you should download a new version that includes cryptographic libraries from SAP Software Downloads.

A utility named sapgenpse is required to generate a pse file containing DigiCertAssuredIDRootCA.

• Download the SAP CRYPTOGRAPHIC SOFTWARE from SAP Software Downloads which contains the sapgenpse tool.
• Choose Installation and Upgrades.
• Choose By Alphabetical Index.
• Choose C.
• Choose SAP CRYPTOGRAPHIC SOFTWARE.
• Choose SAPCRYPTOLIB.
• Choose COMMONCRYPTOLIB 8.
• Choose the appropriate platform and download.
• Extract the SAR file using the SAPCAR utility (also available from SAP Software Downloads).

SAPCAR -xvf SAPCRYPTOLIBP_8530-20011729.SAR

Move or copy the extracted files into the hdbclient folder which will be added to the path with the following step.

The following script should be run which adds the HANA client folder to the PATH and creates an environment variable named SECUDIR.

c:\sap\hdbclient\hdbclienv.bat

After running the script, the SAP HANA client install directory is in the path and an environment variable named SECUDIR is set.

echo %SECUDIR%
c:\SAP\hdbclient\

Use the following command to generate a keystore. Note, additional details can be seen by adding the -log option.

sapgenpse gen_pse -p "%SECUDIR%/sapcli.pse" "CN=MyComputerName"

This tutorial only provides a CN or Common Name as an LDAP parameter but the Common Crypto Library implements the full LDAPv3 standard. Full identification parameters are beyond the scope of this tutorial.

Do not provide a pin as a pse protected by a pin is not supported.

Next, add the root certificate to the keystore just generated.

sapgenpse maintain_pk -p "%SECUDIR%/sapcli.pse" -a "C:\Users\userX\Downloads\DigiCertGlobalRootCA.crt"

You can double-check if your root certificate has been added with the following command:

sapgenpse maintain_pk -p "%SECUDIR%/sapcli.pse% -l

The following script should be run which adds the HANA client folder to the PATH and creates an environment variable named SECUDIR.

source ~/sap/hdbclient/hdbclienv.sh

After running the script, the SAP HANA client install directory is added to the path and an environment variable named SECUDIR is set.

echo $SECUDIR
/home/userX/sap/hdblient

Note, the source command above is used to make changes to the current shell. For further information see script sourcing.

Next you need to generate a keystore. You can do so with the command:

sapgenpse gen_pse -p "$SECUDIR/sapcli.pse" "CN=$HOSTNAME"

This tutorial only provides a CN or Common Name as an LDAP parameter but the Common Crypto Library implements the full LDAPv3 standard. Full identification parameters are beyond the scope of this tutorial.

Do not provide a pin as a pse protected by a pin is not supported.

Now you’ll want to add your root certificate to the keystore you just generated. For this, run the command:

sapgenpse maintain_pk -p "$SECUDIR/sapcli.pse" -a ~/.ssl/trust.pem

You can double-check if your root certificate has been added with the following command:

sapgenpse maintain_pk -p "$SECUDIR/sapcli.pse" -l

You are now ready to use the SAP Common Crypto Library instead of OpenSSL.
Change the sslCryptoProvider to be commoncryptoand run the test app.
Optionally, the location for the sapcli.pse file can be specified via the sslKeyStore setting.

python pythonQuery.py

You have now connected securely to HANA using multiple cryptographic providers.