How to set up a JDBC Connection using the Cloud Connector

[former_member224346]

Abstract

In this blog, I'm going to show you, how to consume data from a MySQL-database running on-premise in a Java Application in SAP Cloud Platform (we will use neo). This will require using the Cloud Connector and one of it's recent features.

To make the example more realistic and less ugly to look at, we will display the data in a small UI5 App instead of looking at raw json data.

---

1. Introduction

The SAP Cloud Connector is a great tool for consuming data from legacy systems and other on-premise applications in the cloud. Previously this required the application to use either the HTTP, RFC or LDAP protocol for data exchange. While these protocols (especially HTTP) cover a high percentage of use-cases, there are some applications which require other protocols. One example is connecting to a database, as many databases are using their own protocols, which are normally based on TCP. Since Cloud Connector version 2.10.0.1 TCP connections are also supported by the Connectivity Services.

As a proof-of-concept, I created a Java application which executes SQL queries using the MySQL Connector/J where the Java application is deployed in the cloud and the database runs locally. I thought of creating the same example except using PostgreSQL instead of MySQL, let me know in the comments, whether you're interested. For the application, I'm using the "world dataset" which is available for download in the Documentation of MySQL. I'm calculating the fraction of people living in the city compared to the whole population of a given country, the result is not very accurate (e.g. the fraction calculated for Singapore is greater than 1) as it's only a dataset meant for testing, but that's good enough for a POC.



The blog is split into three parts:

• Technical Background you can skip this part if you're already familiar with TCP and SOCKS.


• How to set up at home How to set up the Cloud Connector and MySQL, import the dataset and deploy the application.


• Code Review briefly explaining the Java Code.

2. Technical Background

The Connectivity Services provides a SOCKS-Proxy which allows your application to connect to a TCP-based system using its virtual hostname and port.

2.1 TCP

Have you ever heard of "Port 80", "Port 8080", "Port 21", "Port 443", etc.? That's TCP.¹

TCP or Transmission Control Protocol basically has four main tasks:

1. Guarantees the order of packages/bytes sent.


2. Automatic retransmission in case of package loss.


3. Flow control (controlling the speed of data transmission according to the available bandwidth and the ability of the receiver to process the data).


4. Delivering the data to the correct "port" (meaning the correct program and socket).

The TCP protocol is settled lower in the protocol stack than the other protocols supported by the Cloud Connector (Layer 4 of the OSI model). Actually the all the other protocols are based on TCP. This implies two things:

• Starting with the good one: As almost any protocol used in business applications (without a proof, sorry) is based on TCP the Cloud Connector now supports (almost) any protocol, you'll ever want to use.


• Now the downside: As the protocol operates on a lower level (possibly even forwarding encrypted data) it is not possible to provide the same fine granular access control as it's possible e.g. for HTTP (path) and RFC (function name). Which does NOT make it insecure to use, but - as stated - affects granularity. So be aware of that.

2.2 SOCKS

You possibly already came across this term in the proxy settings of your Brower, Operating System, IDE or EMail-Client. SOCKS is a proxy protocol based directly on TCP and supports proxying raw TCP connections (and more), which makes it a perfect fit for the Cloud Connector TCP use case. To get a more detailed overview of the protocol, I can recommend the Wikipedia article about SOCKS. We are using SOCKS5, so concentrate on that parts of the article, if you're going to read it.

3. How to set up at home

To set up my POC you need three things:

1. A MySQL Server with the sample data.


2. A Cloud Connector instance connected to your Cloud Platform Subaccount.


3. The Webapp deployed to your Cloud Platform Subaccount.

The next subchapters will help you set up everything. Each subchapter contains a quick summary as well as a more detailed video tutorial. If you already an advanced Cloud Platform / Cloud Connector user feel free to skip the videos.

3.1 MySQL

1. Download and Install MySQL. The version should not matter, but if you experience any problems use 5.7.18 (the version I have installed).
   This document will help you: https://dev.mysql.com/doc/refman/5.7/en/installing.html


2. Import the world dataset: You can download it here: https://downloads.mysql.com/docs/world.sql.zip
   and installation guidance is available here: https://dev.mysql.com/doc/world-setup/en/world-setup-installation.html

https://downloads.mysql.com/docs/world.sql.zip

3.2 Cloud Connector

1. Download and install the Cloud Connector as described here: https://help.sap.com/viewer/cca91383641e40ffbe03bdc78f00f681/Cloud/en-US/57ae3d62f63440f7952e57bfcef...


2. Perform the initial configuration: https://help.sap.com/viewer/cca91383641e40ffbe03bdc78f00f681/Cloud/en-US/071708a655de4486b498cf5b16f...


3. Import the access_control.zip into the Cloud Connector: https://help.sap.com/viewer/cca91383641e40ffbe03bdc78f00f681/Cloud/en-US/f42fe4471d6a4a5fb09b7f3bb83...


4. If you changed the port or installed MySQL on another machine than your Cloud Connector, change the internal hostname and port of the access control accordingly.


5. Finally, click "check connection" to ensure everything is configured correctly.

https://tools.hana.ondemand.com/#cloud

3.3 Deploy App

1. Open https://account.hanatrial.ondemand.com/ and log in.


2. Deploy a new Java application. Use the provided war file: map-app.war and "Java Web Tomcat 8" as Runtime.


3. After the file was successfully uploaded, deployed and started, open the application url.


4. You should now see a map displaying the aggregated data from your database.

 https://account.hanatrial.ondemand.com/

4. Code Review

The biggest class in the code is the SocketFactory. It might look a bit complex for a POC, but it's just a copy of the StandardSocketFactory provided by the MySQL Connector/J with a small change. This is necessary because the default implementation does a DNS lookup before connecting to the proxy instead of relying on the DNS resolver of the proxy (a feature introduced with SOCKS5) and fails in our case because virtual hosts are not real DNS names. There is already a confirmed bug report in the bug tracking to of MySQL (MySQL Bug #77924). Hopefully, this workaround can be removed soon.

The Driver is loaded in the DBConnection class. There is nothing special in the class, just a very basic JDBC driver instantiation, except for line 19 where the SocketFactory is configured.

Also in the ConnectivityServlet, there is only one thing which is special: the init method (beginning in line 39), where the proxy authentication is configured. This strictly follows the documentation.

5. Related Information

SAP Cloud Platform Documentation

Using TCP for Cloud Applications

Configure Access Control (TCP)

Blogs containing other examples for Cloud Connector TCP

How to connect via SAP Cloud Platform Integration to my On-Premise mail server

How to access an On Premise HANA (through SAP Cloud Connector) via JDBC from SAP Cloud Platform (NEO...

Footnotes

1. UDP (and possibly others) also uses the term "port", but the ports mentioned here are well-known TCP ports.

[moyalynne]

Nice to see you again, Philipp! Thanks for sharing the blog 🙂

[MortenWittrock]

Good stuff, Philipp 😄

[Former Member]

Great stuff Philipp! Is there a solution for using Cloudfoundry to connect to On-Prem HANA? Have been struggled for some time trying to find a solution.

[former_member224346]

I don't think this is currently possible using the Connectivity Services as TCP Connectivity is not yet available in CF.

The only thing I can imagine is an HTTP based on-premise application which accesses the HANA database and makes selected data available via the Cloud Connector to CF.

[karthik_ramachandran2]

Thanks for the informative blog Philipp. I am trying to connect to an Oracle DB. This does not seem to work. Are you aware if Oracle driver has a issue similar to MySQL Bug #77924 where in the DNS resolver of the SOCKS proxy is not used? I am not finding any info on this.

[former_member224346]

Possibly there is some similar Bug in the Oracle JDBC implementation, after all MySQL is developed by Oracle (and Community) so yeah.

Unfortunately I don't have access to an Oracle Database, so I can't try it myself. Could you give me a call and we figure it out together?

[ChadiMohamed]

Great blog Philipp.

I have another scenario, I want to develop Sap Cloud Application in which the destination backend is Oracle E-Business Suite, Is it applicable to use cloud connector to integrate my application with this on-premise system, and should I use OData when I try to read or push data from/to the system ?

Best Regards,
Chadi Mohamed

[former_member224346]

Thank you.

 

I'm not familiar with the Oracle E-Business Suite, but if it offers a restful interface (OData or not) the Cloud Connector is an appropriate solution.

But this blog might not be a good starting point for you (as it uses the TCP instead of the HTTP protocol). Please take a look at the documentation: Exchanging Data via HTTP

 

Kind Regards,

Philipp

[ChadiMohamed]

Thanks a lot Philipp !

[artioms]

Hi Philipp,

Thank you for the blog.

I followed your blog to connect to a AS400 DB. Everything is working fine except the fact that SCC is denying access to the AS400 system. In the SCC I can see that my java application is connected, but in the Audits it says "Denying access to system ... " With HTTP I know you have to make the resouces available, but with TCP this is not possible. Do you know how to resolve this?

 

Kind regards,

Artiom

[former_member224346]

Hi Artiom,

 

TCP on Cloud Connector does not support Resources like RFC or HTTP do.

This is because TCP is on a lower level in the network stack. So it's sufficient to have an Access Control mapping the virtual host to the internal host and setting the protocol to TCP, without any further configuration.

To me your problem sounds like a typo, can you double-check that please?

 

Kind Regards,

Philipp

[artioms]

Hi Philipp,

 

Thank you for the fast reply.

 

I have already checked my code for several times. If you like I can email the code to you.

 

Kind regards,

Artiom

[artioms]

Hi Philipp,

I was able to resolve my issue. It appears that JDBC requires ports: 449, 8470, 8471, and 8476. I had to add all those ports in the Access Control.

 

[former_member224346]

Cool, thank you for sharing 🙂

[former_member585714]

Hi Philipp,

It was a good blog. A lot of good information in one place. I tried accessing data of MySQL server from the cloud. It worked like a charm. But there is an issue for me while trying to  Microsoft SQL server on-premise database., even after following all the steps in your blog, It is giving me time out. From my knowledge what I suspecting is I am trying to use Socket configuration for MySQL server to use Microsoft SQL database. so it's not applying the configuration. Could you please help me with this issue. I am attaching the error log below.

[former_member224346]

It looks suspicious to me, that there is an globally routed IP Address in the log (19.100.0.123 which is some IP Address registered in USA).

Are you using the virtual host correctly?

[former_member585714]

Hi Philipp,

For us we have configured both the virtual address and local address same(19.100.0.123) so it might be looking like a globally routed address. But actually we are trying to access virtually configured address only.

 

[former_member224346]

Ok, I see.

The problem is, the Microsoft SQL driver automatically resolves the hostname instead of relying on the SOCKS Proxy to do this. You'll need to find a way to stop the driver from doing this.

If you can't find something in the documentation about that, take a look at this Issue on GitHub https://github.com/Microsoft/mssql-jdbc/issues/462 and leave a comment if necessary.

[former_member585714]

Hi Philipp,

Thanks a lot for the reply. As far as I understand, there is a problem with Microsoft SQL driver but they were asking to modify the sources of the driver for skipping Microsoft SQL driver throwing UnknownHostException, but in my case, I cant do that because I'm doing this for a productive environment. Could you please suggest another solution if possible?

[former_member224346]

Hi,

actually it is not a big change, so you should be able to do this even for production, after proper testing of course.

But the better way would be, getting in contact with Microsoft (e.g. via the GitHub Issue mentioned) to get this fixed. If you start the conversation there, providing some details why you want to get this fixed, I can provide some technical details to them.

[former_member585714]

Hi Philipp,

I am trying to do that fix that is mentioned in the GitHub issue. I have done the change in the Driver but still not able to call the JDBC URL and get a response. I am not able to track the issue could you please help me? If i want to share my code with you how would I do that?

Attaching a couple of snips, how I am trying to access JDBC.

1. Servlet where I am trying to get Connection.

2. DbConnect class where I am trying to set Properties

[jorge_velasquez]

Hi.

I want to connect Oracle to Cloud Connector any documentation?

Regards

[former_member599804]

Hi Philipp,

I am trying to download a file from my sftp server.

I was able to configure the Cloud connector to my sftp server.

Now I have deployed my code in the SAP Neo environment.

I want to download a certain file from my sftp server going via the SAP Cloud connector....but not been successful at it yet. Not sure how to achieve it.

The blogs on the SAP website have not been of much help.

Please let me know if you have worked in this kind of setup before. Any help will be appreciated.

 

-Deepesh

[former_member224346]

Hi Deepesh,

 

unfortunately I haven't tried sftp yet.

Make sure the library you use to access sftp uses the proxy and you should be good.

Keep me posted.

 

Kind Regards,

Philipp

[former_member601250]

Hello Philipp,

Very good information.

We have a scenario where we want to connect to SAP cloud successfactors with our On premise Oracle database with CPI. How can we connect Oracle database here.

 

[former_member498675]

Hi Philipp,

Thanks for this informative blog.

I am trying to implement the same following your blog. I deployed my war file onto SAP Cloud Platform but I am getting below error ClassNotFoundException: com.mysql.cj.jdbc.Driver.

I am using mysql-connector-java-8.0.16 jar file.

I face the same issue while running it locally also but resolved it by putting JAR file into TOMCAT library folder.

Do you have any idea how to solve this on server?

Thanks.

Best Regards,

Deepak Sharma

[former_member224346]

Hi Sharma,

 

please make sure the mysql-connector-java-8.0.16 jar file is inside your war-file (in the folder WEB-INF/lib).

 

Kind Regards,

Philipp

 

P.S.: In case you don't know how-to inspect a war-file: they are simply zip-archives with additional metadata. You can open them with e.g. WinRAR or any other archiver.

[former_member498675]

Hi Philipp,

 

Thanks for your quick reply.

I was able to resolve this issue. But now I am struggling to make SOCK5 connection as the version which you have used is different and even mySQL version is also different.

So, this latest version don't have PropertyDefinitions, instead there is implementation of PropertyKey and PropertySet. I adjusted my code according to that but I dont know what am I missing, I am not able to connect virtualhost.

 

If you can help me on this?

 

Thanks.

 

Best Regards,

Deepak Sharma

[former_member618297]

Hi Philip

 

 

I have my java application deployed on SAP Cloud platform and mysql database running on a VM.

In the cloud connected, i have added virtual host ip of the db vm and i tested , it is reachable as well.

 

But, when i try to connect to get a connection, the sql exception is thrown:

"communications link failure the last packet sent successfully to the server was 0 milliseconds ago. the driver has not received any packets from the server."

 

String URL :jdbc:mysql://hostname:port/dbname

 

Can you please help me how to resolve this issue. I am struck from many days.

 

PFB the screenshots.



 

 

Thanks

Avneesh

avneesh.kumar@sap.com

[former_member645391]

Hi, has anyone managed to get this working with the the Microsoft JDBC driver for MSSQL?  There doesn't appear to be any support for SOCKS as far as I can tell.

[LouisHuang]

Hi. Logan.

 

Do you get this working for MSSQL? I have the same requirement that using jdbc from cf application to connect on premise MSSQL DB.

 

Thanks a lot.

[former_member645391]

Hi Louis,

 

I'm afraid I didn't, due to other priorities this got parked.  However, I did post out on the github page for the project and I beleive they merged a fix/amendment which may provide a solution.  Though it's a little outside my realms of experience.

 

https://github.com/microsoft/mssql-jdbc/issues/1223

 

Hope that helps!

 

Kind regards,

 

L

[LouisHuang]

Hi. Logan.

Understood. Thanks a lot.

 

 

[andreas_mazzola]

Hi Philipp,

did you build a same example for PostgreSQL?

We have to connect NEO HTML5 apps to PostgreSQL (SCP-Neo to  SAP onPremise)

 

Best regads,

Andreas

 

 

[Ivan-Mirisola]

Hi andreas.mazzola,

To change the application from mySQL to PostgreSQL all you need to do is change the JDBC driver and dependencies. Check the file DBConnection.java on the sample to figure out how to switch the drivers.

Regarding the HTML5 application, it requires an OData/REST service. That service in Neo should be implemented in Java and that application is the one that will perform the DB connection via JDBC through a Socks Proxy.

Best regards,
Ivan

[andreas_mazzola]

Thank you very much Ivan, this will help us a lot 🙂

[philippeaddor]

Hi Philipp

You briefly mention UDP. We have a requirement passing UPD through the Cloud Connector. Since it uses SOCKS5, and that actually supports UDP, I wonder why the CC doesn't support UDP packets (or does it?).

Thanks for your answer in advance,

Philippe

[former_member834011]

Hi Philipp,

The blog was informative. Thanks for sharing. We are now trying to connect to oracle RAC using the way mentioned by you in this blog. We are not able to find a way. Please help me out in this case.

Thanks and regards,

Rishab

[STM23]

Did you find something about this scenario?

 

Best Regards