Extract CSV from SFTP with public key authentication

  • 1 March 2023
  • 3 replies

We have a SFTP source on which CSV files are stored and which can be authenticated to with a ‘public key’. When using the CData SFTP-connector in ODX Server, we are able to connect with the following settings:


The most important setting here is the SSH Auth mode, where we specify that we are using PublicKey authentication. Since I am not familiar with this type of connector, I would not now how to retrieve the data from the CSV files (the only table we get from the source now is the ‘Root’ table that contains the FileNames that can be found in the folder).

I would rather use the CData CSV-connector, but that leaves me with a problem when testing the connection: I cannot specify that I want to use the PublicKey authentication mode. That is, it is specified under ‘SSH Auth Mode’ but it is not possible to select under ‘Auth Scheme’:


As a result, we keep getting the same error on testing the connection (see attached file). I have tried setting the Auth Scheme to ‘None’, ‘Auto’, ‘SFTP’ or ‘Basic’ but the problem persists. 

How can we connect to the SFTP with the Public Key?


Best answer by KajEmergo 2 March 2023, 11:57

View original

3 replies

Userlevel 3
Badge +5

Hi Kaj

It should work with the same options as for SFTP.

Only workaround I know about is using the SFTP provider and adding a stored procedure query table that downloads the CSV file. That is not a better way and I think using an external program to download the csv files to a local folder would work better.

I will contact CData for some help with this.

Hi Thomas, 

I found the issue: the ‘SSH Auth Mode’ was inherited when I changed the CData provider from SFTP to CSV. But it contained a different value than what it should have been for the CSV connector. I noticed this when I added a separate data source and used the CSV connector immediately, you could only use ‘Public_Key’ from the SSH Auth Mode dropdown, whereas it was ‘PublicKey’ in the SFTP connector. So this went wrong in TX interface when changing the CData connector. Apparently they use different values for the same type of setting…


Different value for SFTP connector:


Userlevel 3
Badge +5

Hi Kaj

Good find, I have closed our task with CData.