Forum Discussion
Gateway - Unable to connect to data source
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
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=MyNewSystemDsnNow it is working like a charm
Thanks for your help
Jose
4 Replies
- Greg_Deckler
Community Champion
So, you created that ODBC connection? And it is the right number of bits, 32/64?
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.
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
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=MyNewSystemDsnNow it is working like a charm
Thanks for your help
Jose
