v5.2.0 Connection open issue

[deaklasECB]

Describe the bug

When upgrading from 5.1.5 to 5.2.0 and connecting to Azure Sql, I get an error when connecting to the database.

Exception message:
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 26 - Error Locating Server/Instance Specified) Success 

Stack trace:
Microsoft.Data.SqlClient.SqlException:
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning (Microsoft.Data.SqlClient, Version=5.0.0.0, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5)
   at Microsoft.Data.SqlClient.TdsParser.Connect (Microsoft.Data.SqlClient, Version=5.0.0.0, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin (Microsoft.Data.SqlClient, Version=5.0.0.0, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover (Microsoft.Data.SqlClient, Version=5.0.0.0, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist (Microsoft.Data.SqlClient, Version=5.0.0.0, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds..ctor (Microsoft.Data.SqlClient, Version=5.0.0.0, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5)
   at Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnection (Microsoft.Data.SqlClient, Version=5.0.0.0, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5)
   at Microsoft.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection (Microsoft.Data.SqlClient, Version=5.0.0.0, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5)
   at Microsoft.Data.ProviderBase.DbConnectionPool.CreateObject (Microsoft.Data.SqlClient, Version=5.0.0.0, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5)
   at Microsoft.Data.ProviderBase.DbConnectionPool.UserCreateRequest (Microsoft.Data.SqlClient, Version=5.0.0.0, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5)
   at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection (Microsoft.Data.SqlClient, Version=5.0.0.0, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5)
   at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection (Microsoft.Data.SqlClient, Version=5.0.0.0, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5)
   at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection (Microsoft.Data.SqlClient, Version=5.0.0.0, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5)
   at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal (Microsoft.Data.SqlClient, Version=5.0.0.0, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5)
   at Microsoft.Data.ProviderBase.DbConnectionClosed.TryOpenConnection (Microsoft.Data.SqlClient, Version=5.0.0.0, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5)
   at Microsoft.Data.SqlClient.SqlConnection.TryOpen (Microsoft.Data.SqlClient, Version=5.0.0.0, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5)
   at Microsoft.Data.SqlClient.SqlConnection.Open (Microsoft.Data.SqlClient, Version=5.0.0.0, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5)
   at Microsoft.Data.SqlClient.SqlConnection.Open (Microsoft.Data.SqlClient, Version=5.0.0.0, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerConnection.OpenDbConnection (Microsoft.EntityFrameworkCore.SqlServer, Version=8.0.3.0, Culture=neutral, PublicKeyToken=adb9793829ddae60)

Expected behavior

Connection should be established.

I suspect this might be related to the redirect/proxy connection policy in Azure SQL, because I have 2 instances, one with such a policy and one without, and in one case version 5.2.0 works but in the other it does not.

Is there anything to be changed in the connection string or an env. var to enable the connection?

Further technical details

Microsoft.Data.SqlClient version: 5.2.0
.NET target: (.NET 8)
SQL Server version: Azure SQL
Operating system: Linux containers

[Wraith2]

It's possible that this is related to an unintentional change reported here #2415 Could you try flipping the appcontext switch mentioned in that issue and see if it helps?

[deaklasECB]

I have set the following at application startup:

AppContext.SetSwitch("Switch.Microsoft.Data.SqlClient.UseOneSecFloorInTimeoutCalculationDuringLogin", true);

but I still get the same error as before.

EDIT: My initial assumption seems to be wrong: it does not matter if AzureSQL uses proxy or redirect mode (at least not from a Windows).

Are there inherent dependencies on Linux that could be now required?

[ladeak]

Does the new release use a port or protocol during connection that the previous did not use?

What is the best way to 'debug' this, should I create an event listener and subscribe to traces emitted?

[deaklasECB]

After further testing:

1. Using Azure SQL Redirect mode from Linux does not work.
2. Using Azure SQL Proxy mode from Linux works.
3. Using Azure SQL from Windows in Redirect mode or Proxy mode works.

The issue is with the 1st case.

By Linux, I tested from WSL (Ubuntu 22.04) and from Linux containers (asp.net core 8.0 jammy chiseled image), and both throw the above error.

[JRahnama]

Can you try adding a port number at the end of connection string? like: tcp:, port number?

[deaklasECB]

It is in the connection string (it has been there for all my tests):
Server=tcp:sql-....database.windows.net,1433;Initial Catalo...j;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

EDIT: I tried w/o the port number in the connectionstring and both returns the same error.

I also have this in the logs, not sure if it helps:
Routing Destination:sql-[removed].database.windows.net\ac2e02945b9f,15788

and from WSL the stacktrace looks slightly different, but the error is the same:

fail: Microsoft.AspNetCore.Diagnostics.ExceptionHandlerMiddleware[1]
      An unhandled exception has occurred while executing the request.
      Microsoft.Data.SqlClient.SqlException (0x80131904): A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 26 - Error Locating Server/Instance Specified)
       ---> System.Net.Sockets.SocketException (0x80004005): Success
         at Microsoft.Data.SqlClient.SNI.SSRP.GetPortByInstanceName(String browserHostName, String instanceName, TimeoutTimer timeout, Boolean allIPsInParallel, SqlConnectionIPAddressPreference ipPreference)
         at Microsoft.Data.SqlClient.SNI.SNIProxy.CreateTcpHandle(DataSource details, TimeoutTimer timeout, Boolean parallel, SqlConnectionIPAddressPreference ipPreference, String cachedFQDN, SQLDNSInfo& pendingDNSInfo, Boolean tlsFirst, String hostNameInCertificate, String serverCertificateFilename)
         at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
         at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, SqlCommand command, Boolean callerHasConnectionLock, Boolean asyncClose)
         at Microsoft.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, TimeoutTimer timeout, SqlConnectionString connectionOptions, Boolean withFailover)
         at Microsoft.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, TimeoutTimer timeout, Boolean withFailover)
         at Microsoft.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout)
         at Microsoft.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance)
         at Microsoft.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, DbConnectionPool pool, Func`3 accessTokenCallback)
         at Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
         at Microsoft.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
         at Microsoft.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
         at Microsoft.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
         at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
         at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
         at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
         at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
         at Microsoft.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
         at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry, SqlConnectionOverrides overrides)
         at Microsoft.Data.SqlClient.SqlConnection.Open(SqlConnectionOverrides overrides)
         at Microsoft.Data.SqlClient.SqlConnection.Open()
         at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerConnection.OpenDbConnection(Boolean errorsExpected)

EDIT: any chance this is related? 94c089e#diff-a9b4126995728b5ff4d99d7c10d8e6af5259ee81a790274529b028e7213b2707
or this: https://github.com/dotnet/SqlClient/pull/2129/files#diff-a9b4126995728b5ff4d99d7c10d8e6af5259ee81a790274529b028e7213b2707

[JRahnama]

Is this Kerberos authentication?

Is it possible that you can test with below nuget? (Keep in mind that this nuget is just for testing purposes and it is not an official release and not suitable for production) Just change the extension to .nupkg and it should be good to go (version would be 6.0.0-pull.106802.1)
Microsoft.Data.SqlClient.6.0.0-pull.106802 (1).zip

[deaklasECB]

It is using username and password authentication (removed that section from the connectionstring in the above samples). Let me test the package (I might only come back with results on Monday because I am catching a flight very soon).

[deaklasECB]

In my initial test from my dev vm, it seems to have fixed the issue. The version number for me was shown as 6.0.0-pull.106802.
I will do a test on Monday from the container image as well if you think it worth it.
Thank you very much for the quick turnaround.

[JRahnama]

In my initial test from my dev vm, it seems to have fixed the issue. The version number for me was shown as 6.0.0-pull.106802. I will do a test on Monday from the container image as well if you think it worth it. Thank you very much for the quick turnaround.

It will help to confirm that the regression is addressed in this build. Thank you.

[deaklasECB]

I have tested from the docker container as well, and I can confirm the fix works as expected. Thank you very much for the quick turnaround.

May I ask what was the issue? (just for my curiosity).

My last question: do you already have any plans for the next release? When would you expect the fix to go live? - Asking only, so that I can plan along.

[JRahnama]

@deaklasECB Issue #2378 was the initial report highlighting a regression introduced in v5.2.0. This issue will be resolved in the upcoming hotfix release, v5.2.1, with the release date yet to be determined.

[DavoudEshtehari]

Closing as duplicate of #2378.