Technical Articles
CPI connectivity with MS SQL Server on-prem using Cloud Connector
SAP has recently published a new release of CPI with some new features, including JDBC on-premise connection. This is a long-awaited feature that will be used in many use cases. This blog post covers the steps that you need to follow to configure and test the connectivity in an end-to-end scenario.
Note: Some steps will be skipped because the documentation and different blog posts already describe them in sufficient depth. Anyway, all links to blog posts and documentation will be provided.
Index
- Cloud Connector configuration
- Data Source configuration
- iFlow configuration
- Test E2E scenario
- Troubleshooting
1. Cloud Connector configuration
The initial configuration of the Cloud Connector can be found in this post: How to setup Cloud Connection.
Once everything is established in the on-prem server, it’s time to add the mapping to connect Cloud to the on-prem system. As you can see in the screenshot below, I have added a new TCP connection.
Cloud To On-Premise access control table
The new inbound TCP connection will have this data:
Field | Value |
Back-end Type | Non-SAP System |
Protocol | TCP¹ |
Virtual Host | <virtual host URL>² (e.g. mssqlserver) |
Virtual Port | <virtual host port> (typically 1433) |
Internal Host | <your internal host system URL> (e.g. your-server.com) |
Internal Port | <your internal host port> (typically 1433) |
Principal Type | None |
Description | <it’s up to you> |
Check internal Host | Check/Unchecked (you can always check it manually later) |
¹TCP is the protocol used for default JDBC connections.
²You can add any virtual host address as this is the one that you are going to call from CPI. In this scenario, we have replicated the same address name than the internal host for simplicity, but it is a good recommendation to establish a different naming convention to not expose any backend details.
Mapping virtual to internal system data
2. Data Source configuration
Now that the Cloud Connector has been configured in the CPI tenant, it’s time to add the MS SQL Server Data Source in CPI.
You just have to follow some simple steps to configure the JDBC Data Source:
2.1. Download SQL Server Driver
2.2. Upload/Deploy the Driver into CPI’s JDBC Material
2.3. Create the JDBC Data Source
These steps are explained further in this SAP’ section Managing JDBC Material.
2.1. Download SQL Server Driver
Follow this link and extract the zip, you will be able to see something similar than the screenshot below:
MS SQL Server Driver folder
2.2. Upload/Deploy the Driver into CPI’s JDBC Material
Now go to CPI cockpit and follow “Operations Link” > “JDBC Material” > “JDBC Driver”.
In this screen, you should be able to upload new drivers if you have the correct roles. Click Add and a pop-up will appear, select the Database Type as Microsoft SQL Server and choose the Jar file and click deploy.
Select database type
Note: I have chosen mssql-jdbc-8.4.1.jre8.jar
After deploying the Driver, a new line will be added into JDBC Driver like the screenshot below.
JDBC Driver successfully deployed
It will take a couple of seconds to change the status to “Started” but once that is done, you don’t have to do anything else in this menu.
2.3. Create the JDBC Data Source
Now that the driver has been deployed, the next step is to create the JDBC Data Source that will be consumed from the iFlow. Change the tab into JDBC Data Source and click Add button. This will pop-up a new form that needs to be filled.
The configuration of the JDBC Data Source will have these fields:
Field | Value |
Name | <The name of the Data Source> (e.g. TEST_DATA_SOURCE) |
Description | <Meaningful descripiton> |
Database Type | Microsoft SQL Server |
User | <Username of the SQL Server DB> |
Password | <Password of selected user> |
Repeat Password | <Same as above> |
JDBC URL¹ | jdbc:sqlserver://<Virtual Host>:<Virtual Port>;DatabaseName=<Schema Name>; |
Location ID | <Only if you have more than one Cloud Connector configured> |
Cloud Connector | Checked (it must be checked to go through the Cloud Connector) |
¹Note that Virtual host and virtual port will have to be the same as you have configured before in Cloud Connector
The result will be something like this:
Add new Data Source
Once you have filled all the required fields, click Deploy and the configuration will be deployed and the Data Source created. In the screenshot below you can see how should look after deployed. The Data Source will appear as “Stored” once you initially deploy it, but after a few seconds should appear as “Deployed”.
Data Source successfully added
3. iFlow configuration
Everything is now set to consume database data from a CPI iFlow using the new connection. This section won’t cover how to create the package/iFlow and we are to ignore these steps.
Once the iFlow is created, connect the Sender to the Start point with an HTTPS connection and expose the URL endpoint as you wish. Add a new Request-Reply step and connect it to the SQL Server Receiver.
HTTPS Connection
This connection will allow us to call the process whenever we want. Just don’t forget to add the required roles to your user (or the one you use to call the Process, we will need it later).
HTTPS Connection
JDBC Connection
This connection will forward everything that CPI’s body contains and will try to process it, also, as we have exposed the process as HTTP, the result of each call will be returned back to the caller.
JDBC Connection
The whole iFlow will look like the following screenshot:
Integration Process
Once everything is configured in the iFlow, save the process and deploy it. If all goes fine, you should have an available endpoint that represents your iFlow.
If you want to get the entry_point of the Integration Process you can go to “Operations View” > “Manage Integration Content” > “All”. Select your iFlow and get the Endpoint that should be there.
Classical option to get the endpoint
There is another option to get the URL if you use ConVista CPI Helper tool. Just click Info when you are inside the iFlow and you will see the URL there.
ConVista Helper tool
Note: This process follows Request-reply pattern and if you want to learn more about patterns you can follow my colleague Bhalchandra Wadekar with his series of blog posts about Enterprise Patterns (EIPinCPI).
4. Test E2E scenario
With the iFlow deployed, you can consume the endpoint using Postman or any other REST client tool (in this case I will be using Postman).
Postman insert call
As you can see, the message sent above shows that you can send any SQL statement and it will be run in the Database. After the insert has been triggered, you can see that the result of a select statement also returns some values.
Postman Select
5. Troubleshooting
I will be adding more issues if I found them and how can be solved, as far as I faced, these are the most important that I have seen.
- If you are getting this issue in the iFlow:
com.sap.it.rt.adapter.http.api.exception.HttpResponseException: An internal server error occured: The TCP/IP connection to the host localhost, port 1105 has failed. Error: "The driver received an unexpected pre-login response. Verify the connection properties and check that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. This driver can be used only with SQL Server 2005 or later.".
–> You have to modify the TCP SSL to TCP
- If you are getting this issue in the iFlow:
com.sap.it.rt.adapter.http.api.exception.HttpResponseException: An internal server error occured: Error while getting Datasource Service: ServiceReference is null.
–> You have an incorrect JDBC URL in the JDBC Data Source configuration and probably it has not deployed properly. You don’t have the port established in the JDBC Data Source.
- If you are getting this issue in the iFlow:
com.sap.it.rt.adapter.http.api.exception.HttpResponseException: An internal server error occured: The port number 1108/CPI is not valid..
–> You have an incorrect JDBC URL in the JDBC Data Source configuration and probably it has not deployed properly. You don’t have “;DatabaseName=<DatabaseSchema>;” added in the JDBC URL or is not added properly.
- If you are not getting any response from the DB:
–> See this reply from David Ruiz De Azua
Disclaimer: The setup described in this post is just for experimental purposes and some of the configurations might need a further security review.
This is my first post and could have some mistakes, any feedback is appreciated.
Hi Fernando,
Congratulations on your first post!
Great job and looking forward to your next one.
Thank you for contributing to the SAP Community.
Great blog, Fernando Martin. All the steps have sufficient details. The troubleshooting section is a cherry on top and will certainly facilitate the process of connecting to the MS SQL Server.
Great to see new faces in that community! Keep posting Fernando Martin
Nice blog. Just curious to know .. in case of PO, DB msg processing becomes slow for large volumes. How much message volume CPI can handle?
Hello,
The post is very useful, thanks Fernando.
I would like to share an issue I had but we sorted with SAP. They are updating the documentation as well but not sure when. I can see Fernando already pointed to use different names.
In the meantime in case you face the same issue below how to fix it:
My iFlow was doing a simple query but the iFlow was never getting a response back from the DB. After opening an incident with SAP they were very helpful with a couple of tips.
Usually when setting the cloud connector configuration for on-premise we copy and paste the same virtual host = internal host and virtual port = internal port.
After making the changes as SAP recommended the JDBC adapter worked as expected and iFlow returned the query back.
I would like to point out that if it works as per Fernando’s example is good, but the above is just in case you run into a troubleshooting this might help you.
David.
Many thanks David!!
The post has been updated with your reply.
I have been doing some performance tests in CPI around DB Inserts and I got infinite loops sometimes, might be that the root cause...
Thanks Fernando Martin, glad to help.
Currently doing some testing in DEV and soon in QA. No issues at the moment with DB performance.
What about you? Did you sort the infinite loops on your side after changing the configuration?
Hi Fernando,
I have maintained TCP protocol in Cloud to On-premise connection, but still, I am getting the below error.
Could not get JDBC Connection; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException:
The TCP/IP connection to the host localhost, port 1105 has failed. Error:
"The driver received an unexpected pre-login response.
Verify the connection properties and check that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port.
This driver can be used only with SQL Server 2005 or later."
let me know if you have any clue.
Regards,
Pradeep A.
Hi,
Were you able to resolve the issue ? if yes, please let me know how
I am also facing same issue. Could not find any solution yet.
There are few things which needs to be considered, please make sure all the below points are passed :
Hi Mani,
Hope you are doing good.
Are only these checks works for you? for me, these checks are already done yet, I sometime get it working and sometime not. Specially it fails when I send frequesnt calls.
Could there be any other checks to be done?
Regards,
Debtirtha
Hi,
in my case, I have given a virtual host (which is an existing server name). After changing the virtual host to a new and unique one, I can connect successfully.
Good day,
Thanks for this post. It helped me a lot.
Maybe this can help someone:
I received this error:
com.sap.it.rt.adapter.http.api.exception.HttpResponseException: An internal server error occured: Error while getting Datasource Service: ServiceReference is null.
Which was caused by deploying the wrong JDBC driver. I saw on the SAP Help portal that only certain versions of the driver are supported, and used one of those, i.e. 8.2.2 and it worked.
Regards,
Etienne
Hi,
Thanks for the post, and I'm trying to connected to my On-premise PostgreSQL, I got the same error as Etienne Du Toit, but I didn't configure the JDBC driver.
I tired with Virtual host = "127.0.0.1" and also "testdev", but got same error.
and this is the JDBC datasource configuration.
Let me know if you have any clue.
Regards,
Gary
Hi
Now, the cloud connector check box has disappeared from the JDBC Data Source setting.
so I set like this
Finally, an Error occurred.
I am not able to solve the problem of this error.
SFTP in the same way works well when connected to the cloud connector and tested.
The SFTP Setting still has a cloud connector checkbox.
Regards
Leo
I test the java application with eclipse. it is working.
I think the sql server and service is no problem. I use SQL Server 2022 Express.
Why does SAP still use mssql-jdbc-8.4.1.jre8.jar?
MS recommends using mssql-jdbc-12.2.0.jre8.jar. Are there any compatibility issues?
Regards
Leo
Thank you for sharing this blog helped to configure the on-prem mssql server
Hello,
We are trying to establish connection to MS SQL in Azure (database.windows.net) from our SAP CPI Tenant via the SAP Cloud Connector (Cloud-to-Premise) but facing an error.
Error Detail:
The processing has failed due to error in creating metadata table. : com.microsoft.sqlserver.jdbc.SQLServerException: Cannot open server "localhost" requested by the login. The login failed.
Has anyone encountered this error? If so, how did you fix it?
Note that we have configured the SAP Cloud Connector to have different Virtual Host/Port and Internal Host/Port but still facing the error.
If we do not use the SAP Cloud Connector, we are able to connect to database.windows.net.
But we need to use the SAP Cloud Connector for more secured connection.
Thank you,
Carlo
We had similar issue...please advice what is the resolution
Hi Carlo Borja
We had similar issue recently and we had connected with SAP as well for the same issue. SAP is not supporting MS SQL in Azure (database.windows.net) from our SAP CPI and they are working on it.
Thanks
G Bala Vignan
Hi @Fernando Martin
I'm trying to Integrate CPI with MySQL database. I configured the cloud connector as mentioned in the Blog, facing below error.
Can you please help which driver I need to install in CPI and share JDBC source configuration details like database type, URL.
I tried with MS SQL and facing issues as below.
SAP recently whitelisted Microsoft JDBC Driver for SQL Server 11 version recently. If any version conflicts if you get, download 11 version and deploy it.