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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
mtstewart17
Frequent Visitor

Enterprise Gateway with ODBC

Hello,

 

I have an ODBC source that I use for several Dashboards. I would like to setup a Gateway in order to keep the data up to date. The source is an IBM DB2 database. (I know that connecting straight to the database would be better than ODBC, but ODBC is my only option) 

 

I am wondering how I set this refesh up. I see the gateway setup prompts for a connection string and I am not sure what I need to include there to make the connection. I have a DSN fully configured on my computer's ODBC Data Source Administrator, but not sure how to format this into a connection string. 

 

Any guidance would be appreciated.

14 REPLIES 14
Anonymous
Not applicable

Hi Everyone,

 

I was trying to set up a Data Source in Power BI Service with AWS Athena via ODBC data source. And was finally successful.

 

It's very important that Power BI Gateway is running via user PBIEgwService

 

For this reason, to make AWS authentication (and data source creation) work, you need to add IAM credentials in the folder for this user:

 

C:\Users\PBIEgwService\.aws

 

you will have to create a folder ".aws"

 

Note, that in order to create such a folder, you should actually try to create a folder ".aws." - windows will cut off the last dot and you will receive a folder ".aws"

 

Then, in this folder, you will need to create a file with AWS IAM Credentials. The name of the file should be "credentials" with no extension.

 

The contents of the file should be:

 

[YourIAMUser]

aws_access_key_id=AKIAIOSFODNN7EXAMPLE

aws_secret_access_key=wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY

arielgasek
Regular Visitor

Hi,

 

I have a system DSN set up (64-bit) using vertica driver. It works well.

I can easly connect to the DB via Power BI Desktop, however when I try to create a datasource using this DSN in Gateway manager I'm facing following issue:

 

Untitled.jpg

 

Can you help me figure out how to define the string correctly?

 

What should be the connection string format when connecting to Oracle DB?

 

@arielgasek,

Ensure that you create a system DSN rather than a user DSN.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you very much, it worked for me, I was trying to put the scheduled update via enterprise gateway to work for days

Thank you very much! That was the answer for me!

I didn't have a system DSN 

I did that, it's working well, I can connect via Desktop App easly, just Gateway MAnager does not get me to create new source.

Below screen from my sys DSN

 

Thanks

ariel

 

Untitled.jpg 

If you go to "Edit Queries" then select your ODBC data table and click the "Advanced Editor" option you can see more detail on the DSN string

I just tried the same thing and received the same error.  I'm guessing the DSN needs to be set up on the gateway server.  I am going to try this to see if this resolves the issue.

This is how it worked for me:

1. local machine and gateway server dns need to be set up the same way, name specifically must be the same, as well as schema, table etc. This will ensure both connection strings are the same

2. the database needs to have a view created for the data you're trying to extract, so you can refer to the view while creating the dashboard in desktop app and create the same source under gateway manager

3. as long as both sources are the same the scehduler will see the gateway source.

 

Rgds

Ariel

Thank you Ariel for the information, this is very helpful.

 

@curtismob

Hello All,

 

I am trying to set my Power BI Gateway and I receive below error. I am able to connect to ODBC from Power BI Desktop.

 

I have tried to configure ODBC via Manage gateway but have received errors. Appreciate any help.

 

Please find below error.

 

image.pngimage.pngimage.png

I'm getting the same error, did you figure this out?

 

Error:

 

Unable to connect: We encountered an error while trying to connect to . Details: "We could not register this data source for any gateway instances within this cluster. Please find more details below about specific errors for each gateway instance."

Try to download On-premises data gateway (personal mode). It worked for me.

v-yuezhe-msft
Employee
Employee

Hi @mtstewart17,

Based on your description, you want to add ODBC data source under gateway. In this case, you can input the connection string following the steps below.

1. Check your DSN name(testodbc1 in my scenario) in ODBC administrator or in Power BI Desktop if you have connected to the odbc data source from Power BI desktop.
1.PNG

2. Add data source under gateway by entering connection string , account and password as shown in the following screenshot .
2.png

3. Schedule refresh for the dataset in Service.
3.png

In addition, please ensure that you create a system dsn in ODBC administrator .

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors