cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
davidSIT Frequent Visitor
Frequent Visitor

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Moderator v-yuezhe-msft
Moderator

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.
12 REPLIES 12
Highlighted
Moderator v-yuezhe-msft
Moderator

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.
davidSIT Frequent Visitor
Frequent Visitor

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 v-yuezhe-msft
Moderator

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.
davidSIT Frequent Visitor
Frequent Visitor

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 v-yuezhe-msft
Moderator

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.
davidSIT Frequent Visitor
Frequent Visitor

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 v-yuezhe-msft
Moderator

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.
davidSIT Frequent Visitor
Frequent Visitor

Re: ODBC Connection Error - Driver Error

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 v-yuezhe-msft
Moderator

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.

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 96 members 1,520 guests
Please welcome our newest community members: