Cannot connect Azure Data Catalog to Azure Database for PostgreSQL server

Horniak, Peter 21

Hello,

Can Azure Data Catalog (ADC) connect to Azure Database for PostgreSQL Server?

I am using the local application for ADC. I am able to connect to other datasources like Azure Blob and SQL Database, to upload their metadata to ADC.

However, when I try connecting to any Postgres database, I get this error:

I installed Npgsql but am still getting the error:

Can you please advise how I can get this working?
I can see that there are multiple versions of Npgsql. I don't want to try them at random, because I need an administrator to install each one. I want to be mindful of their time.

1 answer

Anurag Sharma 17,576 Reputation points

2020-08-19T10:41:34.6+00:00

Hi @Horniak, Peter , It looks some issue with latest version of 'Npgsql'. Please install version (Setup_Npgsql-2.2.4.3-net45.exe
) from below link:
https://github.com/npgsql/Npgsql/releases/tag/v2.2.4.3

Please follow below steps:

1) Go to Connection Security under Settings for PostgreSql resource under azure portal and below updates if not done already:

2) Close Azure Data Catalog if already opened and start again.(after installing above Npgsql version)

3) Configure the property values as mentioned below. Please note the User value, it has to be in same format. You can get it from Azure Portal for PostGreSql Resoure created under 'Overview'

4). Try connecting from Azure Data Catalog now:

If an answer is helpful, please "Accept answer" or "Up-Vote" for the same which might be beneficial to other community members reading this thread.
Please sign in to rate this answer.
Anurag Sharma 17,576 Reputation points

2020-08-20T06:05:58.533+00:00

Hi @Horniak, Peter , just wanted to follow up on this. Please let us know if the solution worked out for you or if you are facing issues still.

Horniak, Peter 21 Reputation points

2020-08-21T01:37:24.237+00:00

Hi Anurag,

Thank you for your response. Today I received the installation of the Npgsql version you recommended, and the previous error went away.

The current error I am receiving is:

This error occurs when I have my client IP address permitted under a Firewall rule.

I was able to avoid the above error by changing the "Minimum TLS version" from 1.2 to 1.0. In that case I am able to proceed to choose objects to register. However, when I then try to register those objects, they fail:

Horniak, Peter 21 Reputation points

2020-08-21T02:11:11.367+00:00

Hi Anurag,

In the previous example, I had ticked the checkbox to include the Data Profile of the objects. When I deselect it, then the registration succeeds.
Can you please confirm this option is not available with Azure Database for PostgreSQL server?

I would prefer to use the minimum TLS version of "1.2". When I use "1.2" or "1.1", I get the above error about invalid credentials. Can you please advise how I can connect?
I tried adding the firewall rule for all IP addresses, but it had no effect.

Horniak, Peter 21 Reputation points

2020-08-21T06:06:01.407+00:00

Hi Anurag,

After installing that version of Npgsql, I can no longer connect Power BI to our production Azure Database for PostgreSQL server. I get the below error. I am definitely using the correct credentials.

I am able to connect to our development Azure Database for PostgreSQL server, when it is set to enable TLS1.0. However I do not wish to enable this on our production server.

When my colleague repeated these steps, and then uninstalled Npgsql, he started getting this error in his Power BI connection.

Can you please advise how we can restore our Power BI accesses?

Anurag Sharma 17,576 Reputation points

2020-08-21T08:11:47.407+00:00

Hi @Horniak, Peter , apologies for the trouble you are facing. I was looking into the issue and noticed there were few issues related to latest version of Npgsql related to assembly dependencies.

However we can still use a little older version (4.0.9) which works pretty fine and I think you should be able to connect to your production azure postgresql database resources too. Please follow below steps:

Uninstall Npgsql for any versions you have.

Install the (4.0.9) version. While installing, please select the option as mentioned in below screenshot. It is needed when we connect Azure Data Catalog with Azure PostgreSQl. Restart azure Data Catalog, you should be able to connect now with any 'minimum TLS version'

Please try these and ask your colleague to follow the same and try connecting with Power BI. If still facing any issues, please let me know and we can further look into it.

Anurag Sharma 17,576 Reputation points

2020-08-25T11:29:08.347+00:00

Hi @Horniak, Peter , please let me know if you were able to try this. The latest versions of Npgsql has some issue as mentioned in GitHub link below but with version (4.0.9) you should be able to connect with any 'minimum TLS version':

https://github.com/npgsql/npgsql/issues/2677

Horniak, Peter 21 Reputation points

2020-08-26T22:54:22.293+00:00

Hi Anurag,

Thank you for the advice. I had that version installed including with the GAC option.
I am now able to connect to the Postgres server with ADC, but when I try to publish, then I get a failure:

I've tried many variations, such as omitting the preview and tagging within ADC, and turning the minimum TLS from 1.0 to 1.2 and back. The publishing always fails for Postgres. I am able to publish for other tools, such as Azure BLOB.

I am now able to connect with PowerBI to Postgres with a minimum TLS 1.2. Thank you for resolving that issue.

Anurag Sharma 17,576 Reputation points

2020-08-27T17:59:00.177+00:00

Hi @Horniak, Peter , it is nice to know you were able to move ahead.

Regarding other issue related to registering the objects, even I am facing this now with Npgsql version. Previously I was able to successfully register with older version of Npgsql. Let me once try it out on few other versions to see if this is really the issue with Azure data Catalog. If yes, I will reach out to Product group and will escalate the same to them.

There is thread where in similar issue is discussed but not much information there too. Meanwhile can you try checking the error logs at this location '%appdata%\AzureDataCatalogErrorEventLogProd.log' and let me know please.

Anurag Sharma 17,576 Reputation points

2020-08-28T12:01:59.887+00:00

Hi @Horniak, Peter , I was able to try this out on multiple Npgsql version and the last stable version where registering the objects worked was Npgsql 3.2.2. Good thing is I was able to use minimum TLS 1.2 version with the same package. Would it be possible for you to try this version out and at the same time check if PowerBI is also connected with minimum TLS 1.2.? If this works, we have a solution.

If this does not work out, we might need to reach out to Product group and get their inputs on the same.

Below is the screenshot for object registration:

KalyanChanumolu-MSFT 8,316 Reputation points

2020-09-01T13:14:13.247+00:00

@Horniak, Peter Just checking in to see if the answer(s) helped.
If an answer is helpful, please "Accept answer" or "Up-Vote" for the same which might be beneficial to other community members reading this thread.
And If you have further questions or issues please let us know.

Horniak, Peter 21 Reputation points

2020-09-03T05:35:22.117+00:00

Hi Anurag and Kalyan,

Thank you for your responses. I uninstalled my past version of Npgsql, and installed Npgsql 3.2.2 from this link:

I then logged into the Azure Data Catalog local app, and attempted to connect to the Postgres server, and got the below error:

I received the same error on our PROD and UAT servers, and after I installed the latest Windows updates & restarted.

Thank you for the link to the forum thread and error log. I have emailed the error log to ADCQuestion at Microsoft.com.
Edit: I got an error for the email being undeliverable. Can you please advise where I should send it?

Anurag Sharma 17,576 Reputation points

2020-09-07T11:23:13.003+00:00

Hi @Horniak, Peter , if you have an active support plan you can raise a ticket there, or else please let us know and we can help you with one time free support ticket on the same.

Again when I tried I was able to register the objects again when I tried with the mentioned version. Remotely we might not be able to look into exact issue and hence support ticket is better option.

However, I shall be posting step by step screenshot in answers for anyone else facing the similar issue.

Horniak, Peter 21 Reputation points

2020-09-08T05:55:33.16+00:00

Hi @AnuragSharma-MSFT, I have raised a support ticket with the ID 120090823000778.
Thank you for your support to this point.
Sign in to comment