"SSL peer certificate or SSH remote key was not OK" error occurred

[daisuke-ootaka]

Hi,

I've tried to use the Excelerator Add-in today, but when I connect to snowflake server, following error occurred. Does anybody have an idea how to resolve following error?

OS: Windows10 Pro Excel: Office 2016 (64bit) ODBC Driver: snowflake64_odbc-2.22.2.msi

Best Regards,

[sfc-gh-gmullen]

This is usually because your network appliance is trying to inspect the encrypted traffic. Work with your network team and make sure you you added the SELECT$WHITELIST() endpoints to your list of allowed exceptions on your network appliance or firewall.

[daisuke-ootaka]

Thank you for your reply.

I'm working from home now, and I don't configure any special settings on my router. There is no issue with snowcd and snowsql, so it looks like no network access issue for the endpoints.

c:\tmp>snowcd allowlist.json
Performing 30 checks for 12 hosts
All checks passed

SnowCD (Connectivity Diagnostic Tool) — Snowflake Documentation https://docs.snowflake.com/en/user-guide/snowcd.html

*I also tried it with the firewall off but the result was the same.

[ssegal100]

I'm looking into other possibilities, but would you double check that your Excel is actually 64bit. I haven't seen a 64 bit version even though the OS is. Also, are you connecting through a VPN?

[daisuke-ootaka]

My Excel is looks like 64bit, but I will also try with 32bit snowflake odbc driver.

In addition, I'm connected to the internet directly without VPN. I will try with another network connection too.(smartphone tethering)

[daisuke-ootaka]

My Excel is looks like 64bit, but I will also try with 32bit snowflake odbc driver.

I uninstalled the 64bit odbc driver and tried to install the 32bit odbc driver. Then I got an error saying that the driver could not be found, so my Excel seems to be correct with 64bit.

I will try with another network connection too.(smartphone tethering)

I also tried this, but the result did not change.

[sara0529]

Hi All,

I'm facing the same issue. I'm currently using 64 bit windows 10 pro and I have no restriction at snowflake firewall layer for my IP. But still I'm getting code error 400 and I cannot successfully connect.

Note - I select SSO authentication and submit the connect request, automatic browser page is opening (as expected) and validating my connection with myOkta. However, connection is Unsuccessful

Appreciate your help or comments in fixing this.

[ssegal100]

Sorry for the delay on these issues. @DaisukeOtaka It does look like you have the correct driver. @sara0529 Would you please check if Excel is 64bit?

Do you guys know how to find the ODBC log files? Would you post them here? Thanks.

[sara0529]

Hi Segal,

I'm yet to check the MS-excel version for the machine where I'm facing error (code 400). However, I've tried using different machine (64-bit OS and 64-bit excel version, as shown in the screenshot below) and Excelerator is working successfully.

Please let me know what is the General software prerequisite to use Excelerator from any windows 10 64-bit OS machine, so that I will check the same in other machines.

[ssegal100]

@sara0529 Sorry, but it's not clear. Are you saying you have not checked it yet? Is that version from a different machine? How did you test the connection that is working? The only prereqs should be the 64bit ODBC driver, which sounds like what you have.

Let me know if you can get the ODBC log files. Thanks.

[daisuke-ootaka]

@ssegal100

Do you guys know how to find the ODBC log files? Would you post them here?

I've acknowledged. I will try to get the ODBC log later.

[daisuke-ootaka]

@ssegal100 These are my ODBC log files. *Due to GitHub's attachment file extension restrictions, I renamed "snowflake_odbc_curl.dmp" to "snowflake_odbc_curl.dmp.log".

snowflake_odbc_connection_0.log snowflake_odbc_driver.log snowflake_odbc_generic0.log snowflake_odbc_curl.dmp.log

ref.) FAQ: Where is the Snowflake ODBC log file containing Tableau-generated SQL? https://community.snowflake.com/s/article/faq-where-is-the-snowflake-odbc-log-file-containing-tableau-generated-sql

Should I download the pem file from somewhere and update it?

2020-12-07T13:35:39Z.580        [thread-5848]   Info    Hostname kya38912.us-east-1.snowflakecomputing.com was found in DNS cache.
2020-12-07T13:35:39Z.584        [thread-5848]   Info      Trying 3.232.20.32:443....
2020-12-07T13:35:39Z.587        [thread-5848]   Info    TCP_NODELAY set.
2020-12-07T13:35:39Z.770        [thread-5848]   Info    Connected to kya38912.us-east-1.snowflakecomputing.com (3.232.20.32) port 443 (#0).
2020-12-07T13:35:39Z.774        [thread-5848]   Info    ALPN, offering http/1.1.
2020-12-07T13:35:39Z.790        [thread-5848]   Info    successfully set certificate verify locations:.
2020-12-07T13:35:39Z.792        [thread-5848]   Info      CAfile: C:\Program Files\Snowflake ODBC Driver\etc\cacert.pem.  CApath: none.
2020-12-07T13:35:39Z.793        [thread-5848]   Info    TLSv1.3 (OUT), TLS handshake, Client hello (1):.
2020-12-07T13:35:40Z.133        [thread-5848]   Info    TLSv1.3 (IN), TLS handshake, Server hello (2):.
2020-12-07T13:35:40Z.135        [thread-5848]   Info    TLSv1.2 (IN), TLS handshake, Certificate (11):.
2020-12-07T13:35:40Z.137        [thread-5848]   Info    TLSv1.2 (OUT), TLS alert, unknown CA (560):.
2020-12-07T13:35:40Z.139        [thread-5848]   Info    SSL certificate problem: unable to get local issuer certificate.
2020-12-07T13:35:40Z.141        [thread-5848]   Info    Closing connection 0.

[ssegal100]

@DaisukeOtaka Thank you. I'm looking at it now. Do you know who the Solution Engineer is that supports your account?

[daisuke-ootaka]

@ssegal100 I'm sorry, I found the reason of this issue. This was my PC environment issue.

Step1. update cacert.pem I downloaded the file from the following site and updated it, but there was no change.

curl - Extract CA Certs from Mozilla https://curl.haxx.se/docs/caextract.html

Step 2. Change my antivirus soft (ESET) settings. I turned off the ESET setting called "Enable SSL/TLS protocol filtering" and it works fine.

2020-12-07T14:21:26Z.917        [thread-5309]   Info    TLSv1.3 (OUT), TLS handshake, Client hello (1):.
2020-12-07T14:21:27Z.095        [thread-5309]   Info    TLSv1.3 (IN), TLS handshake, Server hello (2):.
2020-12-07T14:21:27Z.097        [thread-5309]   Info    TLSv1.2 (IN), TLS handshake, Certificate (11):.
2020-12-07T14:21:27Z.134        [thread-5309]   Info    TLSv1.2 (IN), TLS handshake, Server key exchange (12):.
2020-12-07T14:21:27Z.141        [thread-5309]   Info    TLSv1.2 (IN), TLS handshake, Server finished (14):.
2020-12-07T14:21:27Z.152        [thread-5309]   Info    TLSv1.2 (OUT), TLS handshake, Client key exchange (16):.
2020-12-07T14:21:27Z.155        [thread-5309]   Info    TLSv1.2 (OUT), TLS change cipher, Change cipher spec (1):.
2020-12-07T14:21:27Z.157        [thread-5309]   Info    TLSv1.2 (OUT), TLS handshake, Finished (20):.
2020-12-07T14:21:27Z.334        [thread-5309]   Info    TLSv1.2 (IN), TLS handshake, Finished (20):.

[KB3126] Disable SSL filtering in ESET Windows products https://support.eset.com/en/kb3126-disable-ssl-filtering-in-eset-windows-products

Thank you so much for your kind support!

[ssegal100]

Oh great to hear. Thanks for letting me know! Connectivity issues are always tough to track down. Now that you are connected, let me know if you have any questions with the Excelerator.

[ssegal100]

@sara0529 Would you open another issue so we can close out this one? Do you know who your Solution Engineer is that is supporting your account? Thanks.

[daisuke-ootaka]

The original issue has been resolved, so I'm going to close this ticket.

@sara0529 If the problem persists, please open another issue.