Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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 stingError message when trying to add "dsn=dBASE Files" to the query sting

 

I would appreciate any help. Thanks in advance

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Thank you @v-shex-msft, 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

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

Thank you @v-shex-msft, 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

v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Greg_Deckler
Super User
Super User

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.