ODBC driver not found error in Power BI service when attempting to refresh data

[anisevance]

Good morning all,

I successfully created two data connections to a Power BI desktop instance that lives on-prem. The first of the data connections is to a SQL data source that is also on-prem. Let's call this source ABC. ABC has a SQL driver associated with it in System DSN. The second connection is to a PostGresSQL data source that is external to my organization. Let's call this source XYZ. This has a PostGresSQL driver associated with it in System DSN.

All of this works great from Power BI Desktop!

Then, when I upload the data to Power BI service, things get funky. The connection to ABC in Power BI service works totally fine. The connection to XYZ, however, does not. I receive the error below:

Keep in mind: the connection string is exactly the same as on the PBI desktop, the ODBC driver is installed on System DSN that the PBI Desktop lives on, and the data source credentials are identical to those on the PBI desktop.

Any ideas on what is going on where? Where is PBI service looking for drivers? 

[Greg_Deckler]

@anisevance Any chance the 32-bit driver is installed instead of the 64-bit driver? The ODBC driver needs to be installed on the gateway, not just Power BI Desktop.

[anisevance]

Great questions and thanks for the quick reply!

I just double-checked to make sure -- it is definitely the x64 bit driver that is installed. 

On the gateway question, right now, under Connections and Gateways, it has the connection for source XYZ listed as a Personal Mode (in the Gateway Cluster Column). Considering it is an external (to the org) data source, I thought that would be okay. But am I wrong there? Should it be on an on-prem data gateway?

Additionally, how do I go about installing a driver on the gateway? Should the install happen on the server that hosts the gateway?

[Greg_Deckler]

@anisevance Correct, you need to install the driver on the server that hosts the gateway. Generally for production you would use an on-prem data gateway that is not in personal mode but I don't think that is the problem here. 

[anisevance]

Got it. This is tremendously helpful. I will try two things:

1. Installing the driver on the server that hosts my "personal mode" gateway. Is this just the server that my Power BI Desktop instance lives on?

2. If the above doesn't work, I will transition the connections to our on-prem data gateway and make sure the ODBC driver is installed on the server that hosts that on-prem gateway. 

Is that about right?

[Anonymous]

Thanks for the reply from Greg_Deckler.

Hi @anisevance ,

This error is caused by the specified ODBC driver not being found when connecting to the data source. In the course of your discussion with the superuser, it seems that you have got the right idea, have you tried it in the direction you mentioned and got the expected result?

I found some links for you that I hope will be helpful:

Configure ODBC drivers for PostgreSQL (sqlshack.com)

psqlodbc - PostgreSQL ODBC driver

Best Regards,
Zhu

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!