Closed Erik-2019 closed 2 months ago
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.
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
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
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)
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=
Proxy :
snowflake:account=
This returns 3 certificates, 2 were not in the cacert.pem file:
echo | openssl s_client -proxy
This returns an error :
echo | openssl s_client -connect
This returns a forbidden error, guessing the proxy blocks it
echo | openssl s_client -proxy
This returns 2 certificates, 1 is the same as the 2 returned from 1 above
echo | openssl s_client -connect
I am able to connect and run queries through SnowSQL with the following, so I know the proxy is working.
snowsql -a
i started having doubts so to double-check our PHP driver still works with proxy in v3.0.0, i did the following:
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.
cacert.pem
and added the proxy's own CA, just appended to the end of cacert.pem
$ 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:
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.
-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 driverSYSTEM$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.
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
Please answer these questions before submitting your issue. In order to accurately debug the issue this information is required. Thanks!
What version of PDO/PHP driver are you using? Snowflake PHP PDO Driver: 8.2.17-3.0.0
What operating system and processor architecture are you using? Debian GNU/Linux 11 (bullseye)
What version of C/C++ compiler are you using? gcc 4:10.2.1-1 cmake 3.18.4
What did you do? Tried rebuilding PDO_Snowflake Changed Proxy settings Tried from PHP as well as shell using Curl
What did you expect to see? Results from my query.
Getting no results, PDO_Snowflake gives me the error below.
Can you set logging to DEBUG and collect the logs? See snowflake_20240628162939.txt attachment. snowflake_20240628162939.txt
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