SNOW-1511776: PDO_Snowflake through proxy returns Failure when receiving data from the peer?

[Erik-2019]

Please answer these questions before submitting your issue.
In order to accurately debug the issue this information is required. Thanks!

1. What version of PDO/PHP driver are you using? Snowflake PHP PDO Driver: 8.2.17-3.0.0

2. What operating system and processor architecture are you using? Debian GNU/Linux 11 (bullseye)

3. What version of C/C++ compiler are you using?
   gcc 4:10.2.1-1
   cmake 3.18.4

4. What did you do?
   Tried rebuilding PDO_Snowflake
   Changed Proxy settings
   Tried from PHP as well as shell using Curl

5. What did you expect to see?
   Results from my query.

   Getting no results, PDO_Snowflake gives me the error below.

6. Can you set logging to DEBUG and collect the logs?
   See snowflake_20240628162939.txt attachment.
   snowflake_20240628162939.txt

7. What is your Snowflake account identifier, if any? (Optional)

---

I apologize if this is the wrong place to post this, if there is a better forum please let me know.

We are running a PHP server on an Azure App Service.
The account we are connecting to is https://*************.east-us-2.privatelink.snowflakecomputing.com
When trying to execute a query through PHP it's returning "Fatal error: Uncaught PDOException: SQLSTATE[08001] [240012] curl_easy_perform() failed: Failure when receiving data from the peer"

When I use Curl to send the http request to the server it returns a 302 redirect error. I solved this by adding CURLOPT_FOLLOWLOCATION to my code and it now returns the console page.

Does PDO_Snowflake follow redirects when going through a proxy server?
Is there a parameter I need to add to my code?

I was able to setup a private endpoint in azure and execute a query without the proxy, but only to test to make sure the driver works, Corporate policy wouldn't let me keep that setup.

Any help getting this working would be greatly appreciated.

TIA

[sfc-gh-dszmolka]

hi - driver's behaviour is different from when you do a manual curl against your Snowflake account URL or type the same to the browser. then indeed it redirects you with HTTP302 to /console/login, it's normal.
your driver does not take that path; it does a POST to login-request endpoint when doing the login attempt.

unfortunately the error you sent, is only the part of the actual error. it is not included what was the failure when receiving data from the peer.

can you please try configuring the PHP driver's connection as per https://github.com/snowflakedb/pdo_snowflake?tab=readme-ov-file#proxy if you need to send it through the proxy ? make sure please the proxy doesn't inject its own custom CA because then the validation will fail (if it must modify the chain, please add the proxy's own intermediary CA to cacert.pem which comes with the driver; its path is configured under pdo_snowflake.cacert)

if it still doesn't work, please enable debug logging with adding

pdo_snowflake.logdir=/tmp
pdo_snowflake.loglevel=TRACE

to the driver config, reproduce the issue then post the logs here. Please make sure to sanitize them before posting though; account name , any other sensitive information. Alternatively if you're reluctant posting that info here, you can work with a support engineer 1:1 when creating a Support Case with Snowflake.
let me know please how this went.

[Erik-2019]

Thank you for the response.
I think i've covered everything in your response. (And adding some info that I probably should have added originally)

This is what I am using in PHP (using individual proxy settings)
$dsn = "snowflake:account=$db_account;proxy=$db_proxy;";
$dbh = new PDO($dsn,"$db_username", "$db_password");
$dbh->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

This is what I have in the .ini file
; PDO_Snowflake settings
extension=///pdo_snowflake.so
pdo_snowflake.cacert=///cacert.pem
pdo_snowflake.logdir=/*//logs
pdo_snowflake.loglevel=TRACE

This is what openssl returns, I don't think the proxy is injecting it's own certificate(?).
echo | openssl s_client -connect <proxy_server>:<proxy_port> -showcerts
CONNECTED(00000003)
write:errno=104
---
no peer certificate available
---
No client certificate CA names sent
---
SSL handshake has read 0 bytes and written 315 bytes
Verification: OK
---
New, (NONE), Cipher is (NONE)
Secure Renegotiation IS NOT supported
Compression: NONE
Expansion: NONE
No ALPN negotiated
Early data was not sent
Verify return code: 0 (ok)
---

New log output :
snowflake_20240701133512.txt

[sfc-gh-dszmolka]

thanks for doing the test ! assuming there's no typo as result of sanitization process, this

account: <account>.east-us-2

looks incorrect. if you're connecting from the privatelink, please specify account as <account>.east-us-2.privatelink (without specifying region anywhere else) and if you're testing connectivity from the public link, then please use <account>.east-us-2.azure .

of course alternatively you can use the regionless notation for both privatelink (myorg-myaccount.privatelink) and public link (myorg-myaccount) - please make sure the DNS record for the privatelink one is actually configured on the cloud infra.

Speaking about which; does the same setup with the same proxy; work using the public link and only fails for the privatelink one ?
Additionally - does the same setup work for the privatelink without the proxy ?

for the openssl test; it does not really show conclusive results as your proxy seems to be working over http (not a problem, very common configuration). if you wish to use openssl to check connectivity and the cert chain -good idea- it would look something like this

echo | openssl s_client -proxy my.pro.xy:8080 -connect myorg-myaccount.privatelink.snowflakecomputing.com:443 -servername myorg-myaccount.privatelink.snowflakecomputing.com -showcerts

for the private link, but please use the appropriate server name for public/private link you wish to test. Then do the same without the -proxy to connect directly to the Snowflake account; and compare the 2 cert chains to see if there's anything different (there might or might not be)

[Erik-2019]

Let me know if I missed something from your post.
I'm new to this, but learning as quick as I can.

I added the privatelink to the account and now I get an SSL error, I think privatelink was there before, but i've tried so many different variations I probably left it out accidentily.

Region is not specified anywhere else but the account.

No Proxy :
snowflake:account=.east-us-2.privatelink; - Couldn't resolve host name
snowflake:account=.east-us-2.azure; - IP Not allowed

Proxy :
snowflake:account=.east-us-2.privatelink;proxy=<proxy_server>:<proxy_port>; - SSL server certificate status verification FAILED
snowflake:account=.east-us-2.azure;proxy=<proxy_server>:<proxy_port>; - Failure when receiving data from the peer

1. This returns 3 certificates, 2 were not in the cacert.pem file:
   echo | openssl s_client -proxy <proxy_host>:<proxy_port> -connect .east-us-2.privatelink.snowflakecomputing.com:443 -servername .east-us-2.privatelink.snowflakecomputing.com -showcerts
   I added the 2 into the cacert file.

2. This returns an error :
   echo | openssl s_client -connect .east-us-2.privatelink.snowflakecomputing.com:443 -servername .east-us-2.privatelink.snowflakecomputing.com -showcerts
   Error : 138075641091392:error:2008F002:BIO routines:BIO_lookup_ex:system lib:../crypto/bio/b_addr.c:730:Name or service not known connect:errno=22

3. This returns a forbidden error, guessing the proxy blocks it
   echo | openssl s_client -proxy <proxy_server>:<proxy_port> -connect .east-us-2.azure.snowflakecomputing.com:443 -servername .east-us-2.azure.snowflakecomputing.com -showcerts
   Returns : s_client: HTTP CONNECT failed: 403 Forbidden

4. This returns 2 certificates, 1 is the same as the 2 returned from 1 above
   echo | openssl s_client -connect .east-us-2.azure.snowflakecomputing.com:443 -servername .east-us-2.azure.snowflakecomputing.com -showcerts
   I added the new certificate into the cacert.pem file

I am able to connect and run queries through SnowSQL with the following, so I know the proxy is working.
snowsql -a .east-us-2.privatelink -u --proxy-host=<proxy_host> --proxy-port=<proxy_port>

snowflake_20240701214917.txt

[sfc-gh-dszmolka]

i started having doubts so to double-check our PHP driver still works with proxy in v3.0.0, i did the following:

1. setup a SSL-terminating proxy (mitmproxy) which injects its own CA, so the driver doesn't trust the server certificate by default; this is similar with your situation
2. started proxy on the same VM
3. setup private DNS zone in AWS + VPC Endpoint as per Snowflake privatelink setup
4. build pdo_snowflake 3.0.0 (edit: php 8.2.20) on debian 12
5. using following repro script:

$ cat snowflake.php  
<?php
  $account = "myaccount.eu-central-1.privatelink";
  $user = "username";
  $password = "password";
 
  $dbh = new PDO("snowflake:account=$account;proxy=10.20.0.37:8080", $user, $password);
  $dbh->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
  echo "Connected\n";
 
  $sth = $dbh->query("select current_version()");
  while ($row=$sth->fetch(PDO::FETCH_NUM)) {
      echo "RESULT: " . $row[0] . "\n";
  }
  $dbh = null;
  echo "OK\n";
?>

Without any other configuration, simply run the script which as expected, resulted in an error

# php -f snowflake.php 
PHP Fatal error:  Uncaught PDOException: SQLSTATE[08001] [240012] curl_easy_perform() failed: SSL peer certificate or SSH remote key was not OK in /home/admin/snowflake.php:6
Stack trace:
#0 /home/admin/snowflake.php(6): PDO->__construct()
#1 {main}
  thrown in /home/admin/snowflake.php on line 6

because the cacert.pem does not include the custom proxy CA.
6. manually edited cacert.pem and added the proxy's own CA, just appended to the end of cacert.pem
7. run the script again:

$ php -f snowflake.php  
Connected
RESULT: 8.23.5
OK

which confirmed the PHP driver does work with the correct configuration even with proxy, over the private link.
The error

2024-07-01 21:50:03.813 ERROR C     http_perform.c    399: curl_easy_perform() failed: SSL server certificate status verification FAILED

in the logs you sent, confirms that the PHP driver encounters a certificate which it does not trust (= is not included in the cacert.pem file, which cacert.pem is configured in the .ini file under pdo_snowflake.cacert

You could:

• double check what certificates you're getting from the proxy when connecting with openssl to the Snowflake account over privatelink, using the proxy. You'll need to add those custom CA certs from the proxy, to the cacert.pem.
  Make sure to add the certs together with their header and footer. So in my example, the mitmproxy cert was:

$ cat ~/.mitmproxy/mitmproxy-ca-cert.pem 
-----BEGIN CERTIFICATE-----
MIIDNTCCAh2gAwIBAgIUEdLvf+4lL6k1kir+V/ebEakOqt8wDQYJKoZIhvcNAQEL
..
M6XZgkqUu2sX
-----END CERTIFICATE-----

so i navigated to the very end of the driver's cacert.pem to the last certificate:

Atos TrustedRoot Root CA RSA TLS 2021
=====================================
-----BEGIN CERTIFICATE-----
MIIFZDCCA0ygAwIBAgIQU9XP5hmTC/srBRLYwiqipDANBgkqhkiG9w0BAQwFADBMMS4wLAYDVQQD
..
HYMfRsCbvUOZ58SWLs5fyQ==
-----END CERTIFICATE-----

and added my own so it looked like this after the modification:

Atos TrustedRoot Root CA RSA TLS 2021
=====================================
-----BEGIN CERTIFICATE-----
MIIFZDCCA0ygAwIBAgIQU9XP5hmTC/srBRLYwiqipDANBgkqhkiG9w0BAQwFADBMMS4wLAYDVQQD
..
HYMfRsCbvUOZ58SWLs5fyQ==
-----END CERTIFICATE-----

mitmproxy
=========
-----BEGIN CERTIFICATE-----
MIIDNTCCAh2gAwIBAgIUEdLvf+4lL6k1kir+V/ebEakOqt8wDQYJKoZIhvcNAQEL
..
M6XZgkqUu2sX
-----END CERTIFICATE-----

make sure header (BEGIN CERTIFICATE) and footer (END CERTIFICATE) is also included, unmodified, not just the cert.

• if still no joy; since the SnowSQL is working with the same proxy (that could be again due to multiple reasons...);
  a., check if SnowSQL's own certificate bundle is overridden or not. you can enable debug logs with adding -o log_file=/tmp/snowsql.log -o log_level=DEBUG ..rest of the usual arguments , connect to Snowflake, exit SnowSQL, visit log_file and look for REQUESTS_CA_BUNDLE entry.
  It shows where the cacert.pem is what the CLI is using. If it's something custom, you can try pointing PHP driver pdo_snowflake.cacert config to that cacert.pem which SnowSQL is using
  If it points to a directory which looks like a randomized one, locate the SnowSQL binary (looks like something ~/.snowsql/1.2.32/ and under that directory there should be a /certifi/cacert.pem file. Copy that somewhere and try using that one with the PHP driver
• if all these attempts fail, you perhaps will need to work with your proxy team and ask them to bypass endpoins returned form SYSTEM$ALLOWLIST_PRIVATELINK() Snowflake query and transparently proxy those connections without doing SSL inspection, if that's possible.

All in all; the driver looks to be working as it should.

[sfc-gh-dszmolka]

there seems to be no response on this for a while now, so marking it as closed. of course if further help is needed or if you think you found a bug, please comment and can reopen and look further