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!
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.
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.
They are probably installed by default, It can require some other configuration. See the question: “3.What are the platform requirements for ServerXMLHTTP?”
“You must make sure you enable OLE automation…”
2.b. Can we install extensions to SQL Server?
Here is an example with curl:
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:
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.”
“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:
1. SQL Server Reporting Services REST APIs
This feature is only useful for BI purposes. E.g. You can’t call a stored procedure.
Here are the API methods:
2. SQL Server SOAP endpoint
This feature is deprecated.
3. SAP CPI JDBC adapter
This adapter only supports HANA or ASE databases.
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.
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.
That is a very good idea & I should add this to the blog post itself. Also, thank you for the roadmap information!
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?
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
Any idea about an update on this one?
According to note 2924589, it is still not available.
Hello Dimitri Sannen ,
There is this roadmap entry:
"Java database connectivity for on-premise databases" which is planned for 2020 Q3
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.
Sorry, I am also waiting (im)patiently 🙂
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)
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 🙂