Skip to end of metadata
Go to start of metadata

Purpose

Step by step description to show end to end configuration to connect JDBC on premise adapter to remote HANA Database where it is required to enforce SSL client connections on the HANA Database.

To setup the connection the high-level steps are described in the KBA 2965415 - How to Connect from SAP PO to SAP HANA Database.


1: Get HANA Driver file ngdbc.jar. To get the HANA ngdbc.jar you need to download the HANA Client drivers.  To download and extract the HANA Client driver please follow the steps in the

KBA 2970243 - How to download and install the SAP HANA JDBC and ODBC Drivers? For information on HANA Client versions that are supported for your operating system please see the

SAP note 3006307 - SAP HANA Client Supported Platforms for 2.7 and higher

The ngdbc.jar file is located here:


You can extract the file to a JDBC folder as shown below or any location that you want:


2: Before deploying the ngdbc.jar driver on the PI/PO system it is important to check what drivers are deployed on the PI/PO system already. You can check this as per the note

1829286 - Checking the drivers installed for the JMS and JDBC Adapters. In this case there are no drivers deployed:

IMPORTANT: If other drivers were already deployed to avoid overwriting then please follow the KBA 1816456 - Deploying new JMS/JDBC drivers overwrites existing deployed drivers in PI 


3: Detailed information on deploying the driver can be found in the guided answer 2513069 Guided Answers - How to deploy the external drivers for Axis/JDBC/JMS Adapter.


4: You can use the KBA 1829286 - Checking the drivers installed for the JMS and JDBC Adapters, to confirm that the driver was successfully deployed:


5: Then to complete the connection you need to configure the connection parameters in the PI/PO JDBC Adapter as per the SAP Help documentation on Configuring the JDBC Adapter:

The connection string should be in the format:

jdbc:sap://<server>:<port>[/?<options>]

When connecting to an SAP HANA database using JDBC, there are several connection properties that you can use in the connection string.

To be able to connect to a SAP HANA Remote Database via JDBC adapter where SSL is enforced you must add the connection property ‘encrypt=true’ as shown for the connection string here:

SSL configuration on SAP HANA Database side:

The steps to enable HANA SSL on the HANA Database side can be found in the SAP note 1718944 - SAP HANA DB: Securing External SQL Communication (CommonCryptoLib).

KBA 2487639 - HANA Basic How-To Series - HANA and SSL - LEAD KBA and the referenced documentation have more detailed information on securing the SYSTEM DB, Tenant DB’s and other clients like

HANA Studio and applications that use JDBC/ODBC to connect securely to the HANA Database.

The screenshots below are based mainly on the steps from the note 1718944 - SAP HANA DB: Securing External SQL Communication (CommonCryptoLib) and show how to create a

PSE (Personal Security Environment) and certification request:

Determine the fully qualified hostname of the host running your SAP HANA Database. Execute as any operating system user command hostname -f:

Use sapgenpse tool to create a PSE and a Certification Request. In this example PSE is called test3.pse and the certification request has the name test3.req:     

 sapgenpse gen_pse -p test3.pse -r test3.req

The certification request (test3.req) needs to be signed by a certification authority (CA).

Once you have the signed certificate response from your certification authority, you need to import it into your PSE. Assuming the response file is called ‘test3.crt’ command would be:

sapgenpse import_own_cert -p test3.pse -c test3.crt

You can do validation checks on the certificate as per note 2487731 - HANA Basic How-To Series - HANA and SSL - Securing ODBC / JDBC connections - using sapgenpse (Microsoft CA edition) – SYSTEMDB:

You can display and validate the certificate using the following commands:

sapgenpse get_my_name -p test3.pse

sapgenpse maintain_pk -p test3.pse -t show

public_hostname_resolution

It is important that parameter global.ini -> public_hostname_resolution -> use_default_route is set to "FQDN" or a virtual hostname, see the

KBA 2846931 - HANA Basic How-To Series - HANA and SSL - establishing a secured ODBC/JDBC connection using HANA Studio fails with a reply of the IP Address instead of DNS, for further information.

Command to update the parameter if required:

ALTER SYSTEM ALTER CONFIGURATION ('global.ini', 'SYSTEM') SET ('public_hostname_resolution', 'use_default_route') = 'FQDN' WITH RECONFIGURE;

in-database certificate stores

For the reasons given in the note 2175664 - Migration of file system based X.509 certificate stores to in-database certificate stores, it is recommended to migrate the file system based certificate stores to in-database certificate stores.

To do this follow the steps in the KBA 2846882 - HANA Basic How-To Series - HANA and SSL - Migrating the sapsrv.pse certificates into the Database (SYSTEM DB & TENANTS):

Open KBA 2175664 - Migration of file system based X.509 certificate stores to in-database certificate stores and download the attached file "extract_certificates.py" to the HANA Server:

Extract the certificates from the .pse file you want to migrate. In this case the PSE is called ‘test3.pse’:

Follow the steps 3 and 4 from the KBA 2846882 - HANA Basic How-To Series - HANA and SSL - Migrating the sapsrv.pse certificates into the Database (SYSTEM DB & TENANTS),

to create the in-database certificate store PSE and import the certificates.

You can then use the below commands to view the certificates and the content of the in-database certificate store PSEs:

select * from certificates;


select * from pse_certificates;


As soon as an In-Database Certificate is in place, HANA will use it and will ignore the PSE file.


Import the Certificate Chain into the cacerts file of the JVM:

So that the JDBC adapter Database connection to SAP HANA Database is trusted you need to import the certificate chain into the cacerts file on the JVM that the PI/PO system runs on.

The steps required to do this are documented in point 5 from the resolution part of the KBA 2581143 - How to connect the AS Java to the database using SSL:

In this example the JVM locations that we need to update are:

D:\usr\sap\SID\SYS\exe\jvm\NTAMD64\sapjvm_8.1.068\sapjvm_8\jre\lib\security\

And

D:\usr\sap\SID\J00\exe\sapjvm_8\jre\lib\security\cacerts

We first need to copy our certificate Chain file (test3.crt) to the bin directory where the keytool is located, in our case this is:

D:\usr\sap\SID\SYS\exe\jvm\NTAMD64\sapjvm_8.1.068\sapjvm_8\bin

CD to the directory where the keytool is stored : D:\usr\sap\SID\SYS\exe\jvm\NTAMD64\sapjvm_8.1.068\sapjvm_8\bin

Execute the command:

keytool -importcert -file test3.crt -keystore D:\usr\sap\SID\SYS\exe\jvm\NTAMD64\sapjvm_8.1.068\sapjvm_8\jre\lib\security\cacerts

You will be asked to enter the keystore password which by default is ‘changeit’:

You are then asked if you trust the certificate, enter ‘yes’ and Certificate is successfully added:

Execute the same steps to add the certificate chain to the second JVM location which in our case is:

D:\usr\sap\SID\J00\exe\sapjvm_8\jre\lib\security\cacerts


Test JDBC Adapter connection to the SAP HANA Database:

To test the JDBC adapter connection to the SAP HANA Database:

Goto 'Communication Channel' > Open Channel Monitoring':

Enter User and Password:

Click on ‘Ping Channel’:

JDBC Adapter connection test is successful:

Troubleshooting:

Error message:

Access is denied’ error message when trying to update the cacerts file in the JVM locations:

Cause:

In this case the file name ‘cacerts’ is missing in the keytool command used:

Solution:

Correct command to use in this case is:

keytool -importcert -file test3.crt -keystore D:\usr\sap\SID\SYS\exe\jvm\NTAMD64\sapjvm_8.1.068\sapjvm_8\jre\lib\security\cacerts


Error message:

The connection test for the JDBC adapter with SSL enforced on SAP HANA Database fails with:

Cause:

Certificate chain has not been added to JVM locations:

Solution:

Follow the steps documented in point 5 from the resolution part of the KBA 2581143 - How to connect the AS Java to the database using SSL.






  • No labels