Snowflake Loader Setup: SSL error

I am trying to run the snowflake loader setup, but getting an SSL error at the login step. Has anyone seen this before?

My config:

{
  "schema": "iglu:com.snowplowanalytics.snowplow.storage/snowflake_config/jsonschema/1-0-2",
  "data": {
    "name": "Snowflake ETL config",
    "awsRegion": "us-west-1",
    "auth": {
        "roleArn": "arn:aws:iam::091040842070:role/SnowflakeLoadRole",
        "sessionDuration": 900
    },
    "manifest": "snowplow-snowflake-manifest",
    "snowflakeRegion": "us-west-2",
    "database": "snowplowdb",
    "input": "s3://snowplow-good/",
    "stage": "snowplow_stage",
    "badOutputUrl": "s3://snowplow-transformed-s3/badrow",
    "stageUrl": "s3://snowplow-transformed-s3/stage",
    "warehouse": "snowplow_etl_wh",
    "schema": "atomic",
    "account": "cxa00000",
    "username": "snowplow_loader",
    "password": {
        "ec2ParameterStore": {
            "parameterName": "snowplow.snowflake.password"
        }
    },
    "maxError": 1,
    "purpose": "ENRICHED_EVENTS"
  }
}

And the error I am getting:

java -jar snowplow-snowflake-loader-0.5.0.jar setup --config snowflake_etl.conf --resolver iglu_resolver_swflake.json
Setting up...
Feb 21, 2020 2:09:54 AM shadeaws.profile.path.cred.CredentialsLegacyConfigLocationProvider getLocation
WARNING: Found the legacy config profiles file at [/root/.aws/config]. Please move it to the latest default location [~/.aws/credentials].
Feb 21, 2020 2:09:55 AM net.snowflake.client.jdbc.RestRequest execute
WARNING: Exception encountered for: POST https://cxa00000.us-west-2.snowflakecomputing.com/session/v1/login-request?databaseName=snowplowdb&schemaName=atomic&warehouse=snowplow_etl_wh&requestId=1c22b12f-6038-4cfb-98ce-004ae0c3a0ad HTTP/1.1
javax.net.ssl.SSLPeerUnverifiedException: Certificate for <cxa00000.us-west-2.snowflakecomputing.com> doesn't match any of the subject alternative names: [*.snowflakecomputing.com, *.global.snowflakecomputing.com]
	at net.snowflake.client.jdbc.internal.apache.http.conn.ssl.SSLConnectionSocketFactory.verifyHostname(SSLConnectionSocketFactory.java:467)
	at net.snowflake.client.jdbc.internal.apache.http.conn.ssl.SSLConnectionSocketFactory.createLayeredSocket(SSLConnectionSocketFactory.java:397)
	at net.snowflake.client.jdbc.internal.apache.http.conn.ssl.SSLConnectionSocketFactory.connectSocket(SSLConnectionSocketFactory.java:355)
	at net.snowflake.client.jdbc.internal.apache.http.impl.conn.DefaultHttpClientConnectionOperator.connect(DefaultHttpClientConnectionOperator.java:142)
	at net.snowflake.client.jdbc.internal.apache.http.impl.conn.PoolingHttpClientConnectionManager.connect(PoolingHttpClientConnectionManager.java:359)
	at net.snowflake.client.jdbc.internal.apache.http.impl.execchain.MainClientExec.establishRoute(MainClientExec.java:381)
	at net.snowflake.client.jdbc.internal.apache.http.impl.execchain.MainClientExec.execute(MainClientExec.java:237)
	at net.snowflake.client.jdbc.internal.apache.http.impl.execchain.ProtocolExec.execute(ProtocolExec.java:185)
	at net.snowflake.client.jdbc.internal.apache.http.impl.execchain.RetryExec.execute(RetryExec.java:89)
	at net.snowflake.client.jdbc.internal.apache.http.impl.execchain.RedirectExec.execute(RedirectExec.java:111)
	at net.snowflake.client.jdbc.internal.apache.http.impl.client.InternalHttpClient.doExecute(InternalHttpClient.java:185)
	at net.snowflake.client.jdbc.internal.apache.http.impl.client.CloseableHttpClient.execute(CloseableHttpClient.java:83)
	at net.snowflake.client.jdbc.internal.apache.http.impl.client.CloseableHttpClient.execute(CloseableHttpClient.java:108)
	at net.snowflake.client.jdbc.internal.apache.http.impl.client.CloseableHttpClient.execute(CloseableHttpClient.java:56)
	at net.snowflake.client.jdbc.RestRequest.execute(RestRequest.java:141)
	at net.snowflake.client.core.HttpUtil.executeRequestInternal(HttpUtil.java:271)
	at net.snowflake.client.core.HttpUtil.executeRequest(HttpUtil.java:225)
	at net.snowflake.client.core.SessionUtil.openSession(SessionUtil.java:882)
	at net.snowflake.client.core.SFSession.open(SFSession.java:328)
	at net.snowflake.client.jdbc.SnowflakeConnectionV1.<init>(SnowflakeConnectionV1.java:239)
	at net.snowflake.client.jdbc.SnowflakeDriver.connect(SnowflakeDriver.java:344)
	at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:677)
	at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:189)
	at com.snowplowanalytics.snowflake.loader.connection.Jdbc$.getConnection(Jdbc.scala:64)
	at com.snowplowanalytics.snowflake.loader.Initializer$.run(Initializer.scala:25)
	at com.snowplowanalytics.snowflake.loader.Main$.main(Main.scala:25)
	at com.snowplowanalytics.snowflake.loader.Main.main(Main.scala)

Feb 21, 2020 2:09:55 AM net.snowflake.client.jdbc.RestRequest execute
WARNING: Null response for request=POST https://cxa00000.us-west-2.snowflakecomputing.com/session/v1/login-request?databaseName=snowplowdb&schemaName=atomic&warehouse=snowplow_etl_wh&requestId=1c22b12f-6038-4cfb-98ce-004ae0c3a0ad HTTP/1.1

Did I mis-configure something? Or should I talk to the folks at Snowflake about this?
I’m thankful for any help!

1 Like

As an update, I’ve talked to the technical support team at Snowflake and it looks like the “long” URL (account.region.snowflakecomputing.com) is not supported once a short URL (account.snowflakecomputing.com) was created. That’s why I’m getting SSL errors for the long version that the Snowflake loader is using to login. They also told me that for accounts in US West, the long URL would be account.prod2.region.snowflakecomputing.com. So it seems the Snowflake loader is not up to date with how Snowflake URLs work.

I guess more people will be affected by this in the future, so probably we need to change the config and/or the way the URL is constructed.
What would be a good way for me to fix this quickly? I guess I’d have to find the source code where the URL is constructed and then remove the region? Then build the loader myself?

1 Like

Technical support at Snowflake told me that all US West accounts are in the us-west-2 region. Therefore we should change this line in the snowflake-loader code:

if (config.snowflakeRegion == "us-west-1")
            s"${config.account}.snowflakecomputing.com"
1 Like

Hi @boba,

Thank you very much for the report. As you probably already noticed in the code, you can use jdbcHost property in config to overwrite the endpoint. At the same time, I definitely agree we should update our defaults, I created a ticket to address that.

It’s a bit strange that we were not aware of this problem, none of pipelines we manage (to my knowledge) experienced this problem. Also vendors usually contact us when they deprecate a component or configuration option.

1 Like

I’ve experienced the same thing. Try visiting https://oua13326.us-west-2.snowflakecomputing.com/ in a browser and see the SSL certificate warning. If you proceed it’s a 403 forbidden error.

On this Snowflake docs page: https://docs.snowflake.com/en/user-guide/intro-regions.html#specifying-region-information-in-your-account-hostname

  • expand the Account name examples by region and you will see that the AWS us-west-2 region is the only region that does not include the region in the URI.

It is also mentioned in their JDBC docs: https://docs.snowflake.com/en/user-guide/jdbc-configure.html#connection-parameters

<region_id>Deprecated

Specifies the ID for the region where your account is located.

This parameter is no longer used because the region information, if required, is included as part of the full account name. It is documented here only for backward compatibility.

It’s a fairly recent change. I’ve experienced the same thing with Metabase’s JDBC connection to Snowflake which is at least as old as June 2019: see https://github.com/metabase/metabase/issues/10142

I did try leaving the “snowflakeRegion” blank in my config but then the URI resolves to this invalid URI https://oua13326..snowflakecomputing.com

Thanks for pointing out the jdbcHost workaround, I will try that.


UPDATE: The jdbcHost works great,
e.g. "jdbcHost": "<snowflake_account_name_goes_here>.snowflakecomputing.com",

2 Likes