Query BQ External Table from Compute Engine

[arunkumarjca]

Hi 

Team,

While access from VM using individual ID/User define service account ,we are able to access the data from BQ (has external table with Gsheet) . When trying with default service account /service account attached with VM (by give full Cloud api scope) getting theBigQuery: Permission denied while getting Drive credentials.

Any lead please

[ms4446]

The error message "BigQuery: Permission denied while getting Drive credentials" indicates that the service account you're using to access BigQuery doesn't have the necessary permissions to access the Google Sheet linked as an external table.

Possible reasons for this error include:

• The service account lacks permission to access Google Drive.
• The service account doesn't have access to the specific Google Sheet.
• The BigQuery table isn't correctly linked to the intended Google Sheet.

To troubleshoot:

1. Double-check that the BigQuery table is linked to the correct Google Sheet. You can verify this in the BigQuery console by inspecting the external table's definition.

2. Ensure the service account has access to the specific Google Sheet. Share the sheet with the service account's email address directly from the Google Sheet's sharing settings.

3. Confirm that the service account has broader Google Drive access permissions. This might be managed at the domain level, especially if you're using Workspace (formerly G Suite).

If you've verified all the above and still encounter the error, consider checking any domain-wide restrictions or policies that might affect service account access. Creating a new service account should be a last resort, and if done, ensure it's given the necessary permissions.

[arunkumarjca]

Hi @ms4446 ,

Thanks for swift reply , 

I have double check BQ attached to Gsheet and check Gsheet permissions and all looks good . 
I could access the BQ with service account if its not attached with VM . But incase if attach the same service account with VM . i 'm getting this error . 

i have tried with domain wide delegation by adding (drive.readonly ) ,but still its same .. Do we have any issue VM attached service account to access the google drive ?

Thanks,

Arun 

[ms4446]

There has been reported issues with VM-attached service accounts having difficulties accessing Google Drive. This matter is currently being looked into. 

In the interim, you might want to consider the following workarounds:

1. Use a User-Defined Service Account:

   • Create a new service account.
   • Grant this service account the required permissions for Google Drive and BigQuery.
   • Attach this service account to your VM.

2. Use a Service Account Key File:

   • Generate a key file for your service account.
   • Set the GOOGLE_APPLICATION_CREDENTIALS environment variable to point to the path of this key file. This will override the default behavior of the Application Default Credentials.

3. Try a Different VM Instance Type:

   • Some VM instance types might be more affected by this issue than others. If possible, experiment with different instance types to identify one that works for your use case.

If you've exhausted the above workarounds and continue to encounter the error, please reach out to Google Cloud support for further assistance.

[arunkumarjca]

Hi @ms4446 ,

Thanks again for your reply . 
If possible  can i get the Issue tracker link for this  issue.

Thanks,

Arun J 

[ms4446]

Unfortunately, I could not find a public issue tracker link for this issue. However, I can confirm that this is a known issue that is currently being investigated by Google Cloud. I will continue to monitor the issue and update you as soon as more information is available.

In the meantime, I recommend that you try the workarounds that I suggested in my previous response. If you are still having trouble, you can contact Google Cloud support for assistance.

I apologize for the inconvenience caused by this issue.

[arunkumarjca]

Hi @ms4446 

Did you get any update on this ?

Thanks,

Arun