Skip to Content

Connect to SAP HANA with a Secure Connection from Python

test
0 %
Connect to SAP HANA with a Secure Connection from Python
Details

Connect to SAP HANA with a Secure Connection from Python

2020-03-17
Configure a cryptographic library provider to enable a secure connection to SAP HANA from a Python app.

You will learn

  • How to securely connect to SAP HANA using mscrypto on Windows
  • How to securely connect to SAP HANA using OpenSSL on Mac or Linux
  • How to securely connect to SAP HANA using the SAP Common Crypto Library on Windows, Mac, or Linux

Prerequisites

If you have not used the SAP HANA client for Python, check out the Connect to SAP HANA Using Python tutorial.


Step 1: Find connection parameters to SAP HANA

The following information is needed to connect to SAP HANA:

  • SAP HANA host name and port
  • Database username and password

There are multiple ways to gather this information depending on which version of HANA you are using.

If you are using HANA as a Service, you can find endpoint information in the SAP HANA Service Dashboard.

SAP HANA Service Dashboard

If you are using HANA Cloud, you can find the endpoint information in the SAP Cloud Platform Cockpit.

SAP HANA Cloud Dashboard

If you are using SAP HANA, express edition, the host and port by default are hexehost and 39015.

HANA Express
Do you have the following information ready?
×
Step 2: Test connection parameters without validating the server certificate

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.

Log on to answer question
Step 3: Set up certificate validation

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.

open manage computer certificatesDigiCert Global Root CA

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

win_1

If you see a security warning, click Open.

win_2

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

win_3

Install the certificate under Trusted Root Certificate Authorities.

win_4

Complete installing by clicking Finish.

win_5

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',

Download DigiCertGlobalRootCA.crt.pem.

Place the file into the location the SAP HANA Client by default looks for certificates as specified in the error message, such as

/Users/userX/.ssl/trust.pem

or

/home/userX/.ssl/trust.pem.

Note you need to rename the file to trust.pem.
Create the folder .ssl if it does not already exist.

If you instead have a .crt file, it can be converted to a .pem file with the command below.

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

After placing the certificate in the default location, the output should now be as below.

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

If you want to use a custom path for your certificate, the following parameter can be used.

sslTrustStore='<Path to certificate>'

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

sslCryptoProvider='openssl'
Log on to answer question
Step 4: Use the SAP Common Crypto Library (optional)

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.

sapgenpse

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.

sapsegen

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.

Log on to answer question

Next Steps

Back to top