Sqlclient: Could not connect to MSSQL Server

[denis-ivanov]

Good day!
I have a problem with connection to MS SQL Server.
My MS SQL Server works in Docker, runned by following command:

docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=yourStrong(!)Password" -p 1433:1433 --name mssql-server -d mcr.microsoft.com/mssql/server:2019-latest

I have following minimal working example:

using System.Data.SqlClient;

namespace ConnectToSqlServer
{
    class Program
    {
        private const string ConnectionString = "Server=host.docker.internal;Database=master;User Id=sa;Password=yourStrong(!)Password;";

        static void Main(string[] args)
        {
            using (var connection = new SqlConnection(ConnectionString))
            {
                connection.Open();
                connection.Close();
            }
        }
    }
}

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>netcoreapp3.1</TargetFramework>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="System.Data.SqlClient" Version="4.8.0" />
  </ItemGroup>

</Project>

When I run this program I get (sorry for russian error messages)

System.Data.SqlClient.SqlException
  HResult=0x80131904
  Message=A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 - Время ожидания операции истекло.)
  Source=Core .Net SqlClient Data Provider
  StackTrace:
   at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, Boolean applyTransientFaultHandling, String accessToken)
   at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.Open()
   at ConnectToSqlServer.Program.Main(String[] args) in C:\Users\user\source\repos\ConnectToSqlServer\ConnectToSqlServer\Program.cs:line 13

Inner Exception 1:
Win32Exception: Время ожидания операции истекло.

But PowerShell works fine:

$sqlStr = 'Server=host.docker.internal;Database=master;User Id=sa;Password=yourStrong(!)Password;'
$sqlConn = New-Object System.Data.SqlClient.SqlConnection $sqlStr
$sqlConn.Open()
$sqlConn.Close()

I don't get any error message.

Dump of dotnet --info

PS C:\Users\user> dotnet --info
Пакет SDK для .NET Core (отражающий любой global.json):
 Version:   3.1.100
 Commit:    cd82f021f4

Среда выполнения:
 OS Name:     Windows
 OS Version:  10.0.18363
 OS Platform: Windows
 RID:         win10-x64
 Base Path:   C:\Program Files\dotnet\sdk\3.1.100\

Host (useful for support):
  Version: 3.1.0
  Commit:  65f04fb6db

.NET Core SDKs installed:
  3.0.100 [C:\Program Files\dotnet\sdk]
  3.1.100 [C:\Program Files\dotnet\sdk]

.NET Core runtimes installed:
  Microsoft.AspNetCore.All 2.1.14 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.All]
  Microsoft.AspNetCore.All 2.2.8 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.All]
  Microsoft.AspNetCore.App 2.1.14 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.App]
  Microsoft.AspNetCore.App 2.2.8 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.App]
  Microsoft.AspNetCore.App 3.0.0 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.App]
  Microsoft.AspNetCore.App 3.1.0 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.App]
  Microsoft.NETCore.App 2.1.14 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App]
  Microsoft.NETCore.App 2.2.8 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App]
  Microsoft.NETCore.App 3.0.0 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App]
  Microsoft.NETCore.App 3.1.0 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App]
  Microsoft.WindowsDesktop.App 3.0.0 [C:\Program Files\dotnet\shared\Microsoft.WindowsDesktop.App]
  Microsoft.WindowsDesktop.App 3.1.0 [C:\Program Files\dotnet\shared\Microsoft.WindowsDesktop.App]

To install additional .NET Core runtimes or SDKs:
  https://aka.ms/dotnet-download

[cheenamalhotra]

Hi @denis-ivanov

I'm able to reproduce the issue and can confirm that the use-case works with .NET Framework whereas does not work with .NET Core. Currently investigating the root cause.

[cheenamalhotra]

Workaround:
It seems to work on adding Connect Timeout=60 to the connection string.

[denis-ivanov]

@cheenamalhotra, increasing connection timeout works, but i don't think that it is a good solution.

[cheenamalhotra]

I know that's not a solution, I'm still looking into it.
Weirdly, when running the same test in "Debug" mode with the driver, the delay doesn't happen and it connects instantly! 🤔

[cheenamalhotra]

I found the problematic component, it's the "runtime.native.System.Data.SqlClient.sni" native library.

The native API call that takes at-least 20 seconds is this:
https://github.com/dotnet/SqlClient/blob/2ddf3a36b036b60c1c8431875cab277e10f35433/src/Microsoft.Data.SqlClient/netcore/src/Interop/SNINativeMethodWrapper.Windows.cs#L336

My "Debug" mode worked because I have Managed SNI enabled on Windows.

You would not face this issue on Unix platforms, as that uses the Managed SNI implementation. This native library is currently out of our hands and we're trying to get handle to that soon. Till then the workaround can be applied for Windows apps.

[jkshan]

@cheenamalhotra Any schedule to fix this issue, We faced this issue in our project where SQL server was running in VM. As of now, we are using the workaround purposed above.(Connect Timeout=60)

[denis-ivanov]

@cheenamalhotra, any updates in this issue?

[cheenamalhotra]

We are investigating solution currently, an alternate solution you can try in the meantime is by enabling Managed SNI on Windows with Microsoft.Data.SqlClient v2.0.0-preview2. Please find info here: release-notes2.0.0-preview2

[denis-ivanov]

@cheenamalhotra it's impossible to work with so large timeout and I can't use MS.Data.SqlClient.

[jkshan]

We are investigating solution currently, an alternate solution you can try in the meantime is by enabling Managed SNI on Windows with Microsoft.Data.SqlClient v2.0.0-preview2. Please find info here: release-notes2.0.0-preview2

Can guide on how to enable this? Does it need to be enabled in Application server or DB server?

[cheenamalhotra]

@jkshan

If you click on the link to release-notes above, it directs you to app context switch details.
You can enable it in your app startup or via app config.

[v-binpan]

@cheenamalhotra May I know if this preview version is fit to be used in Production environment? Thanks

[cheenamalhotra]

@v-binpan

No, we do not recommend using Preview releases in Production environments.

[v-binpan]

@cheenamalhotra Thanks for your comments. If the preview version is not recommended to use in Production Environment. May I know when the official version will be available? Is there a workaround that does not change the timeout setting and it can be applied in Prod Env?

In current environment, if timeout setting was changed it will have a performance side effect. Any suggestions will be appreciated.

[cheenamalhotra]

Hi @v-binpan

As I mentioned in https://github.com/dotnet/SqlClient/issues/367#issuecomment-571339395 above, the problem occurs due to runtime dll we import in .NET Core on Windows. The only workaround for Prod apps on this environment would be to increase timeout and it should be safe to do so.

With 2.0.0 released, and Managed SNI switch availability, I would still say that's an opt-in behavior and may not be "Production-ready" replacement for Native SNI to go for, as there could be issues with multi-threading in Managed SNI you can also find reported in this repository.

We are investigating a better solution for the Native SNI layer that would be a production ready fix and would fix this problem, will post update here as we make considerable progress on that front.

[jkshan]

@cheenamalhotra Do you know if there is any specific server configure or SQL server load which trigger this issue.
The reason is I have two App server(VM's in AWS), one is having this issue and other don't have this issue.
Configuration wise both app servers are same, the issue happens in App server which is trying to connect to a SQL server which serves heavy traffic.
And the same Issue is not happening in other App server which is trying to connect to a SQL server(having less load)

[cheenamalhotra]

@jkshan

I'm not aware of any server side limitation, could you share more details about SQL Server versions in use in both places? Are these Amazon RDS for SQL Server instances?

And is the issue you're facing on heavy load server also reproducible with Managed SNI?

[jkshan]

@jkshan

I'm not aware of any server side limitation, could you share more details about SQL Server versions in use in both places? Are these Amazon RDS for SQL Server instances?

And is the issue you're facing on heavy load server also reproducible with Managed SNI?

Apologies for the late response. Envi., details below, its SQL server instances running on AWS-EC2.

Also, my statement on load could be wrong since there is no issue in connecting to the DB servers from the same app server if I use .Net standard framework App.

SQLServer: 
   Windows Server 2012 R2 STD
   Microsoft SQL Server 2014 (SP2-CU18) (KB4500180) - 12.0.5687.1 (X64)

App Server: 
Windows Server 2012 R2 STD
   Tried with both 2.1.11 and 2.1.17 Dotnet core runtime

[canertosuner]

Having the same issue for v3.1 app working on docker. Any fix about it ?

[cheenamalhotra]

@canertosuner could you open a new issue with specific details and a repro app if possible?

[cheenamalhotra]

@jkshan

Have you tried working with Microsoft.Data.SqlClient v2.0.0 and seen this issue again?

[denis-ivanov]

The problem is solved. I close this issue.

[jkshan]

@jkshan

Have you tried working with Microsoft.Data.SqlClient v2.0.0 and seen this issue again?

@cheenamalhotra I'm moved out the project which was having this issue, I won't be able to check this anymore. Sorry.