Skip to Content
Technical Articles
Author's profile photo Shoeb Khan

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%20Information

Subaccount Information

 

Step 2: SAP Cloud Connector

Log in to SAP Cloud Connector – Click on ‘Connector’ -> Add Subaccount

Cloud%20Connector

 

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

 

 

 

 

Assigned Tags

      10 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Piergiorgio Basso
      Piergiorgio Basso

      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

      Author's profile photo Shoeb Khan
      Shoeb Khan
      Blog Post Author

      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

      Author's profile photo Piergiorgio Basso
      Piergiorgio Basso

      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

      Author's profile photo Shoeb Khan
      Shoeb Khan
      Blog Post Author

      Hello Pier,

      Also make sure that your SFTP administrator allows access both via

      -username and password

      -username and cert

      Author's profile photo VITO FRANCO
      VITO FRANCO

      Hi Piergiorgio,

      did you solve that issue? I have same.

       

      Thankyou

      Vito

      Author's profile photo VITO FRANCO
      VITO FRANCO

      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

      Author's profile photo Shoeb Khan
      Shoeb Khan

      hi,

       

      Yes, absolutely!

      The SFTP source should be on cloud/publicly hosted.

       

       

       

       

       

       

       

      regards,

      Shoeb

      Author's profile photo VITO FRANCO
      VITO FRANCO

      Hi Shoeb,

       

      thank you, I intended if it is possible to connect with on-premise sftp server without cloud connector.

       

      Regards,

      Vito

      Author's profile photo Shoeb Khan
      Shoeb Khan
      Blog Post Author

      Hello Vito,

      No, that's not possible.

       

      Regards,

      Shoeb

      Author's profile photo Siva Prakash
      Siva Prakash

      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