Skip to Content
Technical Articles
Author's profile photo Matthias Thoma

Connecting SAP Cloud Platform with an on-prem Oracle: The SOCKS way…

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.

Assigned Tags

      9 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Junxing He
      Junxing He

      Hi Mandy

      I am I am very interested in this blog.

      I try deploy the jsocks &  webapp to scp(two standrd apps),but webapp respones error Can't connect to SOCKS proxy:Connection refused (Connection refused)], -813 .

      What should I pay attention to?

       

      Best regards,
      Nick

      Author's profile photo Junxing He
      Junxing He

      I sloved with integrate jsock and webapp together.This Blog is very usefull

      Author's profile photo GIULIANO BELL
      GIULIANO BELL

      Hi Junxing. I'm working on a POC that has the same pattern, and we're facing the same issues that you solved by integrating the proxy and the webapp.

      Can you please share with me yoursolution ?

      Thanks in advance,

      cheers,

      Giuliano

      giuliano.bell@sap.com

      Author's profile photo Matthias Thoma
      Matthias Thoma
      Blog Post Author

      Hello Giuliano

      Did you solve the problem in the mean time? It is no problem to run jsocks and the webapp together. All you need to do is to follow the steps. It should still work. At which point do you have problems? It also depends a little bit on your oracle settings. In one setup we had to convince Java to support some fake DNS entries...

      If just SAP Cloud Platform would provide a proper Socks proxy... 🙂

      -  Matthias

      Author's profile photo ROBERTO URBAN
      ROBERTO URBAN

      Hi Matthias,

      Giuliano and me are working together to the same PoC.

      What we've done was import the jsock classed in our servlet project, change the method onConnect of the class ProxyServer in order to instanciate ConnectivitySocks5ProxySocket instead of Socket, as you stated in your blog.

      Then, in the method doGet of our servlet, we create an instance of such a modified class, using the same snippet you reported in the blog, the we put the code for setting up the jdbc connection, using such a proxy to define the connection parameters.

      Once deployed the servlet, as soon as we run it, the servlet creates the instance of the ProxyServer and then hangs. The servlet thread seems to be totally busy in waiting any connection coming from the outside. This implies neither the code to setup the JDBC connection nor the code to run the query is executed

      So we're stuck since we don't understand how to mix up the servlet code with the jsock proxy creation and usage.

      Don't you have any project you can share to better understand the full structure?

      BR
      Roberto

      Author's profile photo ROBERTO URBAN
      ROBERTO URBAN

      Hi Matthias,

      would you be so kind to share the full code?

      I don't really understand how to use jsocks + ConnectivitySocks5ProxySocket + oracle drivers to execute the DB call

       

      many thanks

      BR

      Roberto

      Author's profile photo Alfredo Semeco Blanco
      Alfredo Semeco Blanco

      Hi Matthias Thoma

      how are you?

      I need your help. I have implemented  the oficial SAP solution SOCK5 CF in Java app. I have deployed the app  in my Cloud foundry trial account. My app needs to connect to SQL Server DB Service On-premise ( The system is exposed in SAP Cloud Connector that i have donwloaded and installed). All connection socket5 (JWT, ID SCC) it's done correctly but when finally start the connection with DB SQL Server onpremise, always  i havethe next  excepction:

       

      com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host 127.0.0.1, port 1433 has failed. Error: "Connection refused (Connection refused). Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.

      Note: The SQL Server has all port TCP OK(1433), I have not FIREWALL and anything. My SQL Server is free in security.

       

      I don't know why?, if the SOCKET connections it's fine. I think  that is because i'm using a trial CF o Cloud Connector ?. I really i don't know

      please you help, i hope your help!

      Thanks

       

      This is the oficial class to implement Java Connection with my DB SQL Server onpremise system (exposed in SCC).

      import java.io.ByteArrayOutputStream; import java.io.IOException; import java.io.InputStream; import java.io.OutputStream; import java.net.InetAddress; import java.net.InetSocketAddress; import java.net.Socket; import java.net.SocketAddress; import java.net.SocketException; import java.nio.ByteBuffer; import java.util.Base64; // or any other library for base64 encoding import org.json.JSONArray; // or any other library for JSON objects import org.json.JSONObject; // or any other library for JSON objects import org.json.JSONException; // or any other library for JSON objects public class ConnectivitySocks5ProxySocket extends Socket { private static final byte SOCKS5_VERSION = 0x05; private static final byte SOCKS5_JWT_AUTHENTICATION_METHOD = (byte) 0x80; private static final byte SOCKS5_JWT_AUTHENTICATION_METHOD_VERSION = 0x01; private static final byte SOCKS5_COMMAND_CONNECT_BYTE = 0x01; private static final byte SOCKS5_COMMAND_REQUEST_RESERVED_BYTE = 0x00; private static final byte SOCKS5_COMMAND_ADDRESS_TYPE_IPv4_BYTE = 0x01; private static final byte SOCKS5_COMMAND_ADDRESS_TYPE_DOMAIN_BYTE = 0x03; private static final byte SOCKS5_AUTHENTICATION_METHODS_COUNT = 0x01; private static final int SOCKS5_JWT_AUTHENTICATION_METHOD_UNSIGNED_VALUE = 0x80 & 0xFF; private static final byte SOCKS5_AUTHENTICATION_SUCCESS_BYTE = 0x00; private static final String SOCKS5_PROXY_HOST_PROPERTY = "onpremise_proxy_host"; private static final String SOCKS5_PROXY_PORT_PROPERTY = "onpremise_socks5_proxy_port"; private final String jwtToken; private final String sccLocationId; public ConnectivitySocks5ProxySocket(String jwtToken, String sccLocationId) { this.jwtToken = jwtToken; this.sccLocationId = sccLocationId != null ? Base64.getEncoder().encodeToString(sccLocationId.getBytes()) : ""; } protected InetSocketAddress getProxyAddress() { try { JSONObject credentials = extractEnvironmentCredentials(); String proxyHost = credentials.getString(SOCKS5_PROXY_HOST_PROPERTY); int proxyPort = Integer.parseInt(credentials.getString(SOCKS5_PROXY_PORT_PROPERTY)); return new InetSocketAddress(proxyHost, proxyPort); } catch (JSONException ex) { throw new IllegalStateException("Unable to extract the SOCKS5 proxy host and port", ex); } } private JSONObject extractEnvironmentCredentials() throws JSONException { JSONObject jsonObj = new JSONObject(System.getenv("VCAP_SERVICES")); JSONArray jsonArr = jsonObj.getJSONArray("connectivity"); return jsonArr.getJSONObject(0).getJSONObject("credentials"); } @Override public void connect(SocketAddress endpoint, int timeout) throws IOException { super.connect(getProxyAddress(), timeout); OutputStream outputStream = getOutputStream(); executeSOCKS5InitialRequest(outputStream); executeSOCKS5AuthenticationRequest(outputStream); executeSOCKS5ConnectRequest(outputStream, (InetSocketAddress) endpoint); } private void executeSOCKS5InitialRequest(OutputStream outputStream) throws IOException { byte[] initialRequest = createInitialSOCKS5Request(); outputStream.write(initialRequest); assertServerInitialResponse(); } private byte[] createInitialSOCKS5Request() throws IOException { ByteArrayOutputStream byteArraysStream = new ByteArrayOutputStream(); try { byteArraysStream.write(SOCKS5_VERSION); byteArraysStream.write(SOCKS5_AUTHENTICATION_METHODS_COUNT); byteArraysStream.write(SOCKS5_JWT_AUTHENTICATION_METHOD); return byteArraysStream.toByteArray(); } finally { byteArraysStream.close(); } } private void assertServerInitialResponse() throws IOException { InputStream inputStream = getInputStream(); int versionByte = inputStream.read(); if (SOCKS5_VERSION != versionByte) { throw new SocketException(String.format("Unsupported SOCKS version - expected %s, but received %s", SOCKS5_VERSION, versionByte)); } int authenticationMethodValue = inputStream.read(); if (SOCKS5_JWT_AUTHENTICATION_METHOD_UNSIGNED_VALUE != authenticationMethodValue) { throw new SocketException(String.format("Unsupported authentication method value - expected %s, but received %s", SOCKS5_JWT_AUTHENTICATION_METHOD_UNSIGNED_VALUE, authenticationMethodValue)); } } private void executeSOCKS5AuthenticationRequest(OutputStream outputStream) throws IOException { byte[] authenticationRequest = createJWTAuthenticationRequest(); outputStream.write(authenticationRequest); assertAuthenticationResponse(); } private byte[] createJWTAuthenticationRequest() throws IOException { ByteArrayOutputStream byteArraysStream = new ByteArrayOutputStream(); try { byteArraysStream.write(SOCKS5_JWT_AUTHENTICATION_METHOD_VERSION); byteArraysStream.write(ByteBuffer.allocate(4).putInt(jwtToken.getBytes().length).array()); byteArraysStream.write(jwtToken.getBytes()); byteArraysStream.write(ByteBuffer.allocate(1).put((byte) sccLocationId.getBytes().length).array()); byteArraysStream.write(sccLocationId.getBytes()); return byteArraysStream.toByteArray(); } finally { byteArraysStream.close(); } } private void assertAuthenticationResponse() throws IOException { InputStream inputStream = getInputStream(); int authenticationMethodVersion = inputStream.read(); if (SOCKS5_JWT_AUTHENTICATION_METHOD_VERSION != authenticationMethodVersion) { throw new SocketException(String.format("Unsupported authentication method version - expected %s, but received %s", SOCKS5_JWT_AUTHENTICATION_METHOD_VERSION, authenticationMethodVersion)); } int authenticationStatus = inputStream.read(); if (SOCKS5_AUTHENTICATION_SUCCESS_BYTE != authenticationStatus) { throw new SocketException("Authentication failed!"); } } private void executeSOCKS5ConnectRequest(OutputStream outputStream, InetSocketAddress endpoint) throws IOException { byte[] commandRequest = createConnectCommandRequest(endpoint); outputStream.write(commandRequest); assertConnectCommandResponse(); } private byte[] createConnectCommandRequest(InetSocketAddress endpoint) throws IOException { String host = endpoint.getHostName(); int port = endpoint.getPort(); ByteArrayOutputStream byteArraysStream = new ByteArrayOutputStream(); try { byteArraysStream.write(SOCKS5_VERSION); byteArraysStream.write(SOCKS5_COMMAND_CONNECT_BYTE); byteArraysStream.write(SOCKS5_COMMAND_REQUEST_RESERVED_BYTE); byte[] hostToIPv4 = parseHostToIPv4(host); if (hostToIPv4 != null) { byteArraysStream.write(SOCKS5_COMMAND_ADDRESS_TYPE_IPv4_BYTE); byteArraysStream.write(hostToIPv4); } else { byteArraysStream.write(SOCKS5_COMMAND_ADDRESS_TYPE_DOMAIN_BYTE); byteArraysStream.write(ByteBuffer.allocate(1).put((byte) host.getBytes().length).array()); byteArraysStream.write(host.getBytes()); } byteArraysStream.write(ByteBuffer.allocate(2).putShort((short) port).array()); return byteArraysStream.toByteArray(); } finally { byteArraysStream.close(); } } private void assertConnectCommandResponse() throws IOException { InputStream inputStream = getInputStream(); int versionByte = inputStream.read(); if (SOCKS5_VERSION != versionByte) { throw new SocketException(String.format("Unsupported SOCKS version - expected %s, but received %s", SOCKS5_VERSION, versionByte)); } int connectStatusByte = inputStream.read(); assertConnectStatus(connectStatusByte); readRemainingCommandResponseBytes(inputStream); } private void assertConnectStatus(int commandConnectStatus) throws IOException { if (commandConnectStatus == 0) { return; } String commandConnectStatusTranslation; switch (commandConnectStatus) { case 1: commandConnectStatusTranslation = "FAILURE"; break; case 2: commandConnectStatusTranslation = "FORBIDDEN"; break; case 3: commandConnectStatusTranslation = "NETWORK_UNREACHABLE"; break; case 4: commandConnectStatusTranslation = "HOST_UNREACHABLE"; break; case 5: commandConnectStatusTranslation = "CONNECTION_REFUSED"; break; case 6: commandConnectStatusTranslation = "TTL_EXPIRED"; break; case 7: commandConnectStatusTranslation = "COMMAND_UNSUPPORTED"; break; case 8: commandConnectStatusTranslation = "ADDRESS_UNSUPPORTED"; break; default: commandConnectStatusTranslation = "UNKNOWN"; break; } throw new SocketException("SOCKS5 command failed with status: " + commandConnectStatusTranslation); } private byte[] parseHostToIPv4(String hostName) { byte[] parsedHostName = null; String[] virtualHostOctets = hostName.split("\\.", -1); int octetsCount = virtualHostOctets.length; if (octetsCount == 4) { try { byte[] ipOctets = new byte[octetsCount]; for (int i = 0; i < octetsCount; i++) { int currentOctet = Integer.parseInt(virtualHostOctets[i]); if ((currentOctet < 0) || (currentOctet > 255)) { throw new IllegalArgumentException(String.format("Provided octet %s is not in the range of [0-255]", currentOctet)); } ipOctets[i] = (byte) currentOctet; } parsedHostName = ipOctets; } catch (IllegalArgumentException ex) { return null; } } return parsedHostName; } private void readRemainingCommandResponseBytes(InputStream inputStream) throws IOException { inputStream.read(); // skipping over SOCKS5 reserved byte int addressTypeByte = inputStream.read(); if (SOCKS5_COMMAND_ADDRESS_TYPE_IPv4_BYTE == addressTypeByte) { for (int i = 0; i < 6; i++) { inputStream.read(); } } else if (SOCKS5_COMMAND_ADDRESS_TYPE_DOMAIN_BYTE == addressTypeByte) { int domainNameLength = inputStream.read(); int portBytes = 2; inputStream.read(new byte[domainNameLength + portBytes], 0, domainNameLength + portBytes); } }
      Author's profile photo Gary Chen
      Gary Chen

      Hi @Matthias Thoma,

      I almost done all things follow your post, but got one issue, I used IP as Virtual host, but got issue "Failed connecting to remote socket. Exception: java.net.SocketException: SOCKS5 command failed with status: FORBIDDEN"

      SOCKS proxy to on-premise Oracle

      Best Regards,

      Gary

      Author's profile photo Manjunath J P
      Manjunath J P

      ​Hi Matthias Thoma,

      Thanks for your article..

      We are also trying to do same thing. Can you please share your sample code ? It will be very helpfull

      Thanks and regards,

      Manjunath J P