Company Firewall

Updated 2 weeks ago by kevin cardoen

Troubleshooting corporate firewall / network restrictions

In corporate environments, network security (firewalls, proxies, cloud security services and SSL/TLS inspection) can block the connection between your tools (Power BI, Excel, ODBC or JDBC clients) and SD Worx Insights Cloud, which runs on Snowflake.

1. Symptoms

1.1 Certificate error at login

The connection fails immediately, with password login as well as with SSO:

OLE DB or ODBC error: [DataSource.Error] ERROR [HY000] [Snowflake][Snowflake] (4) REST request failed: CURLerror (curl_easy_perform() failed) - code=60 msg='SSL peer certificate or SSH remote key was not OK'

1.2 Certificate error when loading larger data sets

Login works and small queries work, but larger refreshes fail:

OLE DB or ODBC error: [DataSource.Error] ERROR [HY000] [Snowflake][Snowflake] (25) Result download worker error: Worker error: [Snowflake][Snowflake] (4) REST request for URL <...> failed: CURLerror (curl_easy_perform() failed) - code=60 msg='SSL peer certificate or SSH remote key was not OK'

Small query results are returned directly by the Snowflake account endpoint. Larger results are downloaded from the Snowflake storage endpoint (Azure Blob Storage). If only the account endpoint is correctly configured, login works but larger refreshes fail.

1.3 How to read the error code

Note: if the error is identical with password login and with SSO login, it occurs before authentication. User accounts, SSO configuration and SD Worx access rules are then not involved.

2. Cause: SSL/TLS inspection

Many companies inspect encrypted traffic: a proxy, firewall or endpoint security agent decrypts the connection and re-signs it with a company certificate. The traffic is allowed, but it is no longer the original Snowflake certificate that reaches the workstation.

• Web browsers accept the company certificate, because the company root certificate is installed in the Windows certificate store. This is why Snowflake URLs often open normally in a browser.
• The Snowflake ODBC driver does not use the Windows certificate store. It validates certificates against its own certificate bundle (cacert.pem in the driver installation folder), which does not contain the company certificate. The driver therefore refuses the connection.

Important: allowing the Snowflake endpoints in a firewall rule is not sufficient. They must also be excluded from SSL/TLS inspection.

3. Solution: endpoints to allow and exclude from inspection

Ask your network team to create an exclusion (do not decrypt rule) for the endpoints below on every proxy, firewall or endpoint security agent on the path between the workstation and the internet.

3.1 Required

3.2 Recommended

Port 80 endpoints are plain HTTP certificate status checks: no inspection applies, they only need to be reachable. If they are not, the connection is not blocked, but it can be slower and generate warnings.

3.3 Additional points

• SSO users: the Microsoft login endpoint (login.microsoftonline.com) must also be reachable. In most Microsoft environments this is already the case.
• Per-application policies: some security tools apply rules per application. The exclusion must then cover Power BI Desktop, Excel and, if used, the on-premises data gateway server.
• Other Snowflake endpoints: the web interface, development tools and MFA enrollment endpoints are not needed for Power BI or Excel.

4. Diagnose the issue

Run these checks from an affected workstation, connected to the company network as in normal use. Check 1 confirms the cause, check 2 shows which endpoints are affected, check 3 is only needed if the first two are inconclusive.

Check 1: certificate issuer (2 minutes)

Open https://kx42541.west-europe.azure.snowflakecomputing.com in a web browser and display the certificate details. If the certificate is issued by your company or by your security product (for example a "Root CA" or "Issuing CA" with your company or vendor name) instead of a public certificate authority (DigiCert), SSL/TLS inspection is confirmed.

Check 2: Snowflake connectivity diagnostic

1. Install Snowflake CLI: Snowflake CLI installation.
2. Save the content below as C:\temp\allowlist.json.
3. Create a connection: run snow connection add and enter account kx42541.west-europe.azure, your user and the login method you normally use. Other prompts can be left empty. The login method has no influence on the result.
4. Run the diagnostic:

   snow connection test -c <connection name> --enable-diag --diag-log-path C:\temp\snowdiag --diag-allowlist-path C:\temp\allowlist.json

5. Review the report SnowflakeConnectionTestReport.txt with your network team, and send it to SD Worx if needed. The report is generated even if the connection test fails.

allowlist.json (endpoints used by Power BI and Excel):

[
{"host":"kx42541.west-europe.azure.snowflakecomputing.com","port":443,"type":"SNOWFLAKE_DEPLOYMENT"},
{"host":"sdworx-insights.snowflakecomputing.com","port":443,"type":"SNOWFLAKE_DEPLOYMENT_REGIONLESS"},
{"host":"mbxbxesfcb1stg.blob.core.windows.net","port":443,"type":"STAGE"},
{"host":"client-telemetry.snowflakecomputing.com","port":443,"type":"OUT_OF_BAND_TELEMETRY"},
{"host":"ocsp.snowflakecomputing.com","port":80,"type":"OCSP_CACHE"},
{"host":"ocsp.digicert.com","port":80,"type":"OCSP_RESPONDER"},
{"host":"oneocsp.microsoft.com","port":80,"type":"OCSP_RESPONDER"},
{"host":"ocsp.rootca1.amazontrust.com","port":80,"type":"OCSP_RESPONDER"},
{"host":"ocsp.rootg2.amazontrust.com","port":80,"type":"OCSP_RESPONDER"},
{"host":"ocsp.r2m01.amazontrust.com","port":80,"type":"OCSP_RESPONDER"},
{"host":"ocsp.r2m02.amazontrust.com","port":80,"type":"OCSP_RESPONDER"},
{"host":"ocsp.r2m03.amazontrust.com","port":80,"type":"OCSP_RESPONDER"},
{"host":"ocsp.r2m04.amazontrust.com","port":80,"type":"OCSP_RESPONDER"},
{"host":"ocsp.sca1b.amazontrust.com","port":80,"type":"OCSP_RESPONDER"},
{"host":"crl3.digicert.com","port":80,"type":"CRL_DISTRIBUTION_POINT"},
{"host":"crl4.digicert.com","port":80,"type":"CRL_DISTRIBUTION_POINT"},
{"host":"crl2.microsoft.com","port":80,"type":"CRL_DISTRIBUTION_POINT"},
{"host":"www.microsoft.com","port":80,"type":"CRL_DISTRIBUTION_POINT"}
]

Note: if your security tool applies policies per application, Snowflake CLI and Power BI may be treated differently on the same workstation. If check 2 succeeds while Power BI still fails, review per-application rules and run check 3.

Check 3: ODBC driver log

1. Open the registry editor as administrator and go to:
   • 64-bit driver: HKEY_LOCAL_MACHINE\SOFTWARE\Snowflake\Driver
   • 32-bit driver: HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Snowflake\Driver
2. Add the string values LogLevel=6, CurlVerboseMode=true and LogPath=C:\temp\snowlog.
3. Reproduce the error, then open C:\temp\snowlog\Snowflake_odbc_curl.dump with a text editor.
4. Remove the registry values afterwards.

Search for "Connected to" followed by the Snowflake account or storage host. A connection affected by SSL/TLS inspection looks like this:

Info CAfile: C:\Program Files\Snowflake ODBC Driver\etc\cacert.pem
Info TLSv1.3 (IN), TLS handshake, Certificate (11):
Info TLSv1.3 (OUT), TLS alert, unknown CA (560):
Info SSL certificate problem: self signed certificate in certificate chain

A correct connection continues with "TLS handshake, Finished" and "SSL connection using TLSv1.x". The CAfile line also shows which certificate bundle the driver actually uses.

5. Temporary workaround

If the exclusion cannot be implemented quickly, your IT team can add the company root certificate to the certificate bundle used by the driver (the CAfile shown in the log of check 3). Append the certificate to the existing file; do not replace the file. This has to be done on every workstation and is overwritten by driver and Power BI updates, so it should only be used as a temporary measure. Disabling certificate validation is not supported.

6. Driver versions

Keep the Snowflake ODBC and JDBC drivers up to date. Outdated drivers can miss current root certificates, which produces similar certificate errors even without SSL/TLS inspection.

7. Still blocked?

Contact data-insights@sdworx.com and include:

• the full error message;
• the result of check 1 (certificate issuer);
• the diagnostic report of check 2 and, if available, the log of check 3;
• the name of the proxy or security solution used for internet traffic, and whether SSL/TLS inspection is enabled.

Any feedback?