"SSL peer certificate or SSH remote key was not OK" error occurred
daisuke-ootaka opened this issue · 16 comments
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,
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.
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.
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?
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)
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.
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.
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.
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.
@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.
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.
@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.
@DaisukeOtaka
Thank you. I'm looking at it now.
Do you know who the Solution Engineer is that supports your account?
@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!
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.
@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.
The original issue has been resolved, so I'm going to close this ticket.
@sara0529
If the problem persists, please open another issue.