Snowflake Loader Setup: SSL error

boba · February 21, 2020, 2:17am

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!

boba · February 21, 2020, 6:56pm

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?

boba · February 21, 2020, 8:16pm

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"

github.com

snowplow-incubator/snowplow-snowflake-loader/blob/master/loader/src/main/scala/com.snowplowanalytics.snowflake.loader/connection/Jdbc.scala#L38


object Jdbc {


  def init[F[_]: Sync]: Database[F] = new Database[F] {
    def getConnection(config: Config): F[Database.Connection] = Sync[F].delay {
      Class.forName("net.snowflake.client.jdbc.SnowflakeDriver")


      // US West is default: https://docs.snowflake.net/manuals/user-guide/jdbc-configure.html#jdbc-driver-connection-string
      val host = config.jdbcHost match {
        case Some(overrideHost) => overrideHost
        case None =>
          if (config.snowflakeRegion == "us-west-1")
            s"${config.account}.snowflakecomputing.com"
          else
            s"${config.account}.${config.snowflakeRegion}.snowflakecomputing.com"
      }
      // Build connection properties
      val properties = new Properties()


      val password = config.password match {
        case Config.PasswordConfig.PlainText(text) => text
        case Config.PasswordConfig.EncryptedKey(Config.EncryptedConfig(key)) =>

anton · February 22, 2020, 2:55am

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.

davehowell · March 26, 2020, 9:27am

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: Supported Cloud Regions | Snowflake Documentation

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: Configuring the JDBC Driver | Snowflake Documentation

<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 Snowflake Connector Requires Deprecated Region Id · Issue #10142 · metabase/metabase · GitHub

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",

Topic		Replies	Views
Snowplow snowflake loader ssl error For engineers	1	714	October 7, 2022
Snowflake Loader Setup Error For engineers	1	978	June 4, 2019
Issue with snowflake transformer/loader Troubleshooting	7	1173	March 4, 2021
Snowflake Transformer failing exitCode: 15 Troubleshooting	4	1381	December 16, 2021
Step [rdb_load] stdout: Configuration error Attempt to decode value on failed cursor: DownField(sslMode) Troubleshooting	10	2288	November 6, 2019

Snowflake Loader Setup: SSL error

Related topics