Skip to Content

Connect to SAP HANA with a Secure Connection from Python

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

    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

    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.

    Python
    Copy
    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.

  • Step 3

    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 certificates
    DigiCert 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',
    
  • Step 4

    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).
    Shell
    Copy
    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.

    Shell
    Copy
    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.

    Shell
    Copy
    echo %SECUDIR%
    c:\SAP\hdbclient\
    

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

    Shell
    Copy
    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.

    Shell
    Copy
    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:

    Shell
    Copy
    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.

    Shell
    Copy
    python pythonQuery.py
    

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

Back to top