How to connect Azure to an on-prem 3rd party database ?

Think we've got a solution (or at least a workaround)

Thanks to @Vinodh247 and @Konstantinos Passadis for their initial help with this - it pointed us in the right direction (particularly the "bridge" idea from Konstantinos Passadis).

What we wanted to do

Connect directly to our c-tree database which sits on a Linux box on-prem

Our Architecture

We have 2 servers on-prem.

Server 1 has a standard windows with the 32bit C-tree ODBC driver installed on it so it can access the C-Tree database which is on Server2.

Server 2 is a Linux box where the c-tree database resides. We can't install Runtime Integration or anything on this server.

What we did

Server 1 is able to link tables from the C-Three database on Server2 to SQL-Express on Server 1, so we know it can connect with no issues. However we want to copy data directly from C-tree (on Server 2) into Azure and were having no success.

We installed the Runtime Integration on Server 1 (since we cant do it on Server 2)

We then created the ODBC Connection in Data Factory. Entered the connection string for server 2 in the appropriate place. Happy with the idea that Server 1 was the bridge between Azure and Server 2.

And it failed to work.

It failed to work multiple times.

Despite what we did.

Important bit

Everything looked ok. The RI was installed properly. The connection string looked ok. It all seemed fine yet still kept failing with an error message saying it couldn't see the host. However, and this is important bit, when we changed the word "server" to "host" in the Data Factory connection string it successfully connected. Just that single change. One word.

Result

So now Azure can jump onto Server 1 (where the RI is installed), use the odbc driver there and connect to server 2.

Not quite a "direct" connection but probably as close as we're going to get :)

Thanks.

Hello @Craig Nixon !

Welcome to Microsoft QnA!

As we can see from the link FairCom / C-Tree database is not natively supported

https://learn.microsoft.com/en-us/azure/data-factory/connector-overview

In this case as we can read from the same link :

Integrate with more data stores

Azure Data Factory and Synapse pipelines can reach broader set of data stores than the list mentioned above. If you need to move data to/from a data store that is not in the service built-in connector list, here are some extensible options:

For database and data warehouse, usually you can find a corresponding ODBC driver, with which you can use generic ODBC connector.

For SaaS applications:

    If it provides RESTful APIs, you can use generic REST connector.

    If it has OData feed, you can use generic OData connector.

    If it provides SOAP APIs, you can use generic HTTP connector.

    If it has ODBC driver, you can use generic ODBC connector.

For others, check if you can load data to or expose data as any supported data stores, e.g. Azure Blob/File/FTP/SFTP/etc, then let the service pick up from there. You can invoke custom data loading mechanism via Azure Function, Custom activity, Databricks/HDInsight, Web activity, etc.

So the point is

Does this Database support ODBC ?

If FairCom/C-Tree supports an ODBC driver, you might be able to use the ODBC connector in Azure Data Factory to connect to your database

Other thoughts :

Rest API ,again If your FairCom/C-Tree database supports it, you could create a REST API layer to expose the data and then use the HTTP connector in Azure Data Factory to pull the data.

The error message suggests that there could be compatibility issues , Authentication or even Access and Permissions

If you are able to post some info or Screenshots it could be possible to provide additional assistance , but please be aware of the ODBC support and/or compatibility

I hope this helps!

Kindly mark the answer as Accepted and Upvote in case it helped!

Regards