Goal

I was in need to access my HANA On Premise instance and use native SQL commands within an Java App (NEO) on SAP Cloud Platform. To attach my HANA On Premise instance I thought of using SAPs Cloud Connector.

Trouble comes when considering the available Cloud Connector features. The needed tunnel direction to access my HANA instance on SQL level is from Cloud to On Premise and is not available right away.

That’s why I write here my personal solution and combined tricks as I didn’t found it anywhere documented so far.

May you find the steps helpful in your endeavor.

Overview

In summary, they are a few steps to be regarded:

1. Get the JDBC packaged into the WAR file via Maven
2. Configure the HANA On Premise access via TCPs on the Cloud Connector
3. Configure the SSL certificates on the Cloud Connector
4. Use the local SOCKS proxy to access the exposed SQL port

JDBC Driver

The JDBC driver for HANA is currently not hosted at any know public Maven Repository. Therefore, I needed to get this installed and managed by Maven locally.

Take the ngdbc.jar (part of the JDBC.TGZ) from the SAP HANA Client packages and add it to your local maven repository:

  mvn install:install-file -DgroupId=com.sap.db.jdbc -DartifactId=ngdbc -Dversion=2.1.2 -Dpackaging=jar -Dfile=ngdbc.jar -DgeneratePom=true

In your pom.xml files of your maven project you add your local repository:

 <repositories>
   <repository>
     <id>local-maven-repo</id>
     <url>file:///${project.parent.basedir}/lib</url>
   </repository>
 </repositories>

and add the dependency to jdbc driver:

  <dependencies>  
     <dependency>
        <groupId>com.sap.db</groupId>  
        <artifactId>ngdbc</artifactId>
        <version>2.1.2</version>
     </dependency>
  </dependencies>

Cloud Connector

As above mentioned, there is no dedicated option available to access an On Premise installed HANA database through via an SQL connection.

The technical protocol chosen is therefore quite generic and explains also the shown warning message. My settings are setup like this:

Take note of the fact that we use SSL secured TCP.

Setup Trust

As I’m connecting to an SSL secured endpoint, the SSL handshake must be successful. For this upload the certificate of your endpoint.

To get the certificate presented by the HANA SQL endpoint, I used OpenSSL:

  openssl s_client -connect my-real-onpremise-endpoint:30215

And saved the certificate to a text file and uploaded it into the Trust Store of the Cloud Connector:

Beware: The certificate presented must match correct endpoint configured in the Cloud Connector.

Connect through SOCKS

Instead of using the normal connection to the internet of an SCP java application, the connection to TCP resources tunneled via the Cloud Connector must be made via the included SOCKS proxy.

The pre-connection settings any proxy is currently not supported by the HANA JDBC Driver. Therefore, I took the solution to set the connection property directly:

 System.setProperty("socksProxyHost", "localhost"); 
 System.setProperty("socksProxyPort", "20004"); 
 System.setProperty("java.net.socks.username", auth);

The rest was well documented and therefore I don’t repeat it here.

Summary

These are in very short statement my approach to get SQL access from my SAP Cloud Platform Java App (NEO) to my HANA On Premise instance.

Find in the following the needed resources, documentation, and of course my coding.

Near Future

My colleagues are always working on new features and responded timely on the missing ngdbc feature. In near future (likely rev 2.00.021), the proxy parameter can be set directly on the jdbc connection and (avoiding any conflict of other proxy usages inside the same Java application).

jdbc:sap://virtual-hostname-for-hana:30215/?autocommit=false&
  proxyHostName=localhost&
  proxyPort=20004&
  proxyScpAccount=SUBACCOUNT.LOCATIONID

Resources

Documentation

• Generic Cloud Connector documentation
• Using TCP for Cloud Applications, explains the use of the SOCKS proxy
• Java Networking and Proxies

Software

• HANA JDBC Driver (called ngdbc.jar) is part of the HANA client installation
• Cloud Connector can be downloaded e.g. from SAP Development Tools
• OpenSSL binaries are part of many Linux distributions and there are also build for windows available, see OpenSSL.org

Example Coding

package com.sap.example;

import java.net.Authenticator;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Base64;

public class SCPAppToHANAOnPremise {

  public static String getResult(String filter) {

    setupSOCKS();

    try {
      Class.forName("com.sap.db.jdbc.Driver");
    } catch (ClassNotFoundException e) {
      System.out.println("Where is my JDBC Driver?");
      e.printStackTrace();
      return "Error";
    }

    Connection connection = null;
    try {
      System.out.println("Start");
      connection = DriverManager.getConnection(
        "jdbc:sap://virtual-hostname-for-hana:30215/?autocommit=false", "USER", "PASSWORD");
    } catch (SQLException e) {
      System.err.println("Connection Failed. Message: " + e.getMessage());
      return "Error";
    }
    if (connection != null) {
      try {
        System.out.println("Connection to HANA successful!");
        Statement stmt = connection.createStatement();
        ResultSet resultSet = stmt.executeQuery("select * from SCHEMA.TABLE where " + filter);
        while (resultSet.next()) {
          String id = resultSet.getString("COLUMN");
          System.out.println("Output: " + id );
          return id;
        }
      } catch (SQLException e) {
        System.err.println("Query failed! Message: " + e.getMessage());
      } finally {
        try {
          connection.close();
        } catch (SQLException e) {
          // TODO Auto-generated catch block
          e.printStackTrace();
          return "Error";
        }
      }
    }
    return "None";
  }

  private static void setupSOCKS() {

    String auth = setSOCKS5ProxyAuthentication("SUBACCOUNT", "LOCATIONID");

    System.setProperty("socksProxyHost", "localhost");
    System.setProperty("socksProxyPort", "20004");
    System.setProperty("java.net.socks.username", auth);

  }

  private static String setSOCKS5ProxyAuthentication(String subaccount, String locationId) {
    final String encodedSubaccount = new String(Base64.getEncoder().encodeToString(subaccount.getBytes()));
    final String encodedLocationId = new String(Base64.getEncoder().encodeToString(locationId.getBytes()));

    Authenticator.setDefault(new Authenticator() {
      @Override
      protected java.net.PasswordAuthentication getPasswordAuthentication() {
        return new java.net.PasswordAuthentication("1." + encodedSubaccount + "." + encodedLocationId,
            new char[] {});
      }
    });

    return "1." + encodedSubaccount + "." + encodedLocationId;
  }

}