Technology Blogs by SAP
Learn how to extend and personalize SAP applications. Follow the SAP technology blog for insights into SAP BTP, ABAP, SAP Analytics Cloud, SAP HANA, and more.
cancel
Showing results for 
Search instead for 
Did you mean: 
Rohit01
Employee
Employee
6,846

Introduction


This blog post is one possible way to connect to an on-premise database from SAP BTP via Cloud Connector. The code in this guide was originally implemented for a research project and is not production-grade.

 

The unique problem I faced while connecting to the on-premise database was that all the interactions with the database is be done over TCP and not over HTTP. This rules out the usage of SAP Cloud Platform Destination Service and calls for an implementation of SOCKS5 proxy to communicate via Cloud connector over TCP.

 

Pre-requisites:



  • SAP BTP

    • A dev space created to deploy the application

    • Minimum of 1GB application runtime allocated to the space

    • An instance of Connectivity Service

    • An instance of UAA service



  • On-Prem Database

    • Postgres is used for the purpose of this blog



  • SAP Cloud Connector

    • The on-premise database should be accessible from the Cloud Connector

    • The Cloud foundry sub-account should be added

    • A virtual host and virtual port mapped to the on-premise database



  • CF CLI installed on the machine



 

Language & Frameworks:



  • Java 8 and above

  • Spring Boot

  • Spring Cloud


 

Approach:


The java application will have a Connectivity Proxy(Socket) through which the TCP requests are transferred to the SAP CC.


Application on SAP Cloud Foundry connecting to on-premise Postgres via Cloud Connector


The above approach is inspired from this reference

 

Connectivity Proxy


The connectivity proxy class plays a pivotal role in being a bridge between the SAP Cloud Connector and the application. The proxy is an implementation of a socket which will be used by the JDBC connector to establish a connection to the database.

 

The proxy socket also works nicely on a cloud foundry account with multiple cloud connectors. Each cloud connector will have a unique location Id that can be passed to the constructor.

ConnectivitySOCKS5ProxySocket


There is detailed explanation of this implementation here
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
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.JSONException; // or any other library for JSON objects
import org.json.JSONObject; // 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 jwtToken4ConnectivityService, String sccLocationId) {
this.jwtToken = jwtToken4ConnectivityService;
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);
}
}
}

 

Implementation


Create springboot project with the following maven dependencies
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>

<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>

<!-- https://mvnrepository.com/artifact/org.springframework.cloud/spring-cloud-cloudfoundry-connector -->
<dependency>
<groupId>org.springframework.cloud</groupId>
<artifactId>spring-cloud-cloudfoundry-connector</artifactId>
<version>2.0.7.RELEASE</version>
</dependency>

<!-- https://mvnrepository.com/artifact/org.springframework.cloud/spring-cloud-spring-service-connector -->
<dependency>
<groupId>org.springframework.cloud</groupId>
<artifactId>spring-cloud-spring-service-connector</artifactId>
<version>2.0.7.RELEASE</version>
</dependency>

<!-- https://mvnrepository.com/artifact/org.json/json -->
<dependency>
<groupId>org.json</groupId>
<artifactId>json</artifactId>
<version>20210307</version>
</dependency>

 

Create a custom SocketFactory(DBSocketFactory) class extending javax.net.SocketFactory. 

This class overrides the createSocket method which does the following,

  • Generate a JWT token for the Connectivity Service instance

  • Create an instance of socket by passing the JWT token and the cloud connector location id(empty string if location id is not applicable) to the proxy socket class constructor


import java.io.IOException;
import java.net.InetAddress;
import java.net.Socket;
import java.net.UnknownHostException;

import javax.net.SocketFactory;

import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;
import org.springframework.http.HttpEntity;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpMethod;
import org.springframework.web.client.RestTemplate;

public class DBSocketFactory extends SocketFactory {

public DBSocketFactory() {
super();
}

@Override
public Socket createSocket() throws IOException {

JSONObject credentials = extractEnvironmentCredentials();
String client_id = credentials.getString("clientid");
String client_secret = credentials.getString("clientsecret");
String tokenUrl = credentials.getString("url") + "/oauth/token?grant_type=client_credentials";

RestTemplate template = new RestTemplate();
HttpHeaders headers = new HttpHeaders();
headers.setBasicAuth(client_id, client_secret);
HttpEntity<String> entity = new HttpEntity<String>(headers);

String response = template.exchange(tokenUrl,HttpMethod.GET, entity, String.class).getBody();

JSONObject obj = new JSONObject(response);
ConnectivitySocks5ProxySocket proxy = new ConnectivitySocks5ProxySocket(obj.getString("access_token"), "");
return proxy;
}

@Override
public Socket createSocket(String arg0, int arg1) throws IOException, UnknownHostException {
throw new UnsupportedOperationException();
}

@Override
public Socket createSocket(InetAddress arg0, int arg1) throws IOException {
throw new UnsupportedOperationException();
}

@Override
public Socket createSocket(String arg0, int arg1, InetAddress arg2, int arg3)
throws IOException, UnknownHostException {
throw new UnsupportedOperationException();
}

@Override
public Socket createSocket(InetAddress arg0, int arg1, InetAddress arg2, int arg3) throws IOException {
throw new UnsupportedOperationException();
}


private JSONObject extractEnvironmentCredentials() throws JSONException {
JSONObject jsonObj = new JSONObject(System.getenv("VCAP_SERVICES"));
JSONArray jsonArr = jsonObj.getJSONArray("connectivity");
return jsonArr.getJSONObject(0).getJSONObject("credentials");
}

}

 

Now create the connection object to postgres database using the below code,
	private Connection getConnection() throws SQLException {
String url = "jdbc:postgresql://localhost:5912/DB";
Properties props = new Properties();
props.setProperty("user", "username");
props.setProperty("password", "password");
props.setProperty("socketFactory", DBSocketFactory.class.getName());
Connection conn = DriverManager.getConnection(url, props);
return conn;
}

 

Now the database connection is using the Custom DB Socket Factory class which in turn will use the Proxy Socket to route the calls via socks5 proxy to the cloud connector

 

Conclusion


I hope this tutorial helps in some way for connecting to on-premise database. I have also used the similar approach with few changes to connect to SFTP using JSCH.

 
4 Comments
0 Kudos
Hi rohit_j,

Thanks for your post.

I followed it, but finally got CalssNotFound exception for the package path of DBSocketFactory.

what should I do to avoid this exception?

Best Regards,

Gary
0 Kudos
Hi Rohit,

My app deployment is failing. Can you please help me here as in what should the jar contents should be? It seems my jar does not have dependencies within it and that is why the deployment is failing.

Thanks and regards,

Rishab
manjunath_jp
Discoverer
0 Kudos
Hi Rohit,

Thanks for article,

I have followed your article to establish connection to on-prem oracle DB, But we are getting time_out error.  Below is the code for connecting;

Class.forName("oracle.jdbc.driver.OracleDriver");



String url = "jdbc:oracle:thin:@<ip>:<port>:<db_name>";

Properties props = new Properties();

props.setProperty("user", "****");

props.setProperty("password", "*****");


props.setProperty("socketFactory", DBSocketFactory.class.getName());


DriverManager.getConnection(url, props);


Except this, rest all I followed your article.

I tried with location_Id and without location_id also, Unable to crack this problem.


There is no trace of logs in CloudConnector also.
matevam
Explorer
0 Kudos

Hi Rohit!

We are able to authenticate to proxy server but our sftp session is not getting established. We are getting error-

javax.script.ScriptException: java.lang.Exception: java.net.SocketException: Connection failed with response code3@ line 283 in script1.groovy, cause: java.net.SocketException: Connection failed with response code3

 

Can you please suggest what might solve it?

 

Cheers,

Maria