Skip to Content
Technical Articles
Author's profile photo Fatih Pense

Connectivity Options for On-Premise SQL Server and SAP Cloud Platform Integration (CPI)

This blog post contains possible solutions to access an on-premise SQL Server from CPI. I have done my best to make this list to be as extensive as possible. I will also include links for further reading. Let’s start!

Possible Solutions

0. Using Cloud Connector

Update: Now you can connect to on-premise databases using JDBC. Check this blog post from Fernando for the steps.

1. A very small application in the on-premise network

It can be Java, NodeJS, .Net, etc. I believe this is the cleanest solution available because it is supported by Microsoft and SAP with recent features. Accessing an on-premise HTTP resource is the main point of the SAP Cloud Connector.

https://github.com/microsoft/sql-server-samples/tree/master/samples/features/json/todo-app/nodejs-express4-rest-api

https://docs.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server?view=sql-server-ver15

2. Using HTTP Client inside an SQL Server Stored Procedure

2.a. Default libraries

There are some libraries that may require configuration, and there can be limitations.
-MSXML2.XMLHTTP (old)
-MSXML2.ServerXMLHTTP (new)

https://stackoverflow.com/questions/17407338/how-can-i-make-http-request-from-sql-server

They are probably installed by default, It can require some other configuration. See the question: “3.What are the platform requirements for ServerXMLHTTP?”
https://support.microsoft.com/en-us/help/290761/frequently-asked-questions-about-serverxmlhttp

“You must make sure you enable OLE automation…”
https://stackoverflow.com/questions/21332125/how-do-i-send-parameters-by-using-http-post-with-t-sql-and-ole-automation-proced

2.b. Can we install extensions to SQL Server?

Here is an example with curl:
https://blogs.msdn.microsoft.com/sqlserverstorageengine/2018/04/17/calling-http-endpoints-in-t-sql-using-curl-extension/

3. Solutions based on SAP Cloud Connector TCP proxy feature:

We have to ignore the performance cost of the lack of connection pooling.

In general, the solution can be viewed as a hack. SAP may not endorse this solution & can prevent it in the future.

Cloud Connector uses SOCKS5 proxy for TCP.

This example is not for CPI, this is a generic Java application deployed on SCP. Beware that CPI may have limitations:
https://blogs.sap.com/2018/02/06/how-to-set-up-a-jdbc-connection-using-the-cloud-connector/

Standard adapters like SFTP, Mail can work with TCP proxy. But there is no clear path for custom adapters or Java code working in a mapping:
“What I want to emphasize here is the SAP Cloud Connector support added for the mail receiver adapter in our July 2017 release. This configuration utilizes the SOCKS5 proxy supported in SAP Cloud Connector version 2.10 and higher.”
https://blogs.sap.com/2017/07/20/how-to-connect-via-sap-cloud-platform-integration-to-my-on-premise-mail-server/

“Adapter Development Guide” is recently updated with Cloud Connector features. It only gives an example of an HTTP proxy. There is also a good write-up by Appala Naidu: https://blogs.sap.com/2019/10/31/cloud-integration-developing-custom-adapters-to-access-on-premise-systems/

Public API only has HTTP connection type:
com.sap.it.api.ccs.adapter.ConnectionType.HTTP

Nonsolutions

1. SQL Server Reporting Services REST APIs

This feature is only useful for BI purposes. E.g. You can’t call a stored procedure.
https://docs.microsoft.com/en-us/sql/reporting-services/developer/rest-api?view=sql-server-ver15

Here are the API methods:
https://app.swaggerhub.com/apis/microsoft-rs/SSRS/2.0

2. SQL Server SOAP endpoint

This feature is deprecated.

3. SAP CPI JDBC adapter

This adapter only supports HANA or ASE databases.

Closing Thoughts

I think supporting on-premise JDBC for “CPI for process services” is a difficult problem for SAP. There are a lot of JDBC driver versions for several databases and they differ for Java versions. Right now CPI only supports Java 7.

Because SAP CPI is a SaaS service maintaining these options can require more effort than it is worth. Also, there is SAP CPI for Data Services which can be used for some of the same requirements regarding DBs. Overall, native on-premise JDBC support would be great for the users.

Anything missing? Please add your solution in the comments.

Assigned Tags

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

      Dear Fatih,

      SAP will soon release the JDBC adapter for CPI which will also include on-premise databases.

      In the meantime I recommend my clients to use the PI J2SE adapter engine (lightweight runtime with mainly File and JDBC adapter), which can be addressed from CPI easily via SOAP XI.30 protocol (http/s) through Cloud Connector.

      Best regards,
      Adam

      Author's profile photo Fatih Pense
      Fatih Pense
      Blog Post Author

      Dear Adam,

      That is a very good idea & I should add this to the blog post itself. Also, thank you for the roadmap information!

      Best regards,
      Fatih

      Author's profile photo Sandesh Kurumella
      Sandesh Kurumella

      Hi Adam,

      Does that mean this JDBC adapter that SAP is going to release should be installed on-premise so that it can access on-premise databases?

      Regards,
      Sandesh

      Author's profile photo Adam Kiwon
      Adam Kiwon

      Dear Sandesh,

      No, my information is that this will be passed through the cloud connector (like for HTTP/SFTP/MAIL already). But SAP postponed the delivery (again) on their roadmap to Q2/2020.

      Best regards, Adam

      Author's profile photo Dimitri Sannen
      Dimitri Sannen

      Hi Adam,

      Any idea about an update on this one?

      According to note 2924589, it is still not available.

      Kind regards,
      Dimitri

      Author's profile photo Fatih Pense
      Fatih Pense
      Blog Post Author

      Hello Dimitri Sannen ,

      There is this roadmap entry:

      "Java database connectivity for on-premise databases" which is planned for 2020 Q3

      https://roadmaps.sap.com/board?PRODUCT=901B0ED1A0641EDAB694E0269F13E0F6&q=jdbc&range=CURRENT-LAST#;INNO=6EAE8B28C5D91ED89DAF36D9A684C0CE

      Maybe Adam Kiwon has more information.

      SAP Blogs tip: I think it is better to mention using @ and search for the name if you want the person notified by SAP Community Inbox.

      Best regards,
      Fatih

      Author's profile photo Adam Kiwon
      Adam Kiwon

      Sorry, I am also waiting (im)patiently 🙂

      Author's profile photo Fernando Martin
      Fernando Martin

      Hi Fatih Pense,

      Good post! I have just been looking forward to the JDBC Adapter on-prem. For all that has been waiting, It was released on the 5th of December.

      I had the opportunity to have a look at it and it works quite well. (See my post about it CPI connectivity with MS SQL Server on-prem using Cloud Connector)

      Best regards,

      Fernando Martin

       

      Author's profile photo Fatih Pense
      Fatih Pense
      Blog Post Author

      Hi Fernando , Thanks for sharing! I have updated the post with a link to your post.

      I have read the blog post and included it in my podcast episode as well 🙂

      Best regards,
      Fatih