Introduction

Similarly to a previous blog post on how to connect SAP Cloud Platform to an on-premise JDBC databases we will show you, how to consume data from a JDBC-database (Oracle, in our case) running on-premise in a Java Application on a SAP Cloud Platform (Cloud Foundry) environment. A similar approach for MySQL (but on Neo) is described here:

https://blogs.sap.com/2018/02/06/how-to-set-up-a-jdbc-connection-using-the-cloud-connector/

In case you need more technical information or background on the connectivity Service or the SOCKS-Proxy provided by SAP please read this blog post first. We assume basic knowledge of the SAP Cloud Platform, e.g. you already know what the Cloud Connector is and how it is configured.

The (original) plan

We didn’t find any information on how to connect to an Oracle database and/or JDBC connections with Cloud Foundry instead of Neo. As we didn’t want to drill a hole in our firewall we decided to use the Cloud Connector with a SOCKS proxy. This lead us the original plan to just follow the same architecture as was described in the MySQL blog.

The plan (originaly from https://blogs.sap.com/2018/02/06/how-to-set-up-a-jdbc-connection-using-the-cloud-connector/)

The revised plan

The plan seemed pretty straightforward to us. As it turned out quite soon, what sounded quite easy turned out to be quite challenging. We had several brig walls, some of them quite large, to overcome.

Brig Wall No. 1: Oracle Thin Driver and JDBC – SOCKS the Oracle way

The first problem challenge we encountered was that the Oracle driver does not support SOCKS proxies anymore. A quick search on the internet, stack overflow and the other usual suspects all come up with the same answer: No longer working. Luckily, buried in some Oracle support forum, there was a solution. Setting the environment variable oracle.jdbc.javaNetNio to false turns off non-blocking IO, but turns on SOCKS support:

System.setProperty(“oracle.jdbc.javaNetNio”, “false”);

This innocent looking setting was hidden so well, that even Stack Overflow did not know it existed. With this property turned on, we could confirm localy that the SOCKS proxy is used.

Brig Wall No. 2: SOCKS – the SAP way

Now, what could be easier than activating the Connectivity service, read the proxy information from the environment and set the socksProxyHost and socksProxyPort respectively? Nothing to be worried about, we thought. What could have been easy, turned out to be (almost) impossible.

Instead of just providing a standard SOCKS5 proxy we got a something that needs a custom authorization procedure. How could we inject a custom authorization into the cloud foundry java environment or the JDBC driver? In the case of MySQL it would have been easy. As it has been shown in the respective blog post there you can inject a custom socket class. So, in case you are one of the lucky ones and just want to know how to connect an on-prem MySQL with Cloud Foundry that is the way to go. Everyone else, please keep on reading.

Now, what did we do to make the impossible possible? As we were not able to inject a custom Socket, we added a local socks5 proxy server which the sole purpose of forwarding the requests to the SAP Socks5 server on Cloud Foundry.

SAP provides some example code on how to authenticate to their SOCKS5 proxy. The authorization scheme, as well as the example code, can be found here:

https://help.sap.com/viewer/cca91383641e40ffbe03bdc78f00f681/Cloud/en-US/cd1583775afa43f0bb9ec69d9dbcc880.html

Information on how to get the token can be found here:

https://help.sap.com/viewer/cca91383641e40ffbe03bdc78f00f681/Cloud/en-US/313b215066a8400db461b311e01bd99b.html#loio313b215066a8400db461b311e01bd99b__section_HttpProxy

We will use the ConnectivitySocks5ProxySocket class. A quick test showed that we can indeed connect with it to the Oracle database.

So, next is to get a SOCKS5 proxy. SOCKS5 is a very simple protocol. It is quite easy to write a simple socks5 proxy that does not need to support much more than the connect method. As an alternative one can use an available (open source) proxy server and work with one of them. As we already have a java socket implementation in this small blog post we use jsocks (https://github.com/kruton/jsocks). JSocks allows to setup a “good-enough” socks5 server with just a few lines of code

final IdentAuthenticator auth = new IdentAuthenticator();

final ProxyServer server = new ProxyServer(auth);
try {
  server.start(9999,5, InetAddress.getByName("localhost"));
} catch (UnknownHostException e) {
  e.printStackTrace();
}

Please ensure that the proxy server is not accessible through the world wide web in case you encapsulte it in some service.

What is left now is to implement forwarding the traffic from this proxy server to SAP socks5 proxy: JSocks provides a mechanism for that called proxy chaining. With proxy chaining one could forward the traffic from one proxy to the other. As our proxy has a non-standard way of authentication and we already have a working Socket class, for the sake of simplicity, we just inject that one into the existing jsocks proxy.

For JSocks to work with the SAP proxy just a few little line needs to be changed: The original onConnect for JSocks looks like this

private void onConnect(ProxyMessage msg) throws IOException {
  […]
  Socket s;
  […]
  s = new Socket(msg.ip,msg.port);

here we can inject our ConnectivitySocks5ProxySocket. Changing the code as follows will make jsocks connect to our proxy:

s = new ConnectivitySocks5ProxySocket(jwtToken, sccLocationId);
s.connect(msg.ip, msg.port)

Please be aware that all your traffic now goes through the SOCKS5 tunnel. Therefore we need to whitelist all non on-premise traffic. This could be achieved via custom code, e.g. in the ConnectivitySocks5ProxySocket or by whitelisting it in the environment variable socksNonProxyHosts

System.setProperty("socksNonProxyHosts", 
  "connectivityproxy.internal.cf.eu10.hana.ondemand.com|[…]|localhost|127.0.0.1");

Turning on your socks proxy works the same way

System.setProperty("socksProxyHost", "localhost");
System.setProperty("socksProxyPort", "9999");

Brig Wall No. 3: Public DNS lookup – Oracle

Now, there is one small problem left. We cannot use virtual host names with the Oracle JDBC driver as it does a public DNS request first and, if not found, it exits with an error message “host not found”. There are several possible solutions to this:

• Make the public DNS point to “something” (not necessaringly the real server)
• Choose virtual server names that point to something (e.g. we successfully used sap.com as virtual server name, representing some internal oracle database)
• Use IP addresses only

We ended up using IP addresse, but the other two ways should work as well.

Putting it all together

If you followed all the steps in this blog you should now be able to connect an oracle database via SOCKS5 to an cloud foundry jdbc application. The new architecture shown in the following diagram:

As part of this blog we focused on the overall architecture and the java implementation. We did not show all the necessary configuration steps. Some example on how to setup cloud connector for TCP connection can be found in the following blog:

Cloud Integration – How to Connect to an On-Premise sftp server via Cloud Connector

We hope that you enjoyed reading this little blog post. We have shown that, using some twists here and there, it is possible to run arbitrary libraries through socks5, even when you do not have the source code to implement the custom authorization.