Authenticate to SAP HANA Cloud using X.509

The SAP HANA Client can use different cryptographic service providers on the operating systems shown below. The steps will vary slightly depending on the operating system and security library used.

Before proceeding, ensure that basic connectivity is working with a user name and password. The steps below check the version of the SAP HANA Client, creates a test user, and attempts to connect using basic authentication with mscrypto or OpenSSL. The SQL commands can be run by an admin user such as DBADMIN in the SAP HANA database explorer or hdbsql.

1. Check the version of the SAP HANA Client.

   Shell

   Copy

   hdbsql -v 
   

   

2. Create a user to attempt to connect with.

   SQL

   Copy

   CREATE USER TESTUSER PASSWORD Password1 NO FORCE_FIRST_PASSWORD_CHANGE SET USERGROUP DEFAULT;
   

   

3. Attempt to connect with hdbsql using basic authentication.

   Shell (Microsoft Windows)

   Copy

   hdbsql -j -A -sslprovider mscrypto -u TESTUSER -p Password1 -Z traceFile=stdout -Z traceOptions=debug=warning,flush=on -n xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx.hana.trial-us10.hanacloud.ondemand.com:443 "SELECT CURRENT_USER, CURRENT_SCHEMA FROM DUMMY;"
   

   Shell (Linux or macOS)

   Copy

   hdbsql -j -A -sslprovider openssl -u TESTUSER -p Password1 -Z traceFile=stdout -Z traceOptions=debug=warning,flush=on -n xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx.hana.trial-us10.hanacloud.ondemand.com:443 "SELECT CURRENT_USER, CURRENT_SCHEMA FROM DUMMY;"
   

   

Perform the following steps in your selected environment. The result will be a directory named certs containing a private key and a public certificate for a new certificate authority. In a non demo environment, a trusted certification authority could instead be used.

Any environment with SAP HANA Client installed and that contains a recent OpenSSL version should work.

This tutorial used a Git Bash shell on Microsoft Windows. On Microsoft Windows, a forward slash may need to be entered twice such as -subj "//CN=DEMO_ROOT_CERT_AUTH".

1. Verify that OpenSSL is installed.

   Shell

   Copy

   openssl version
   

   If OpenSSL is installed a value such as “OpenSSL 1.1.1l 24 Aug 2021” will be shown.

   LibreSSL will appear instead of OpenSSL when using macOS.

2. Create a folder where the generated certificates will be stored. Create a private key and public certificate for a self-signed certificate authority.

   Shell

   Copy

   mkdir ~/certs
   cd ~/certs
   openssl genrsa -out demorootca.key 4096
   openssl req -x509 -new -nodes -key demorootca.key -sha256 -days 365 -subj "/CN=DEMO_ROOT_CERT_AUTH/ST=ON" -out demorootca.crt
   ls -al
   

   

   Further details on OpenSSL commands can be found at OpenSSL commands.

1. Create a private user key and a certificate signing request.

   Shell

   Copy

   openssl genrsa -out test_x509_user.key 2048
   openssl req -new -nodes -key test_x509_user.key -out test_x509_user.csr -subj "/CN=TESTX509"
   ls -al
   

   

2. Create a client certificate that has been signed by the certificate authority.

   Shell

   Copy

   openssl x509 -req -in test_x509_user.csr -CA demorootca.crt -CAkey demorootca.key -CAcreateserial -days 1 -out test_x509_user.crt
   ls -al
   

   

3. Perform the following steps in your selected environment to verify that the user certificate has been signed by the certificate authority.

   Shell

   Copy

   openssl verify -CAfile demorootca.crt test_x509_user.crt
   

4. Display the client certificate using Openssl and by examining the certificate once imported into SAP HANA.

   Use openSSL to display the certificate.

   Shell

   Copy

   openssl x509 -text -noout -in test_x509_user.crt
   

   

   Import the certificate into SAP HANA and view the certificate.

   SQL

   Copy

   CREATE CERTIFICATE X509_USER_CERT FROM '-----BEGIN CERTIFICATE-----
   MIIDuDCCAaACFD8uRrS2MgGqakcJZ6bPOyl7f5NfMA0GCSqGSIb3DQEBCwUAMB4x
   ...
   eHDFB5AyicWtbXoqVohrcv1QIG9S5eFP3ub5ijruUatC0UTVRyWMLjZtvDY=
   -----END CERTIFICATE-----';
   SELECT CERTIFICATE_NAME, SUBJECT_DISTINGUISHED_NAME, ISSUER_DISTINGUISHED_NAME, ISSUER_COMMON_NAME FROM CERTIFICATES WHERE CERTIFICATE_NAME='X509_USER_CERT';
   --DROP CERTIFICATE X509_USER_CERT;  --This cert is only imported so we can view it.  It can be dropped at any time 
   

   

   Notice that the value of the ISSUER_DISTINGUISHED_NAME is slightly different than what was specified when the certificate authority was originally created. The State or ST value is shown as SP. Additional details on this can be found at SAP Note: 2094102 - Certificate DName attributes mapping between RFC 2256 and the CommonCryptoLib. This becomes important in step 6 when an X.509 certificate provider is created.

The details of the client certificate needed for authentication will be stored in a PEM file when using OpenSSL or a Personal Security Environment (PSE) file when using SAP CommonCryptoLib. A PSE can either be file based or be an object in the SAP HANA database. The SAP HANA Client needs to be able to access certificate details when connecting to the database and hence needs the certificate details to be stored in a file.

cat test_x509_user.key test_x509_user.crt demorootca.crt > test_x509_user.pem

The below step is only required when SAP CommonCryptoLib is used.

Shell

Copy

sapgenpse import_p8 -p ~/certs/test_x509_user.pse -x "" ~/certs/test_x509_user.pem

Additional details on sapgenpse can be found by entering sapgenpse -h or sapgenpse import_p8 -h.

Execute the following SQL to create a certificate from the self-signed certificate authority.

CREATE CERTIFICATE DEMOROOTCA_CERT FROM '';  --Contents of cat demorootca.crt 
--It may also be of interest to import the test_x509_user.crt file and view its contents with the query below.
SELECT CERTIFICATE_NAME, SUBJECT_DISTINGUISHED_NAME, ISSUER_DISTINGUISHED_NAME, ISSUER_COMMON_NAME  FROM CERTIFICATES WHERE CERTIFICATE_NAME = 'DEMOROOTCA_CERT';

details of the DEMOROOTCA_CERT

Further details can be found at CREATE CERTIFICATE Statement.

Execute the following SQL.

CREATE X509 PROVIDER DEMO_X509_PROVIDER WITH ISSUER 'SP=ON, CN=DEMO_ROOT_CERT_AUTH';
SELECT * FROM X509_PROVIDERS;
CREATE PSE X509_PSE;
SELECT * FROM PSES;
ALTER PSE X509_PSE ADD CERTIFICATE DEMOROOTCA_CERT;
SELECT * FROM PSE_CERTIFICATES;
SET PSE X509_PSE PURPOSE X509 FOR PROVIDER DEMO_X509_PROVIDER;
SELECT * FROM PSES;

There must be a match between the issuer value in the X509 provider and the ISSUER_DISTINGUISHED_NAME of the client certificate. The needed value was shown at the end of step 3.

Further details can be found at CREATE X509 PROVIDER Statement and CREATE PSE statement.

Execute the following SQL to create a database user.

CREATE USER TESTX509_TECHNICAL WITH IDENTITY 'CN=TESTX509' FOR X509 PROVIDER DEMO_X509_PROVIDER SET USERGROUP DEFAULT;

The above is known as an explicit mapping. The common name or CN value in the user matches to CN value in the client certificate. It is also possible to use matching rules specified on the provider.

Further details can be found at CREATE USER Statement.

Use hdbsql to connect to a HANA instance using a certificate. Adjust the location of the test_x509_user.pem and the host and port values accordingly.

hdbsql -j -A -sslprovider openssl -Z authenticationMethods=x509 -Z authenticationX509=test_x509_user.pem -Z traceFile=stdout -Z traceOptions=debug=error,flush=on -n xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx.hana.trial-us10.hanacloud.ondemand.com:443  "SELECT CURRENT_USER, CURRENT_SCHEMA FROM DUMMY;"

Some of the used connection options are summarized in the table below. Further details can be found at SAP HANA HDBSQL Options and SQLDBC Connection Properties

1. A few steps are required to enable the usage of CommonCryptoLib such as the setting of an environment variable named SECUDIR to the location of the SAP HANA Client install and the creation of a personal security environment file that contains the root certificate of the SAP HANA Cloud database. Follow the steps documented in the tutorial Create a User, Tables and Import Data Using SAP HANA HDBSQL (in a note in substep step 1) to use commoncrtypo.

2. Connect using CommonCryptoLib.

   Notice that the changes are to the parameters sslprovider and the pse is used rather than the pem for the authenticationX509 parameter.

   Shell

   Copy

   hdbsql -j -A -sslprovider commoncrypto -Z authenticationMethods=x509 -Z authenticationX509=~/certs/test_x509_user.pse -Z traceFile=stdout -Z traceOptions=debug=error,flush=on -n xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx.hana.trial-us10.hanacloud.ondemand.com:443  "SELECT CURRENT_USER, CURRENT_SCHEMA FROM DUMMY;"
   

   When using macOS, you may need to specify the full path to the PSE file instead of using ~.

   

1. Create a folder named nodeX509OpenSSL and enter the newly created directory.

   Shell (Microsoft Windows)

   Copy

   mkdir %HOMEPATH%\HANAClientsTutorial\nodeX509OpenSSL
   cd %HOMEPATH%\HANAClientsTutorial\nodeX509OpenSSL
   

   Shell (Linux or macOS)

   Copy

   mkdir $HOME/HANAClientsTutorial/nodeX509OpenSSL
   cd $HOME/HANAClientsTutorial/nodeX509OpenSSL
   

2. Initialize the project and install the hana-client driver from NPM.

   Shell

   Copy

   npm init -y
   npm install @sap/hana-client
   

3. Open a file named nodeQuery.js in an editor.

   Substitute pico or notepad below for your preferred text editor.

   Shell (Microsoft Windows)

   Copy

   notepad nodeQuery.js
   

   Shell (Linux or macOS)

   Copy

   pico nodeQuery.js
   

4. Add the code below to nodeQuery.js.

   JavaScript

   Copy

   'use strict';
   var util = require('util');
   var hana = require('@sap/hana-client');
   
   var connOptions = {
       //Option 1, retrieve the connection parameters from the hdbuserstore
       serverNode: '@X509UserKey',  //host, port, authenticationMethod, and pem retrieved from hdbuserstore
   
       //Option 2, specify the connection parameters rather than use the values from a key
       //serverNode: 'host:port',
       //authenticationX509: '~/certs/test_x509_user.pem', //.pem if using OpenSSL and .pse  if using commoncrypto
       //authenticationX509: 'C:\\Users\\xxx\\certs\\test_x509_user.pse',  //Microsoft Windows
   
   
       //sslValidateCertificate: 'false',  //Must be set to false when connecting to an on-premise instance that uses a self-signed certificate.
       //traceFile: 'stdout',
       //traceOptions: 'debug=error,flush=on',
       sslCryptoProvider: 'openssl',  //openssl or commoncrypto
       authenticationMethods: 'x509'
   };
   
   var connection = hana.createConnection();
   
   connection.connect(connOptions);
   
   var sql = 'select CURRENT_USER FROM DUMMY;';
   var result = connection.exec(sql);
   console.log(util.inspect(result, { colors: false }));
   connection.disconnect();
   

   The values for host, port, and pem can be retrieved from an hdbuserstore key X509UserKey or they can be specified in the application.

   Shell

   Copy

   hdbuserstore SetX509 X509UserKey xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx.hana.trial-us10.hanacloud.ondemand.com:443 ~/certs/test_x509_user.pem
   hdbuserstore list
   

5. Run the app.

   Shell

   Copy

   node nodeQuery.js
   

   

6. Should you wish to use SAP CommonCryptoLib, make sure the changes in the previous step to set were completed and then change the parameter sslCryptoProvider from openssl to commoncrypto and the authenticationX509 parameter to point to the pse file.

Optionally execute the following statements to undo the steps in this tutorial.

DROP USER TESTX509_TECHNICAL;
DROP PSE X509_PSE;
DROP CERTIFICATE DEMOROOTCA_CERT;
DROP X509 PROVIDER DEMO_X509_PROVIDER;

cd %HOMEPATH%/certs
del * /Q

cd ~/certs
rm *

Congratulations! You have now used an X.509 client certificate when connecting to an SAP HANA Cloud database.