Unable to create a Remote Source from Hana database (Linux) to Microsoft SQL Server database (Win)

[#1]

Dear Experts,

we have installed a Data Provisioning Agent and we have connected it to a Hana Database.
Now on the Hana Database, we need to create a Remote Source to a Microsoft SQL Server database, running on a Windows Server platform.

Unfortunately, creating a remote source in Hana Studio seems not to be working anymore (we only see the indexserver, but not the dpagent). Hana Cockpit is not available.

As a workaround, we are creating the remote source using a SQL statement:

CREATE REMOTE SOURCE "YourRemoteSourceName"
ADAPTER "MssqlLogReaderAdapter"
AT LOCATION AGENT "YourAgentName"
CONFIGURATION '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<ConnectionProperties>
<PropertyEntry name="Data Server (serverName[\\instanceName])">YourServerAddress:YourPort\\YourInstanceName</PropertyEntry>
<PropertyEntry name="Database name">YourDatabaseName</PropertyEntry>
<PropertyEntry name="User Name">YourUsername</PropertyEntry>
<PropertyEntry name="Password">YourPassword</PropertyEntry>
</ConnectionProperties>'
WITH CREDENTIAL TYPE 'PASSWORD'
USING '<CredentialEntry name="db_credential">
<user>YourUsername</user>
<password>YourPassword</password>
</CredentialEntry>';

The statement executes successfully, the remote source is being created, but unfortunately it does not work 😞

In the log file of the Data Provisioning Agent framework.trc, we have the following error:

[ERROR] [62f2fa52-f6a9-499b-b57f-e87d77d6ba1a28772] DPFramework | WorkerThread.processRequest [] - com.sap.hana.dp.adapter.sdk.AdapterException: Adapter validation failed. Failed to validate properties. Error(s):
Property [Password] is mandatory.
Property [Data Server (serverName[\instanceName])] is mandatory.
Property [User Name] is mandatory.
Property [Database name] is mandatory. Context: com.sap.hana.dp.cdcadaptercommons.validation.ValidationException: Failed to validate properties. Error(s):
Property [Password] is mandatory.
Property [Data Server (serverName[\instanceName])] is mandatory.
Property [User Name] is mandatory.
Property [Database name] is mandatory.
at com.sap.hana.dp.cdcadaptercom...

PLEASE provide help and advice! Many thanks!

[xiaoming_wu]

The official tool to setup remote source connection for SDI is through web ide or web development workbench, you can choose to use any of the tools, or create through sql command, check the syntax below in our help document.
https://help.sap.com/docs/HANA_SMART_DATA_INTEGRATION/7952ef28a6914997abc01745fef1b607/444b1a8775834...

For hanastudio, this is a known issue to only show index server and since it is not offical tool for SDI, there won't be fix for that.

Best regards
Helen

[mamartins]

You need to install first the MS SQL ODBC client on the HANA DB server (Linux) in order to be able to connect.

The official documentation is here:  https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-fo...

[symon_braunbaer]

Hello mamartins, many thanks for your answer! I think your suggestion is for ODBC connection. Up to my knowledge, this is only suitable for SDA, not for SDI. And we are using the MssqlLogReaderAdapter. Do we have to use a different adapter ? According to my knowledge, there is no other adapter suitable for SDI in connection with a Microsoft SQL Server database. Is our SQL statement for creating the remote source correct ? Many thanks!

[Andreas_Zet]

The MssqlLogReaderAdapter is handled via the DPAgent and does not need any installation of drivers on the HANA DB Server. You should checkl the framework.trc file on the DPAgent box to get more informations about the root cause.