Connect Postgre on GCP using Azure Data Factory

Question

Connect Postgre on GCP using Azure Data Factory

Lokesh 211

We need to source data from postgre database hosted on GCP using Azure Data Factory.
We were asked to use SSL to connect and fetch data from GCP Postgre.

While creating the Linked Service we are not sure how to accomplish this. Any help would be really appreciated.

KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2022-12-19T01:23:00.987+00:00

Hello @Lokesh ,

Thanks for the question and using MS Q&A platform.

As per the connectors documentation ADF has connectors for only PostgreSQL and Azure Database for PostgreSQL. I don't think there is a dedicated connector for Google Cloud SQL for PostgreSQL.

I'm not exactly sure if you can use ADF PostgreSQL connector to establish a connection to Google Cloud SQL for PostgreSQL but you can give a try.

Could you please confirm what connector are you trying to explore for this requirement as there is no direct connector for it?

Thanks.
Lokesh 211 Reputation points

2022-12-19T09:50:38.117+00:00

The connector works fine, however, we need to upload an SSL certificate.
I am not sure how should we do that. Please look at the screenshot provided above.

1 answer

Your answer

KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2022-12-19T01:23:00.987+00:00

Hello @Lokesh ,

Thanks for the question and using MS Q&A platform.

As per the connectors documentation ADF has connectors for only PostgreSQL and Azure Database for PostgreSQL. I don't think there is a dedicated connector for Google Cloud SQL for PostgreSQL.

I'm not exactly sure if you can use ADF PostgreSQL connector to establish a connection to Google Cloud SQL for PostgreSQL but you can give a try.

Could you please confirm what connector are you trying to explore for this requirement as there is no direct connector for it?

Thanks.
Lokesh 211 Reputation points

2022-12-19T09:50:38.117+00:00

The connector works fine, however, we need to upload an SSL certificate.
I am not sure how should we do that. Please look at the screenshot provided above.

Answer 1

Hello @Lokesh ,

Thanks for your response and additional clarification. In order to perform SSL Certificate validation to establish connection to your PostgreSQL, you will have to use ODBC connector instead of PostgreSQL connector in ADF.

In order to have full SSL verification via the ODBC connection using the Self Hosted Integration Runtime you must use an ODBC type connection instead of the PostgreSQL connector explicitly, and complete the following configuration:

Set up the DSN on any SHIR servers.
Put the proper certificate for PostgreSQL in C:\Users\DIAHostService\AppData\Roaming\postgresql\root.crt on the SHIR servers. This is where the ODBC driver looks > for the SSL cert to verify when it connects to the database.
In your data factory connection, use an ODBC type connection, with your connection string pointing to the DSN you created on your SHIR servers.

This has been called out in the PostgreSQL connector documentation as a NOTE: ADF PostgreSQL Connector configuration details

Hope this info helps.

KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2023-01-05T17:53:19.613+00:00

Hello @Lokesh ,

Following up to see if the above information was helpful to setup your configuration. And, if you have any further query do let us know.

Share via

Connect Postgre on GCP using Azure Data Factory

1 answer

Your answer