BigQuery ODBC error

[trenslow]

Hi community,

I'm trying to use the BigQuery connector to query a BigQuery table. My organization has a policy that dictates that a Customer-Managed Encryption Key (CMEK) must be specified for all data stored in Google Cloud.  In this case, data is temporarily stored when running a query on a BigQuery table, so a CMEK is needed.

If I run the query

SELECT * FROM <project_name>.<dataset_name>.<table_name>

I get the error:

Details: "ODBC: ERROR [HY000] [Microsoft][BigQuery] (100) HTTP Error 412: conditionNotMet (Your administrator requires that you specify an encryption key for queries in project `<REDACTED>`. See https://cloud.google.com/bigquery/docs/customer-managed-encryption#services_constraint for more info.)."

This is expected, as I didn't specify the CMEK on the query. The only ways to specify the key is to use the Query settings (UI, see picture), the BigQuery CLI or the BigQuery client libraries from e.g. Python.

As far as I can tell, there's no way to do it with Native SQL queries, which is the interface to BigQuery that the Power BI connector provides.

Has anyone else solved this issue? Either way, I would like to request that the BigQuery connector includes a field where the CMEK key can be specified.

Thanks for the consideration

[lbendlin]

If this is important to you please consider voting for an existing idea or raising a new one at https://ideas.fabric.microsoft.com

[trenslow]

I would be happy to raise an idea if someone can confirm that it's impossible to set the CMEK on the BigQuery connector.