Upgrade from .NET Standard 2.0 to .NET Standard 2.1: Connection Open Task Fails

[BretMishler]

Issue description

I upgraded a working solution from projects that had .NET Core 2.2 and .NET Standard 2.0 to .NET Core 3.0 and .NET Standard 2.1, respectively.

In the project built with the .NET Standard framework, when the code reaches conn.Open(), it just hangs until finally returning

TaskCanceledException: A task was canceled.

and it gives no further information than that. What is odd is that the first time this method was invoked, it actually worked just fine. Then I refreshed the page and its been broken since. Until I sat for some time, refreshed the page again, and then it returned with the correct data -- and then randomly broke upon a refresh or another build of the solution.

Example code

private _connectionString = " stuff here ";

public List<Character> GetAll(int take, int skip, string orderBy, string sortOrder)
{   
    ...
    using (IDbConnection conn = new SnowflakeDbConnection())
    {

        conn.ConnectionString = _connectionString;

        if (conn != null && conn.State == ConnectionState.Closed)
            conn.Close();

        try {
            conn.Open();
        }
        catch(Exception ex)
        {
            throw ex;
        }
    ...
}

Error log

> TaskCanceledException: A task was canceled.

Configuration

Dotnet framework and version: .NET Standard 2.1 (worked with 2.0)

[BretMishler]

After running SYSTEM$WHITELIST() on Snowflake, copying the json result and saving it to file called whitelist.json, I ran the following command on my local machine:

snowcd .\whitelist.json
Performing 33 checks for 13 hosts
All checks passed

[stegus64]

What version of the snowflake connector are you using? What version of Visual Studio? What version of windows?

What snowflake hostname are you using?

[BretMishler]

@stegus64 What version of the snowflake connector are you using? Snowflake.Data 1.1.0

What version of Visual Studio? 16.3.8

What version of windows? Enterprise, 1803

What snowflake hostname are you using? Sorry, but that's private information.

[stegus64]

This sounds like some kind of network problem.

Snowflake.Data uses log4net for logging. The logfile contains lots of details to help trouble shooting the exact issue.

To enable logging in a .net core console application you can do this:

Create a file log4net.config, put the file in the output folder of the application.

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <configSections>
    <section name="log4net" type="log4net.Config.Log4NetConfigurationSectionHandler, log4net"/>
  </configSections>

  <log4net>
    <appender name="MyRollingFileAppender" type="log4net.Appender.RollingFileAppender">
      <file value="snowflake_dotnet.log" />
      <appendToFile value="true"/>
      <rollingStyle value="Size" />
      <maximumFileSize value="10MB" />
      <staticLogFileName value="true" />
      <maxSizeRollBackups value="10" />
      <layout type="log4net.Layout.PatternLayout">
        <!-- <header value="[DateTime]  [Thread]  [Level]  [ClassName] Message&#13;&#10;" /> -->
        <conversionPattern value="[%date] [%t] [%-5level] [%logger] %message%newline" />
      </layout>
    </appender>

    <root>
      <level value="ALL" />
      <appender-ref ref="MyRollingFileAppender" />
    </root>
  </log4net>
</configuration>

Test code:

   class Program
    {
        static void Main(string[] args)
        {
            // Enable logging
            log4net.GlobalContext.Properties["framework"] = "netcoreapp2.0";
            var logRepository = log4net.LogManager.GetRepository(Assembly.GetEntryAssembly());
            log4net.Config.XmlConfigurator.Configure(logRepository, new FileInfo("log4net.config"));

            using (IDbConnection conn = new SnowflakeDbConnection())
            {
                var connectionString = "ACCOUNT=XXX;DB=XXX;HOST=XXX.eu-west-1.snowflakecomputing.com;WAREHOUSE=XXX;ROLE=SYSADMIN;USER=XXX;PASSWORD=XXX";
                conn.ConnectionString = connectionString;
                conn.Open(); // Fails here

                IDbCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES LIMIT 10";
                IDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    Console.WriteLine(reader.GetString(0));
                }

                conn.Close();
            }
        }
    }

When you run this code it should create the file snowflake_dotnet.log in the output folder.

Please post the content of this file (after removing any sensitive information)

[e8-ZhixinYang]

Are there any updates? I encountered the same problem.

[DEBUG] [Snowflake.Data.Core.HttpUtil+RetryHandler] SF rest request timeout. [ERROR] [Snowflake.Data.Core.SFStatement] Query execution failed. System.AggregateException: One or more errors occurred. ---> System.Threading.Tasks.TaskCanceledException: A task was canceled. --- End of inner exception stack trace --- at System.Threading.Tasks.Task1.GetResultCore(Boolean waitCompletionNotification) at Snowflake.Data.Core.RestRequesterImpl.Post[T](IRestRequest request) in snowflake-connector-net-master\Snowflake.Data\Core\RestRequestImpl.cs:line 34`

[e8-ZhixinYang]

Steps to reproduce:

`conn.ConnectionString = connectionString; conn.Open();

IDbCommand cmd = conn.CreateCommand(); cmd.CommandText = "select * from t"; IDataReader reader = cmd.ExecuteReader(); System.Threading.Thread.Sleep(100000); IDataReader newreader = cmd.ExecuteReader(); conn.Close();`

[pcchannz]

I'm hitting the same error using docker, but only with newly created us-east-1 snowflake account, my older snowflake account working without any issue.

Both accounts work fine for .net standard 2.0, but not 2.1.

Workaround:

1. To run without error in both snowflake account with netstandard2.1, I have to use windows distribution, instead of unix.
2. Edit the httputil as specified in https://github.com/snowflakedb/snowflake-connector-net/issues/160, but unsure if that is recommended.

I think it should be related to OCSP. .NET connector should also implement fail-open/fail-close configuration similar to other connector.

initHttpClient() in HttpUtil.cs, HttpClientHandler initialization can change as bellow

var handler = new HttpClientHandler(); handler.ClientCertificateOptions = ClientCertificateOption.Manual; // if allow fail-open add the following code handler.ServerCertificateCustomValidationCallback = delegate { return true; }; var client = new HttpClient(handler);

[JorgeArellano]

We were getting a RemoteCertificateNameMismatch policy status error during certificate validation inside a Linux container, no issue in windows. After looking at this issue https://github.com/dotnet/runtime/issues/35880 we confirmed that it was the case for us, our connection string was like MyCompany_Dev.us-east-1.snoflakecomputing,com, snowflake supports dashes instead of underscores automatically, so, when we changed our connection string to MyCompany-Dev.us-east-1.snowflakecomputing.com we were able to connect with no issues in Linux, if that is the case there is no need to change the custom validation callback in the library.

[sfc-gh-jtang]

To clean up and re-prioritize more pressing bugs and feature requests we are closing all issues older than 6 months as of April 1, 2023. If there are any issues or feature requests that you would like us to address, please create them according to the new templates we have created. For urgent issues, opening a support case with this link Snowflake Community is the fastest way to get a response.