Connect to SAP HANA with a Secure Connection from Python
- 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
- Install required clients through the SAP HANA Client Installation and Update Guide.
- An SAP HANA instance
- Python 3 installed
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.
If you are using HANA Cloud, you can find the endpoint information in the SAP Cloud Platform Cockpit.
If you are using SAP HANA, express edition, the host and port by default are
hexehost
and 39015.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.
PythonCopyfrom 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 usesmscrypto
by default and can be configured to usecommoncrypto
. Linux and Mac useopenssl
by default and can be configured to usecommoncrypto
.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.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',
- 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 sayskeycaption: 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 apse
file containingDigiCertAssuredIDRootCA
.- 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).
ShellCopySAPCAR -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
.ShellCopyc:\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.
ShellCopyecho %SECUDIR% c:\SAP\hdbclient\
Use the following command to generate a
keystore
. Note, additional details can be seen by adding the -log option.ShellCopysapgenpse 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.
ShellCopysapgenpse 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:
ShellCopysapgenpse maintain_pk -p "%SECUDIR%/sapcli.pse% -l
You are now ready to use the SAP Common Crypto Library instead of OpenSSL.
Change thesslCryptoProvider
to becommoncrypto
and run the test app.
Optionally, the location for thesapcli.pse
file can be specified via thesslKeyStore
setting.ShellCopypython pythonQuery.py
You have now connected securely to HANA using multiple cryptographic providers.
- Download the SAP CRYPTOGRAPHIC SOFTWARE from SAP Software Downloads which contains the