Technical Articles
SAP Datasphere: On Premise SFTP connectivity
Hello,
Introduction:
In this blog post we will be discussing the steps required to setup connectivity between SAP Datasphere (formerly Data Warehouse Cloud) and SFTP data source hosted on-premise.
Components involved:
- SAP DWC/Datasphere
- SAP Cloud Connector – on premise
- SFTP server – on Premise
The connectivity type used will be ‘Generic SFTP’ and it will support ‘Data flows’.
Pre requisites:
- The firewall rules must be adjusted to allow SAP Cloud connector to connect with SFTP server.
- SAP Datasphere is allowed to connect with SAP CC.
- You must have access to Cloud Connector administration.
- You must also have administration credentials on SAP DWC/Dataspehere.
Step 1: SAP Datashphere environment
Collect details of your DWC environment – log in DWC -> System -> Administration -> Data Source Configuration
make note of account information, subaccount, Region, user and also make sure that you have the password for the subaccount user.
Subaccount Information
Step 2: SAP Cloud Connector
Log in to SAP Cloud Connector – Click on ‘Connector’ -> Add Subaccount
Fill the subaccount information picked from DWC in Step1
Take note of the ‘Location ID’. It will be used later in the steps.
Location ID is defined by us, it is a unique ID for the account.
When the account is set-up we can select to switch account to view its status
Step 3: SAP Cloud Connector
In the Cloud Connector -> select sub account and then click ‘Cloud To On-Premise’
On the ‘Access Control’ tab click +
For SFTP connectivity (or for any other non-SAP connectivity) – select the following options
Fill in the SFTP server details
On the next screen – define a virtual host and port. The virtual host and port will be used for connectivity on the cloud side.
Review summary and finish the setup
To check and validate the connectivity from Cloud Connector
Step 4: SAP Datasphere
Log on to the DWC environment : Go to ‘system’ -> ‘administration’ -> ‘Data Source Configuration’
Click – ‘Add a new location’
Add the cloud connector location ID which we created in SAP Cloud Connector subaccount setup.
click create.
Next,
Click on ‘Connections’ -> navigate to the appropriate SPACE ID which you want to use for connectivity.
click ‘create’
select the connection type -‘Generic SFTP’
Fill the respective fields
*Note that the host key will be provided by the SFTP administrator.
Procedure to generate the host key is explained here
*Authentication can be user/password based or it can also be SSH type.
*Desired root path can also be defined for browsing objects.
Finalize the connection
‘validate’ the connection from DWC
Successful connection will return the following
Conclusion:
Source data on the SFTP servers can easily be accessed using the procedure defined above.
Data Flows are enabled without the need to set any additional connection properties.
Troubleshooting:
Cloud Connector Troubleshooting, logs and traces:
Update the trace level by clicking edit button and select the highlighted parameters
Ljs trace and traffic trace will be helpful in troubleshooting scenarios.
Regards,
Shoeb
—
References:
https://help.sap.com/docs/SAP_DATASPHERE/9f804b8efa8043539289f42f372c4862/5454a8cfdb9845a9b6c772d63b8e92ec.html
https://help.sap.com/docs/SAP_DATASPHERE/9f804b8efa8043539289f42f372c4862/70ee87c19d3b47459ac7f2c8d4fea48a.html
https://help.sap.com/docs/SAP_DATASPHERE/9f804b8efa8043539289f42f372c4862/2f80b57359254b5cbb4faac794ade90c.html
https://help.sap.com/docs/SAP_DATASPHERE/9f804b8efa8043539289f42f372c4862/35141e7668774958b5e59495f7a7828e.html
Hi,
I get this error when I try to connect.:
"Data Flows: Cause: Possible causes of error: Detailed error message (com.jcraft.jsch.jschexception: reject hostkey: xxxxxxxxxx) Code:1103002"
In the ljs_trace.log file there is not much information except: "The specified network name is no longer available." o "Error trying to obtain a certificate from the client javax.net.ssl.SSLPeerUnverifiedException: peer not authenticated"
On SAP side, I don't find evidence of this specific error between Dataspere and Cloud Connector/FTP.
Any suggestions?
Piergiorgio
Hello Pier,
This looks like a problem with your hostkey file.
Please generate your SFTP host key exactly as it is described here -
https://help.sap.com/docs/SAP_DATASPHERE/9f804b8efa8043539289f42f372c4862/5454a8cfdb9845a9b6c772d63b8e92ec.html
Any other format or renaming of the file will not do.
Regards
Shoeb
Hi Shoeb,
we have already tried to generate the key via ssh-keyscan (both manually from my pc and asking the sftp administrator) and using the public one. in both cases the error is the same.
Obviously both in .txt format, as indicated by the documentation.
Regards
Piergiorgio
Hello Pier,
Also make sure that your SFTP administrator allows access both via
-username and password
-username and cert
Hi Piergiorgio,
did you solve that issue? I have same.
Thankyou
Vito
Hi,
is Cloud Connector mandatory for Generic SFTP connectivity? Can DWC connect just using public host, port and hostkey with user/password?
Thank you.
Vito
hi,
Yes, absolutely!
The SFTP source should be on cloud/publicly hosted.
regards,
Shoeb
Hi Shoeb,
thank you, I intended if it is possible to connect with on-premise sftp server without cloud connector.
Regards,
Vito
Hello Vito,
No, that's not possible.
Regards,
Shoeb
Hi Shoeb Khan,
Great article. Nice and detailed. I just have a few questions in mind. Please correct me if I'm wrong.
My Understanding:
I read that SFTP server (like filezilla) can store structured/semistructured files like text files,CSV, XML,JSON etc.
In one of the Datasphere documentations that I've read, datasphere doesn't support remote tables/replication flows and can support only dataflows.
My Questions:
With Dataflows using SFTP as the source, what kind of source table will it be? (Local/Remote),
Can we perform Datarun using the Data live from SFTP server?
My Request:
I would like to very much see a sample screenshot on how the interface of SFTP would look like and also the screenshot from dataflow screen and the various options it allows.
With regards,
Siva Prakash