Steps to Connect PostgreSQL to Azure Data Factory using ODBC method

Steps to connect to PostgreSQL database using Azure Data Factory as a Linked Service?

I was banging my head to fix this issue and had no proper documentation that could help me. It was throwing the same SSL error again and again when I used source as PostgreSQL option on Data factory. Below is the error for your reference.

>> ERROR [28000] [DataDirect][ODBC PostgreSQL Wire Protocol driver]Cannot load trust store

However, I found an alternative solution to connect to PostgreSQL using Azure Data factory as Linked service using ODBC method. This is for source only. You will need couple of other applications apart from Data factory to perform this activity.

Pre-requisites:

1. ODBC Drivers for PostgreSQL on your machine (Machine could be the VM or on-prem server which is up and running all the time)

2. Self-Hosted Runtime integrator ( Download it from Microsoft website using this link https://www.microsoft.com/en-us/download/details.aspx?id=39717 )

3. Azure Data Factory

4. Whitelist your IPs of your VM location on PostgreSQL server

Step 1:

Download and Install ODBC drivers to your machine

https://www.postgresql.org/ftp/odbc/versions/msi/

Scroll down for the latest versions

Install drivers. It might ask for a restart to use the drivers.

Go to start menu and find ODBC drivers

Click on Add button and new window will ask for create a data source. Select any PostgreSQL and click finish.

It opens a new window to create a driver setup for PostgreSQL. Select the appropriate SSL certificate mode.

Save the Data source name from above. You will be using in the Final Step as a DSN.

Step 2:

Install self-hosted Integration run-time. Follow the instructions from Microsoft website.

https://www.microsoft.com/en-us/download/details.aspx?id=39717

Once you open it you will see the below window to configure Azure Data Factory with runtime integrator

Step 3: Get the Azure Authentication Key from Data Factory

Go to connections Tab in the bottom of Author page and click Create New connections in Integration Runtimes tab as shown below

Then, Select Perform data movement option

Next, Select Self-Hosted

Now, Name it . Make sure you name it right because you cannot edit it later and it should be unique in each data factory

Copy One of these Keys

Paste them in the Register window from step-2. You will see the successful connection window as below

You can gohead and test the connection using Diagnosis tab. Use the same details that you have used in step1 to create the ODBC drivers

Step 4:

Its time to create the new Linked service. Go to Azure Data Factory >> Author >> Connections >> Create New connection on Linked services tab as below

New side menu pops up to select the linked service. Search for ODBC. I know you want to search for PostgreSQL as I did but microsoft is some issue with it. If you have connected using postgresql then you are lucky. I was unable to do it even after 1000 trys.