The Driver Received an Unexpected Pre-login Response Updated FREE
The Driver Received an Unexpected Pre-login Response
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 demand to follow to configure and examination the connectivity in an finish-to-stop scenario.
Annotation: Some steps will exist skipped considering the documentation and different weblog 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
- Examination E2E scenario
- Troubleshooting
ane. Cloud Connector configuration
This section covers the configuration of the mapping to the internal system which will allow the external inbound calls (CPI) to exist redirected to the proper internal Host URL/port.
The initial configuration of the Cloud Connector can be found in this mail service: How to setup Cloud Connection.
Once everything is established in the on-prem server, information technology'southward time to add the mapping to connect Cloud to the on-prem arrangement. Equally you can see in the screenshot beneath, I have added a new TCP connectedness.
Deject To On-Premise access command table
The new entering TCP connection will have this data:
| Field | Value |
| Back-stop Blazon | Not-SAP Organization |
| Protocol | TCP¹ |
| Virtual Host | <virtual host URL>² (e.g. mssqlserver) |
| Virtual Port | <virtual host port> (typically 1433) |
| Internal Host | <your internal host organisation URL> (e.g. your-server.com) |
| Internal Port | <your internal host port> (typically 1433) |
| Chief Type | None |
| Clarification | <it's up to yous> |
| Check internal Host | Check/Unchecked (you can always bank check information technology manually later) |
¹TCP is the protocol used for default JDBC connections.
²You can add together whatever virtual host address equally this is the one that you are going to phone call from CPI. In this scenario, we have replicated the aforementioned address proper noun than the internal host for simplicity, merely it is a practiced recommendation to found a different naming convention to not expose any backend details .
Mapping virtual to internal organization information
two. Data Source configuration
Now that the Deject Connector has been configured in the CPI tenant, it'southward fourth dimension 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.ane. Download SQL Server Driver
ii.2. Upload/Deploy the Driver into CPI'south JDBC Material
ii.3. Create the JDBC Data Source
These steps are explained further in this SAP' section Managing JDBC Fabric.
ii.1. Download SQL Server Driver
Follow this link and excerpt the zip, you will exist able to see something similar than the screenshot below:
MS SQL Server Commuter folder
2.2. Upload/Deploy the Commuter into CPI's JDBC Material
Now get to CPI cockpit and follow "Operations Link" > "JDBC Material" > "JDBC Driver".
In this screen, you should be able to upload new drivers if you accept the correct roles. Click Add and a pop-up will announced, select the Database Type as Microsoft SQL Server and cull the Jar file and click deploy.
Select database type
Annotation: I have called mssql-jdbc-viii.iv.1.jre8.jar
After deploying the Driver, a new line volition be added into JDBC Driver like the screenshot below.
JDBC Commuter successfully deployed
It will take a couple of seconds to alter the condition to "Started" but once that is done, you don't have to practice anything else in this menu.
2.3. Create the JDBC Data Source
At present that the driver has been deployed, the next footstep is to create the JDBC Data Source that will be consumed from the iFlow. Change the tab into JDBC Data Source and click Add together button. This will pop-upwards a new class that needs to exist filled.
The configuration of the JDBC Data Source will accept these fields:
| Field | Value |
| Proper noun | <The name of the Information Source> (e.grand. TEST_DATA_SOURCE) |
| Clarification | <Meaningful descripiton> |
| Database Blazon | Microsoft SQL Server |
| User | <Username of the SQL Server DB> |
| Password | <Password of selected user> |
| Repeat Password | <Same as in a higher place> |
| JDBC URL¹ | jdbc:sqlserver://<Virtual Host>:<Virtual Port>;DatabaseName=<Schema Proper name>; |
| Location ID | <Only if yous have more than one Deject Connector configured> |
| Cloud Connector | Checked (it must be checked to get through the Cloud Connector) |
¹Notation that Virtual host and virtual port will accept to be the same as you have configured before in Deject Connector
The outcome will be something like this:
Add new Information 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 beneath you tin can meet how should wait later deployed. The Information Source will appear as "Stored" one time you initially deploy information technology, just later on a few seconds should announced as "Deployed".
Data Source successfully added
3. iFlow configuration
Everything is now set up to consume database data from a CPI iFlow using the new connexion. This department won't comprehend how to create the bundle/iFlow and we are to ignore these steps.
Once the iFlow is created, connect the Sender to the Start indicate with an HTTPS connection and expose the URL endpoint as you lot wish. Add a new Asking-Reply step and connect it to the SQL Server Receiver.
HTTPS Connection
This connection will allow us to call the process whenever we desire. Just don't forget to add together the required roles to your user (or the i yous utilise to call the Process, nosotros will need it afterward).
HTTPS Connection
JDBC Connection
This connection volition forward everything that CPI's body contains and volition try to process it, also, as we have exposed the process equally HTTP, the result of each call will exist returned back to the caller.
JDBC Connection
The whole iFlow will look like the post-obit screenshot:
Integration Process
Once everything is configured in the iFlow, save the procedure and deploy it. If all goes fine, you should have an available endpoint that represents your iFlow.
If y'all want to get the entry_point of the Integration Process you tin go to "Operations View" > "Manage Integration Content" > "All". Select your iFlow and go the Endpoint that should be at that place.
Classical option to get the endpoint
In that location is another pick to get the URL if you lot use ConVista CPI Helper tool. Just click Info when y'all are inside the iFlow and you will see the URL there.
ConVista Helper tool
Notation: This process follows Request-reply pattern and if you want to larn more nigh patterns you tin follow my colleague Bhalchandra Wadekar with his serial of web log posts about Enterprise Patterns (EIPinCPI).
4. Exam E2E scenario
With the iFlow deployed, you can consume the endpoint using Postman or any other Residual client tool (in this case I volition be using Postman).
Postman insert phone call
As you can come across, the bulletin sent above shows that y'all tin send any SQL argument and it will be run in the Database. After the insert has been triggered, y'all 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 plant them and how can be solved, as far equally I faced, these are the almost 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 connexion to the host localhost, port 1105 has failed. Mistake: "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 afterward.". –> You take to alter the TCP SSL to TCP
- If you are getting this result in the iFlow:
com.sap.it.rt.adapter.http.api.exception.HttpResponseException: An internal server error occured: Mistake while getting Datasource Service: ServiceReference is null. –> You have an wrong 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.information technology.rt.adapter.http.api.exception.HttpResponseException: An internal server error occured: The port number 1108/CPI is non valid.. –> Y'all have an incorrect JDBC URL in the JDBC Data Source configuration and probably information technology has non deployed properly. You don't accept "; DatabaseName=<DatabaseSchema>;" added in the JDBC URL or is non added properly.
- If yous 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 get-go mail and could accept some mistakes, whatsoever feedback is appreciated.
The Driver Received an Unexpected Pre-login Response
DOWNLOAD HERE
Source: https://blogs.sap.com/2020/12/18/cpi-connectivity-with-ms-sql-server-on-prem-using-cloud-connector/
Posted by: galvanknowerfe.blogspot.com