ODBC Connection Error - Driver Error

[davidSIT]

I've been trying to connect an ODBC database to the gateway in Power BI service.

I've been successful in connecting to this in Power BI Desktop, i've tried to replicate this process using the Power BI Service gateway.

It is returning an error complaining about locating the ODBC driver, I have confirmed I referenced the driver name correctly as seen in Power BI desktop:

Attempts in Power BI Gateway, windows authentication is used on power bi desktop to connect to the data source so i've used the same credentials:

This error is returned:

I have confirmed the DSN and driver name in ODBC Data Source Admin.

[davidSIT]

Hi, a late reply but I hope to help someone when including this information which is a fix for the issue I was having in my previous post here...

I'd like to add that I discovered the reason it wouldn't let me select On-premises gateway before, it was because I had a few CSV files included as part of the published report, these CSVs also need to be added to the Gateway as a File, pointing to the CSV.

Once I had done this, it was possible to select an On-premises gateway.

[v-yuezhe-msft · Accepted solution]

Hi @davidSIT,

The error is caused by that you create User DSN in ODBC administrator. Please create a System DSN to points to your data source in in ODBC administrator, then add the ODBC data source under gateway. When adding a ODBC data source under gateway, make sure you enter the same connection string as that you get in Power BI Desktop. For more details, please review this similar thread: http://community.powerbi.com/t5/Integrations-with-Files-and/Personal-Gateway-X-On-Premises-Gateway/m... .

Thanks,
Lydia Zhang

[shunsho]

Thank you! works for me!

[davidSIT]

Thanks for that, I suppose Power BI only accepts 64-bit drivers for data sources?

I had to create a 64-bit DSN and use the 64-bit driver 'Microsoft Access dBASE Driver' where I was trying to use a 32-bit driver 'Microsoft dBase Driver' with a 32-bit DSN which gave mismatch between Driver and Application error, this can be seen in:

http://community.powerbi.com/t5/Integrations-with-Files-and/Gateway-Error-ODBC/m-p/157887#M9516

[v-yuezhe-msft]

Hi @davidSIT,

Yes. Power BI gateway only accepts 64-bit drivers for data sources.

Thanks,
Lydia Zhang

[davidSIT]

I have the gateway setup successfully, however I had to use a personal gateway as opposed to an on-premises gateway as it couldn't connect or find an on-premises gateway.

I've read that ODBC isn't supported for Live/Direct and i'm not sure whether that affects the compatability, but should it be possible to use an ODBC dataset with on-premises gateway?

I'm not sure what i'd gain from using on-premesis as opposed to personal when it comes to ODBC.

[v-yuezhe-msft]

Hi @davidSIT,

What connection string do you enter when adding the ODBC data source under gateway? Does your dataset only contain the ODBC data source?

Thanks,
Lydia Zhang

[davidSIT]

I only enter dsn=SIT_PBI

I've configured the System DSN to point to a specific directory on the server machine, and I didn't require a Driver= string as that's set in ODBC config as well.

[v-yuezhe-msft]

Hi @davidSIT,

In your PBIX file, I note that you also specify dbq. Also add the dbq part when entering connection string under gateway.

Thanks,
Lydia Zhang

[davidSIT]

Hi Lydia,

I removed the personal gateway installation and reinstalled the on-premesis gateway and recovered the gateway.

I added the data source back in using the dbq= string and the dsn= string, which was successful.

However, in the settings of my dataset it's only asking/recognising for a personal gateway.

EDIT: I've also tried removing the existing gateway from the dashboard and re-installing on-premesis gateway, creating an entirely new gateway with a different name, then added the data source back, yet the dataset still asks for a personal gateway. Perhaps I should try remove the dataset and republishing it?

[v-yuezhe-msft]

Hi @davidSIT,

Do you use Windows authentication method when creating the ODBC data source? If so, is there any possibility that you use basic authentication method? Based on my test, I have to use same authentication method when creating ODBC data source and adding ODBC data source under gateway.

Thanks,
Lydia Zhang

[davidSIT]

Yes I use my Windows credentials when I import the ODBC data source in Power BI Desktop and do the same in Gateway Data source on the web.