Connect to Simba ODBC Big Query through Gateway

[alexbjorlig]

My objective is to develop and publish a power bi report to app.powerbi.com, that connects to Big Query.

The connection is done with the Simba ODBC driver, and is using Service Authentication (a JSON key file).

I have installed this above driver on the same computer running the Power BI Gateway.

Everything work's great locally. But after publishing to the powerbi service, I get the following error when trying to refresh in powerbi service:

Underlying error code-2147467259
Underlying error messageThe credentials provided for the Odbc source are invalid. (Source at dsn=analytics_google.)

The gateway is running fine, and looks like this:

The Simba driver looks like this:

I tried updating the permissions from none/private/organizational --> but no help.

What can I do to fix the issue?

[FranciscoCosta · Accepted solution]

I could configure it using SIMBA ODBC Driver to BigQuery

Setps:
1- Create a service account for BigQuery, keep the email and JSON file with Keys
2- Install the ODBC driver locally and on the Server that have the Gateway the driver
https://cloud.google.com/bigquery/docs/reference/odbc-jdbc-drivers

3- Configure the ODBC connection locally and on the gateway server, just open the ODBC (32 or 64 depending the driver you choosed) and edit putting the email and path to JSON keys:
 

4- On PowerBI Desktop choose ODBC has source 

5 - Choose Google BigQuery data sourse If you want to put a query go to advance options else just continue

6- Build your report and upload it.
7- Go to gateway management portal power bi and add a new data source of ODBC type
https://docs.microsoft.com/en-us/data-integration/gateway/service-gateway-manage
https://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-data-sources
Configure like this (choose anynonymous authentication):

And it Works 🙂

[ambarishkunte]

Have you found a way to connect ?? Does it work if we use the actual Big Query username & password  if not with the service account ?

[RogerHenriques]

@alexbjorlig , any progress? I am in a very similar situation here, also using a service account to connect.

[alexbjorlig]

nope - I gave up 😢 I don't think this way of connecting is currently supported.

[lbendlin]

Have you tried using the built-in BigQuery connector instead?

[lbendlin]

I got bad news for you.  Power BI gateway is already using SIMBA ODBC drivers for some of the connectors, in a very clandestine and frankly disturbing way (for example they do not show up in the ODBC settings dialogs on the machine). So you basically installed a second, competing driver.

Uninstall your driver, reinstall the gateway, and try connecting to the data source on the gateway again.  Or find a better driver, native or ODBC.

[alexbjorlig]

Thanks for your answer.

Problem here is that I'm trying to connect to Big Query, using a service account. So I don't think that is possible using any other ODBC driver - after all, the SIMBA ODBC driver is the recommended by Google.