- SAP Managed Tags:
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
- Refer this link to set this up.
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.
5 Comments
