Issues connecting to Snowflake with FME

Introduction

Here are some known issues when attempting to connect to the Snowflake Database

Handshake Error in FME Log

Error: COM.safe.fme.jdbc.TranslationFailureException: net.snowflake.client.jdbc.SnowflakeSQLException: JDBC driver encountered communication error. Message: Exception encountered for HTTP request: Remote host closed connection during handshake.

Solution: This is likely caused by restrictive ports or URL blocking.  Instruct your IT Team opened up the ports for OCSP for Snowflake Certificates using the following documentation from Snowflake. OCSP Configuration .

Here is a great Snowflake network connectivity diagnostics tool. SnowCD

These should help with resolving the handshake error in FME.  

Account Name in the Database Connection

The account you’ll use when making a new connection to the Snowflake system will depend on the region and platform it was launched in. Check out this documentation from Snowflake for some of those values. Logging into Snowflake and Configuring the JDBC Driver.  
Essentially, if you log into the Snowflake Web Console with a URL like https://mycompany.snowflakecomputing.com then your account name used in the Database connection will consist of 'mycompany'.  If the Snowflake Web Console URL looks like https://mycompany.us-west-2.snowflakecomputing.com then the account name used in the Database Connection will consist of 'mycompany.us-west-2'.  

Error 1: An error you may see when using an incorrect account name when testing a connection in the Database Connection dailog:

net.snowflake.client.jdbc.SnowflakeSQLException: JDBC driver encountered communication error. Message: HTTP status=403.
Tip: Reveiw the Snowflake Article and determine the appropriate account name.  One way to capture this is via your Web URL address that you use for the Snowflake Web UI. Caution when using custom domains for Snowflake as often you'll need to configure access to additional Snowflake resources when connecting via FME (JDBC). Review the  Snowflake article, OCSP Responder Hosts, and determine if your client system has the required access.

Error 2: An error you may see when running a workspace if you have added snowflakecomputing.com to the account name in the Database Connection, for example, "mycompany.snowflakecomputing.com" might appear as:

JDBC driver encountered communication error. Message: Exception encountered for HTTP request: Certificate for <mycompany.snowflakecomputing.com.snowflakecomputing.com> doesn't match any of the subject alternative names: [*.us-west-2.snowflakecomputing.com, *.snowflakecomputing.com, *.global.snowflakecomputing.com, *.prod1.us-west-2.aws.snowflakecomputing.com, *.prod2.us-west-2.aws.snowflakecomputing.com, *.us-west-2.aws.snowflakecomputing.com].
Tip: Note the doubling up of the snowflakecomputing.com value in the error message.

Error 3: An error you may see when testing a database connection may appear as:
JDBC driver encountered communication error. Message: Exception encountered for HTTP request: sun.security.validator.ValidatorException: No trusted certificate found.

Tip: This may be caused by an incorrect account name and also access to 
Have your IT Team review the Snowflake article, OCSP Responder Hosts, and determine if your client system has the required access.

Reader Timeout

Complex, custom, select queries may result in a timeout when used in a WHERE or SQLExecutor. Timeout error messages many not be clear so setting the Snowflake reader timeout = 0 (no timeout) might resolve some issues. For example the message: