Skip to Content

How to set up a JDBC Connection using the Cloud Connector

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.1

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/57ae3d62f63440f7952e57bfcef948d3.html
  2. Perform the initial configuration: https://help.sap.com/viewer/cca91383641e40ffbe03bdc78f00f681/Cloud/en-US/071708a655de4486b498cf5b16fb8ea8.html
  3. Import the access_control.zip into the Cloud Connector: https://help.sap.com/viewer/cca91383641e40ffbe03bdc78f00f681/Cloud/en-US/f42fe4471d6a4a5fb09b7f3bb83c66a4.html
  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 thConnectivityServlet, 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) Java App

Footnotes

  1. UDP (and possibly others) also uses the term “port”, but the ports mentioned here are well-known TCP ports.
25 Comments
You must be Logged on to comment or reply to a post.
  • 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.

    • 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.

  • 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.

    • 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?

  • 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

  • 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

    • 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

  • 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.

    • 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?

      • 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.

         

        • 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.

          • 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?

          • 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.

          • 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

  • 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

    • 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

  • 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.