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

[fatihpense]

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 fmsanchez 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-ex...

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

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

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

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

"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-s...

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.