"SSL connection could not be established" error

[mattthr]

Hi,

I believe I had the same issue as this poster: #2

When I try to connect to Snowflake, I see the error:

System.Net.Http.HttpRequestException: The SSL connection could not be established, see inner exception.
---> System.Security.Authentication.AuthenticationException: The remote certificate is invalid according to the validation procedure.

I tried the fix suggested in that thread which they never came back to you about:
ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12;
ServicePointManager.UseNagleAlgorithm = false;
ServicePointManager.CheckCertificateRevocationList = true;

But I'm still getting the error as soon as I try to issue a query to Snowflake. Is there something else I can try, or some addition debugging info you'd like me to provide?

[fixer-m]

@mattthr, Hi!

What region in Snowflake do you use?
Do you use any proxy in your connection?
How your URL looks like when you are logged-in into Snowflake web interface? Does it ends with *.snowflakecomputing.com ?

Can you please try to run some queries with official .NET connector?
Will it produce the same connection exception? Some users have this issue with official connector: snowflakedb/snowflake-connector-net#6 , but I don't see clear answer how to resolve it.

[mattthr]

Thanks for coming back to me.

I can connect normally and run successful queries with the standard Snowflake .Net connector.

Our snowflake instance ends with west-europe.azure.snowflakecomputing.com

The connection I'm using (with region) is:
var snowflakeClient = new SnowflakeClient("MY_USER", "myPa$$word", "my_account", "west-europe");

Leaving off the "west-europe" region and submitting just the three parameters yields a 403 Foribidden response from the Snowflake server.

We do use a proxy server as we're all working from home at the moment, but I've verified I get the same error both with and without the proxy turned on.

[rafael-queiroz-cko]

I have the same issue.

[fixer-m]

@rafael-queiroz-cko, Hi! Thanks for reporting!

@mattthr, @rafael-queiroz-cko, After some research I think the root cause of this issue is in SSL settings applied to HTTP connection. These settings/options are:

SecurityProtocol = SecurityProtocolType.Tls12;
UseNagleAlgorithm = false;
CheckCertificateRevocationList = true;

Unfortunately I can't reproduce this issue: for me it does work regardless of these settings. However I think they might be required for some regions (or cloud provider) in SF, but I don't have account in every region to check this. What's bad - these options have different default and allowed values in different Windows versions and .NET versions.

Anyway I have prepared special version of Snowflake.Client which have option to pass your own HttpClient instance, so you can configure its behavior. I've created separate branch for it: ssl-issue. So if you can test it and help me with debugging this - that would be nice.

Here is the basic idea how you can test it:

var handler = new HttpClientHandler
{
    SslProtocols = SslProtocols.Tls12,
    CheckCertificateRevocationList = true
};
var httpClient = new HttpClient(handler);

var snowflakeClient = new SnowflakeClient("user", "password", "account", "region");
snowflakeClient.SetHttpClient(httpClient);

var sessionInitialized = await snowflakeClient.InitNewSessionAsync(); // <= does it work?

What info also could help:

1. Your .NET and Windows versions
2. Your SF region and cloud provider
3. Does official library (Snowflake.Data) works for you?
4. Have you tried solution with ServicePointManager (see first post here)? Did it work?

[rafael-queiroz-cko]

1- Lambda function in AWS, it works fine in my end. But I'm connected to a VPN in my home, perhaps it is a mismatch of configurations in AWS
2- AWS, eu-west-1
3- the official library works locally but I can't publish it to AWS because it has issues with log4net not being able to run in linux
4 - didn't work

[rafael-queiroz-cko]

@fixer-m there is a problem with certificates. Using this might solve the problem:

handler.ServerCertificateCustomValidationCallback = (message, cert, chain, errors) => true;

After I did this my lambda stopped complaining about certificates and started complaining about reaching the host and trying to authenticate (403) which is expected, perhaps the official SDK (that crap one) does this underneath the carpet xD

This will skip certificate validation. Can you publish a nuget version with this new branch that you created? With the ability to send custom HTTP Handlers / Clients. I can check in my environment to see if it is working as expected but I cannot publish the whole code there as it is a AWS Lambda. But it might be good to understand what the underlying is. Let me know if you are willing to do this.

Thing is, why does it work in my local environment without the hack, but doesn't work in AWS Lambda?

[rafael-queiroz-cko]

Also, check this forked code: https://github.com/rdagumampan/snowflake-connector-net

This is forked from the original, it doesn't give me problems with certs but gave me other problems because it was forcing to log some weird stuff in a File. Which my lambda complained .

[fixer-m]

@rafael-queiroz-cko, thanks! I have just published new version of package (0.3.3) with SetHttpClient() method. Can you please try to use it in AWS environment?

Yes, trick with a ServerCertificateCustomValidationCallback should work: it just disables cert validation. However I can't use it as a solution in my library for obvious reasons, so I really want to figure out proper solution.

In official library they use HttpClient, but before its creation they have these lines:

ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12;
ServicePointManager.UseNagleAlgorithm = false;
ServicePointManager.CheckCertificateRevocationList = true;

However - as I recently discovered - these lines have effect only in .NET Framework. To set these parameters in .NET Core you have to use HttpClientHandler with according options, as I suggested earlier:

var handler = new HttpClientHandler
{
    SslProtocols = SslProtocols.Tls12,
    CheckCertificateRevocationList = true
};
var httpClient = new HttpClient(handler);

Another issue with ServicePointManager is that it sets options globally, affecting all HttpClients that you have in your application, which is really bad for library code. That's why initially I haven't reproduced this code in my library.

[rafael-queiroz-cko]

Ok thanks! I got something really specific and perhaps it should be enough to make it work with the right certs. The scenarios are as following:

In my HOME PC:
1 - VPN disconnected + HTTPClientHandler (with attributes) = SSL error
2 - VPN connected + HTTPCLientHandler = 🎉

AWS Lambda:
1 - No VPN conn + HTTPClientHandler = SSL error (Same as home pc)
2 - I can't run scenario 2 without making a PR to my code base (I will do now and ask someone to approve it tmr)

I will let you know

[mattthr]

Thanks for the new branch. I've given it a go and I'm still getting the same error. The code I'm using with the branch is:

var handler = new HttpClientHandler
{
	SslProtocols = SslProtocols.Tls12,
	CheckCertificateRevocationList = true
};
var httpClient = new HttpClient(handler);

var snowflakeClient = new SnowflakeClient("MY_USER", "pa$$word", "myAccount", "west-europe");
snowflakeClient.SetHttpClient(httpClient);

var sessionInitialized = await snowflakeClient.InitNewSessionAsync();

To answer your other questions:

Your .NET and Windows versions - data access is .net standard 2.0, web API layer is .net core 3.1 , windows is 10.0.18363
Your SF region and cloud provider - west-europe and Azure
Does official library (Snowflake.Data) works for you? - Yes
Have you tried solution with ServicePointManager (see first post here)? Did it work? - Yes, and no

[fixer-m]

@mattthr,
Ok, what if you add "empty" ServerCertificateCustomValidationCallback to handler?
Like this:

var handler = new HttpClientHandler
{
    SslProtocols = SslProtocols.Tls12,
    CheckCertificateRevocationList = true, 
    ServerCertificateCustomValidationCallback = (message, cert, chain, errors) => true // i.e. bypass cert validation
};

var httpClient = new HttpClient(handler);
var snowflakeClient = new SnowflakeClient("MY_USER", "pa$$word", "myAccount", "west-europe");
snowflakeClient.SetHttpClient(httpClient);

var sessionInitialized = await snowflakeClient.InitNewSessionAsync();

This will bypass SSL cert validation, so I would not recommend to use it in production, but this should work. If it actually does, then my next step would be to set another delegate in ServerCertificateCustomValidationCallback to log all errors - it may contain much more detailed error messages.

One more question: does your account name contains underscore ("_")? If so, try to replace it with dash ("-") when creating SnowflakeClient. It can be a cause of the issue: snowflakedb/snowflake-connector-net#160 (comment)

[fixer-m]

@mattthr, @rafael-queiroz-cko,
One more finding. It appears that SF hostname can include cloud provider name, like this:

account-name.us-east-1.aws.snowflakecomputing.com

In SnowflakeClient host name - if not passed explicitly - is built like this: $"{Account}.{Region}.snowflakecomputing.com"
I.e. it doesn't include cloud provider name. And incorrect hostname will lead to SSL cert error.
To overcome this you can specify your region with your cloud provider like "us-east.aws".

Or you can explicitly specify your full hostname like this:

var authInfo = new AuthInfo("user", "pass", "account", "region");
var urlInfo = new UrlInfo() { Host = "your SF full hostname"};
var client = new SnowflakeClient(authInfo, null, urlInfo);

I will think how to fix this.

[rafael-queiroz-cko]

@fixer-m this is how I'm doing, I have the connection string in my secrets manager (AWS)

var builder = new DbConnectionStringBuilder
{
    ConnectionString = secrets.ConnectionString
};

var handler = new HttpClientHandler
{
    SslProtocols = SslProtocols.Tls12,
    CheckCertificateRevocationList = true,
    ServerCertificateCustomValidationCallback = (message, cert, chain, errors) => true
};

var httpClient = new HttpClient(handler);

var authInfo = new AuthInfo
{
    Account = builder["account"].ToString(),
    User = builder["user"].ToString(),
    Password = builder["password"].ToString(),
    Region = builder["region"].ToString()
};

var snowflakeClientSettings = new SnowflakeClientSettings(authInfo)
{
    UrlInfo =
    {
        Host = $"{builder["host"]}"
    }
};

var snowflakeClient = new SnowflakeClient(snowflakeClientSettings);
snowflakeClient.SetHttpClient(httpClient);

var result = await snowflakeClient.QueryRawResponseAsync(
    MissingEventsQuery.Query, new { ReportDate = positionDate });

[fixer-m]

@rafael-queiroz-cko, so does it work?

[rafael-queiroz-cko]

@rafael-queiroz-cko, so does it work?

it does.. but I'm using ServerCertificateCustomValidationCallback = (message, cert, chain, errors) => true