PostgreSQL ODBC connection failure on service only

[AlexaderMilland]

When I'm building my report in Power BI desktop i have no trouble connection to the PostgreSQL server via a ODBC connectionstring, 

= Odbc.DataSource(
"Driver={PostgreSQL ANSI(x64)};
Server=servername;
Port=1234;
Database=dbname;
sslmode=require",
[HierarchicalNavigation=true])

However when i get to the Power BI service to schedule automatic refreshes i need to add it to the data sources and then i run into trouble. 

After clicking "add to gateway" i get here, 

Adding the database login in the "basic" login does not work and i do not have a windows login for the server. 

The password contains some odd characters like - or ? 

Not sure if those have an impact when on the PowerBI service versus on desktop. 

Any ideas how to the Power BI service to automatically refresh data from a PostgreSQL server in which i only have a database login? 

[v-mengzhu-msft]

Hi @AlexaderMilland ，

First please ensure that you are using the updated version of Power BI Gateway, and ensure the username and password you input have permission to your POSTgreSQL database.

Then ,I will give you the following docs and the content is related to how to configure POSTgreSQL database through ODBC connector in gateway , you can view for reference. REFRESHING ODBC DATASOURCE ON POWER BI SERVICE - Ruki's Blog (rukiatu.com); PostgreSQL data refresh using Data Gateway in Power BI report | by Ramu Vudugula | Medium

And you also asked if there is any other way to refresh data from  a PostgreSQL server.

May be you can try to directly connect to PostgreSQL data source without ODBC. Configuring Power BI Connectivity to PostgreSQL Database - oxcrx34285;

Best regards,

Community Support Team Selina zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[AlexaderMilland]

I'm not using a Data Gateway application, so not sure what you mean by updating? 

Regarding the use of direct postgresql access I couldn't get it to work as there was no place to specify port number and ssl=required connectivity.

[v-mengzhu-msft]

Hi @AlexaderMilland ,

What I mean by that is the gateway app that you have installed on your computer, keep it up to date.

If you can't connect directly to the POSTgreSQL data source, perhaps we still have to resort to connecting with ODBC.  Please refer to this doc and follow its steps to check if you have missed anything.

REFRESHING ODBC DATASOURCE ON POWER BI SERVICE - Ruki's Blog (rukiatu.com)

If it is convenient for you, you can also show a screenshot of your error message so that I can better understand your problem.

Best regards,

Community Support Team Selina zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[AlexaderMilland]

I do not have the SQL database hosted on my PC, It's remotely hosted, i just have a database login that allows me access. Hence I do not have a gateway app installed. Is that necessary?

[v-mengzhu-msft]

Hi @AlexaderMilland ,

Are you and your remote server in the same domain? If they are not in the same domain, you need to install gateway on your remote server (not local server).

If you are in the same domain, i.e. you have access to your remote server, then whether the gateway is installed depends on your data source is on-premise or in the cloud, and if it is local, the gateway need to be installed on your local server.

Best regards,

Community Support Team Selina zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[AlexaderMilland]

No we are not in the same domain, however installing the data gateway is not an option. Is it the only way to connect when using PowerBI Service

[v-mengzhu-msft]

Hi @AlexaderMilland ,

If you are not in the same domain, gateway is necessary to install so that you can give your server access to the datasource which is on the other remote server. 

Based on this situation, I‘ll tend to recommend you to install a gateway. If you want, you can install from this website:

Install an on-premises data gateway | Microsoft Docs

Best regards,

Community Support Team Selina zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[AlexaderMilland]

So the PostgreSQL is hosted in cloud. Do I need to have the gateway installed on my PC or on the cloud server? 

[v-mengzhu-msft]

Hi @AlexaderMilland ,

Can you access your cloud server? if can't, you should install gateway on your cloud server.

you can download it in the following doc:

Install an on-premises data gateway | Microsoft Docs

Best regards,

Community Support Team Selina zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Almiwork]

No I don't have access to the Server nor can a gateway be installed on there.

Did you mean to install the gateway on my local machine?