Gateway - Unable to connect to data source

[Anonymous]

Hello

I am trying to set up an on-premises data gateway. Everything seems to be working fine, and I am able to create data sources (the green check appears, ✔️ Connection successful) using an ODBC connecting string like this:

driver={PostgreSQL Unicode(x64)};server=myserver.com;port=5432;database=my_db

However, when I try to refresh the data from my datasets I get an error:

Refresh failed due to gateway configuration issues.

If you're using enterprise mode, please make sure you've added the following data sources in the Gateway Management Portal

ODBC { connectionString : "driver={PostgreSQL Unicode(x64)};server=myserver.com;port=5432;database=my_db;dsn=dBASE Files" }

As you can see, the only difference here is the dsn-related attribute. However, when I try to add the suggested connection, I am not able, I get this error:Error message when trying to add "dsn=dBASE Files" to the query sting

I would appreciate any help. Thanks in advance

[Anonymous · Accepted solution]

Thank you @Anonymous, as you pointed out "Power bi service has different security and policy so some of the connection string works on desktop, but failed on power bi service side"

In my case, I was using a User DSN. It works in Power BI Desktop and seemed to work in Power BI Service since I get a "Connection successful" message, but it didn't.

I had to add a System DSN using the ODBC DataSource Administrator tool. And then, use it in my connection string:

driver={PostgreSQL Unicode(x64)};server=myserver.com;port=5432;database=my_db;dsn=MyNewSystemDsn

Now it is working like a charm

Thanks for your help

Jose

[Anonymous]

HI @Anonymous,

Maybe you can try to use other type of connecting strings or add optional properties into connection string.

PostgreSQL connection strings 
Power bi service has different security and policy so some of the connection string works on desktop, but failed on power bi service side due to inaccurate connection properties definition and driver version compatibility.

Power BI Security#data-storage-security 

Regards,

Xiaoxin Sheng

[Greg_Deckler]

So, you created that ODBC connection? And it is the right number of bits, 32/64? 

[Anonymous]

Yes, the ODBC connection string (without the dsn part) is working fine (connection successful). 

But when I try to refresh my data, an error occurs and PowerBI asks me to add a connection with the same string + "dsn=dBase Files". If I try to add that suggested connection, I get the error you see in the image.