Did anyone connect Power BI to BigQuery using a service account? (with refresh of data working?)

[alexbjorlig]

Hi everyone.

I have a working Power BI app in desktop that uses the simba ODBC driver to authenticate against BigQuery using a service account.

It does not work in app.powerbi.com.

Does anyone actually have a working solution for a PowerBI app connecting to Big Query using a service account?

[janinebp]

Hi Alex, 

I am able to (Up until yesterday anyway -- weird new issues today) 

When you set up the ODBC connector you gotta use a connection string . See here: https://www.simba.com/products/BigQuery/doc/v2/ODBC_InstallGuide/win/content/odbc/bq/strings.htm

Let me know if this helps

[alexbjorlig]

Hi @janinebp ! 

You give me hope! Would you mind sharing your setup for this? Do you use the gateway --> and how does your connection-string look like?

[janinebp]

Hey! Yes I use a (personal) data gateway. My connnection string looks like this

OAuthMechanism=0;Email=powerbi-reader@project-name.iam.gserviceaccount.com;KeyFilePath=c:\path\to\credentials\powerbi-reader-credentials.json;Catalog=project-name;

And then set up the ODBC data source as usual

hope this helps!

[alexbjorlig]

I think the problem might be the personal data gateway. I'm using the non-personal gateway, and here it does not work. Might be because I don't have the Authentication method called "WindowsWithoutImpersonation". I also had a rather long support case with Microsoft, where they gave up on making it work 🤔

[janinebp]

😞 That sucks. I guess I am going to run into your issue eventually... I'll have a look at the normal gateway and will let you know if I find any workaround.

[janinebp]

Hi @alexbjorlig 

In case you're not aware (I just recently found out, the official BigQuery connector now allows for Service Account authentication also. This might help you!

Good luck!

Janine

[alexbjorlig]

I will give it a spin - thanks for keeping the post updated 🙂 

[collinq]

Hi @alexbjorlig ,

Were you able to make sure that the setup was as designed in this article Connect to a Google BigQuery database in Power BI Desktop - Power BI | Microsoft Docs ?  The article states that this works in Desktop and Service.

[alexbjorlig]

The linked article is only about user authentication, and not service accounts.