Power BI certificate Error - Snowflake

[ogilabertl]

Hello everyone!

I am new to the community so I apologize if I am politically incorrect or have posted this query wrong.

The problem is the following: I am trying to connectat Power BI with Snowflake and I get the this error:

And I don't understand what is happening. Could it be a poxy issue or that I really don't have enough permissions to resume the warehouse?

P.S: I have all dimension tables in import mode and the fact table in Direct Query (it is a very large table).

Thank you in advance,

Oriol 

[amitchandak]

@ogilabertl , refer if these can help

https://community.snowflake.com/s/article/CURLerror-SSL-peer-certificate-or-SSH-remote-key-was-not-O...

https://community.powerbi.com/t5/Desktop/Power-BI-Certificate-issue-when-connecting-to-snowflake/m-p...

[ogilabertl]

I @amitchandak thanks for your quick response.

I have talked to snowflake support but this case is not of their concern (I understand perfectly, it seems to be more of a company issue.). In any case, they told me the following: It seems to be a proxy issue, more specifically

As a consequence, if you have a proxy that intercepts SSL/TLS traffic and presents its own certificate to the client (via TLS intercept or ssl proxy), this will not work.

Actions:

1. Use SnowCD from where you are trying to connect to verify the connection to the snowflake account URLs
1. Run the SYSTEM$WHITELIST, this function exports the list of URLs needed for seamless connection
2. Download and Install SnowCD (if not installed)
3. Run SnowCD: ./snowcd <path to input json file from step 1> [flags]
2. Share the output of the following command:
1. curl -v -k https://<myaccount>.<mystorage>.snowflakecomputing.com:<port>

We have performed all these tests and the SnowCD tool doesn't show any errors.

In any case, if I solve it, I will keep you informed.

KR,

Oriol Gilabert

[v-easonf-msft]

Hi, @ogilabertl 

This issue has been mentioned on the Snowflake forum.
"CURL SSL error pops up when connecting to Snowflake using the ODBC driver and reading large amounts of data. This can be seen with tools like PowerBI".

CURLERROR 'SSL PEER CERTIFICATE OR SSH REMOTE KEY WAS NOT OK' USING ODBC DRIVER CONNECTION TO SNOWFL...

Cause:

When a client pulls a smaller result, it directly gets the result from the Snowflake server. Whereas when the result set is larger (like over 1000 records), the client pulls the data directly from the internal Snowflake S3 bucket. This error is seen in these cases.

As seen from the ODBC curl dump logs show a glitch seen after the server certificate was flown to the client .

Solution :

Run  the open_ssl command to show the certificate chain while connecting to the s3 bucket endpoint as opposed to the snowflake server .

---This would  reveal a   'Verify return code: 19 (self signed certificate in certificate chain)'

---This happens when a TLS proxy presents itself and its certificate during a handshake . This confirmed the presence of a proxy in between .

 Command used: 

echo QUIT | openssl s_client -connect abcdn-customer-stage.s3.amazonaws.com:443 -showcerts

As a solution , please contact your network team and bypass the following: 

1)The  Snowflake endpoints  found through SYSTEM$WHITELIST() and/or SYSTEM$WHITELIST_PRIVATELINK() for your SSL proxy.

If the issue still exists , then in addition to the above step 

2) Bypass the S3 bucket  URL [the ones that are seen in the error stack of the error thrown ] .

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ogilabertl · Accepted solution]

Good morning, @v-easonf-msft!

Yesterday I was working closely with the cybersecurity and communications team and we think we have found the problem. It seems that the proxy and the corporate antivirus (ZScaler) were intercepting a certificate that they did not trust. Basically, the communications team has bypassed the connection, so it is now working.

Anyway, I am not connected to an Amazon Simple Storage Service (Amazon S3) instance. I am connecting to an Azure instance.

Thank you very much for replying!

KR

Oriol Gilabert