Restore Cloud Composer default connection

[lorenh]

In Cloud Composer 2 (Version: 2.9.3+composer), I started getting a warning in several of my tasks `WARNING - Connection schemes (type: google_cloud_platform) shall not contain '_' according to RFC3986.` I went into my Connections and set the bigquery_default connection type to Google Bigquery instead of Google Cloud, not realizing it was a default connection. Now I'm getting an error:  

google.api_core.exceptions.BadRequest: 400 GET  https://bigquery.googleapis.com/bigquery/v2/projects/[REDACTED]&prettyPrint=false:  Some rows belong to different partitions rather than destination partition

When i click the url, i get a page with this message:

{
  "error": {
    "code": 401,
    "message": "Request is missing required authentication credential. Expected OAuth 2 access token, login cookie or other valid authentication credential. See https://developers.google.com/identity/sign-in/web/devconsole-project.",
    "errors": [
      {
        "message": "Login Required.",
        "domain": "global",
        "reason": "required",
        "location": "Authorization",
        "locationType": "header"
      }
    ],
    "status": "UNAUTHENTICATED",
    "details": [
      {
        "@type": "type.googleapis.com/google.rpc.ErrorInfo",
        "reason": "CREDENTIALS_MISSING",
        "domain": "googleapis.com",
        "metadata": {
          "method": "google.cloud.bigquery.v2.JobService.GetQueryResults",
          "service": "bigquery.googleapis.com"
        }
      }
    ]
  }
}

 How do I restore the default credentialed connection for Cloud Composer?

[NorieRam]

Hi @lorenh,

Welcome to Google Cloud Community!

The BigQuery authentication error you're seeing in your Cloud Composer environment is due to a lack of proper authorization, not a misconfigured connection. The solution involves verifying the service account associated with your Cloud Composer environment has the necessary BigQuery permissions (BigQuery User or BigQuery Job User) within the IAM settings of your BigQuery project. It also emphasized checking for network restrictions and, as a last resort, regenerating the service account key. The core issue isn't restoring a "default connection" but ensuring the service account has the correct permissions to access BigQuery.

Was this helpful? If so, please accept this answer as “Solution”. If you need additional assistance, reply here within 2 business days and I’ll be happy to help.

[lorenh · Accepted solution]

Yes, I realize this error is related to authorization. As 1) the same composer SA was previously granted permissions in BigQuery, 2) the connection had the proper authorization without issue in multiple DAGs for over a year, and 3) I only began to see this error after I made a change to a default connection and saved, I remain confident that it is related to me accidentally overwriting the default authorization credentials stored in the connection. I filed a support ticket last week and they let me know that Google isn't able to restore, so I had to create a new connection with the SA key.