Reply
Frequent Visitor
Posts: 11
Registered: ‎04-24-2017
Accepted Solution

ODBC Connection Error - Driver Error

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:

 

2017-04-24_11-48-26.jpg

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:

2017-04-24_11-52-59.jpg

This error is returned:

2017-04-24_11-51-59.jpg

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

2017-04-24_11-54-18.jpg2017-04-24_12-23-19.jpg


Accepted Solutions
Highlighted
Moderator
Posts: 9,878
Registered: ‎03-10-2016

Re: ODBC Connection Error - Driver Error

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

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.

View solution in original post


All Replies
Highlighted
Moderator
Posts: 9,878
Registered: ‎03-10-2016

Re: ODBC Connection Error - Driver Error

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

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.
Frequent Visitor
Posts: 11
Registered: ‎04-24-2017

Re: ODBC Connection Error - Driver Error

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

Moderator
Posts: 9,878
Registered: ‎03-10-2016

Re: ODBC Connection Error - Driver Error

Hi @davidSIT,

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

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.
Frequent Visitor
Posts: 11
Registered: ‎04-24-2017

Re: ODBC Connection Error - Driver Error

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.

 

Moderator
Posts: 9,878
Registered: ‎03-10-2016

Re: ODBC Connection Error - Driver Error

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

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.
Frequent Visitor
Posts: 11
Registered: ‎04-24-2017

Re: ODBC Connection Error - Driver Error

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.

Moderator
Posts: 9,878
Registered: ‎03-10-2016

Re: ODBC Connection Error - Driver Error

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

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.
Frequent Visitor
Posts: 11
Registered: ‎04-24-2017

Re: ODBC Connection Error - Driver Error

[ Edited ]

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?

Moderator
Posts: 9,878
Registered: ‎03-10-2016

Re: ODBC Connection Error - Driver Error

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

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.