Connecting GCP Snowflake to Airflow certificate issue

Question

We are trying to connect to the snowflake instance using snowflake-sqlalchemy library (latest version).

Getting next error:
[2020-09-28 14:47:47,558] {{connection.py:409}} WARNING - Certificate did not match expected hostname: xxxxxxx.europe-west4.snowflakecomputing.com. Certificate: {'subject': ((('commonName', '*.us-west-2.snowflakecomputing.com'),),), 'subjectAltName': [('DNS', '*.us-west-2.snowflakecomputing.com'), ('DNS', '*.snowflakecomputing.com'), ('DNS', '*.global.snowflakecomputing.com'), ('DNS', '*.prod1.us-west-2.aws.snowflakecomputing.com'), ('DNS', '*.prod2.us-west-2.aws.snowflakecomputing.com'), ('DNS', '*.us-west-2.aws.snowflakecomputing.com')]}

Seems like the certificates for the snowflake instance do not match the host. Is there any way to resolve this issue?

This is on a trial account if that matters.

Answer 1

The airflow snowflake objects are built for AWS, and not compatible for GCP so I will need to find GCP versions or create GCP compatible versions.

Answer 2

I noticed europe-west4. Is that a GCP account? If so, I think your URL/hostname is supposed to look like this, but you can double-check in the UI:

XXXXX.europe-west4.GCP.snowflakecomputing.com

Answer 3

I think this is how you would solve the issue. The account name should also contain the gcp. as shown in the article above

{
"account":"xxxxx.us-central1.gcp",

"warehouse":"COMPUTE_WH",

"region":"us-central1",

"database":"CITIBIKE",

"schema":"PUBLIC"

}

Answer 4

As noted by @Suzy Lockwood, the domain being generated is wrong. The reason it ends up pointing to *.us-west-2.snowflakecomputing.com is because the target, lacking the gcp or azure ends up getting a redirect to us-west-2, where (of course) the certificate is wrong for what was expected.

The solution (for me) turned out to be that region needs the .azure suffix, not just the region. I'd given it that information under 'account' - I'm not sure if the presence of the region parameter got in the way, or if both are needed. But, it is working now, and I'm loathe to touch it more today. :)