Getting ExtendedSocketException in "th-TH" culture SqlConnection.Open method

[ghost]

Describe the bug

When the CultureInfo is set to "th-TH", and the SQL Server port (e.g. 1433) is specified in the connection string, the SqlConnection.Open method will fail with ExtendedSocketException. This only happens in .Net Core 3.1 LTS version in Linux.

Exception: 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: 35 - An internal exception was caught)
---> System.Net.Internals.SocketExceptionFactory+ExtendedSocketException (00000005, 0xFFFDFFFF): Name or service not known
at System.Net.Dns.InternalGetHostByName(String hostName)
at System.Net.Dns.GetHostAddresses(String hostNameOrAddress)
at Microsoft.Data.SqlClient.SNI.SNITCPHandle.Connect(String serverName, Int32 port, TimeSpan timeout, Boolean isInfiniteTimeout, SqlConnectionIPAddressPreference ipPreference, String cachedFQDN, SQLDNSInfo& pendingDNSInfo)
at Microsoft.Data.SqlClient.SNI.SNITCPHandle..ctor(String serverName, Int32 port, Int64 timerExpire, Boolean parallel, SqlConnectionIPAddressPreference ipPreference, String cachedFQDN, SQLDNSInfo& pendingDNSInfo)
at Microsoft.Data.ProviderBase.DbConnectionPool.CheckPoolBlockingPeriod(Exception e)
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, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) at Microsoft.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions)
at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource1 retry, SqlConnectionOverrides overrides) at Microsoft.Data.SqlClient.SqlConnection.Open(SqlConnectionOverrides overrides) at Microsoft.Data.SqlClient.SqlConnection.Open() at sqltest.Program.Main(String[] args) in /home/mztse/sqltest/Program.cs:line 57 ClientConnectionId:00000000-0000-0000-0000-000000000000 at Microsoft.Data.ProviderBase.DbConnectionPool.CheckPoolBlockingPeriod(Exception e) 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, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions)
at Microsoft.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource1 retry, SqlConnectionOverrides overrides)
at Microsoft.Data.SqlClient.SqlConnection.Open(SqlConnectionOverrides overrides)
at Microsoft.Data.SqlClient.SqlConnection.Open()
at sqltest.Program.Main(String[] args) in /home/mztse/sqltest/Program.cs:line 57

Below is the part of the verbose log showing the issue:

<prov.DbConnectionHelper.ConnectionString_Set|API> 1, 'Server = tcp:YOURSQLSERVER.database.windows.net,1433; Initial Catalog = YOURDB; Persist Security Info = False; User ID = USERNAME; MultipleActiveResultSets = False; Encrypt = True; TrustServerCertificate = False; Connection Timeout = 30;'
SqlConnection.Open | API | Correlation | Object Id 1, Activity Id 9528a595-4d56-409c-ab00-66fc5f708419:1
SqlConnection.Open | API | Correlation | Object Id 1, Activity Id 9528a595-4d56-409c-ab00-66fc5f708419:1
<sc.SqlConfigurableRetryLogicManager.ConnectionProvider|INFO> Requested the ConnectionProvider value.
<sc.AppConfigManager.FetchConfigurationSection|INFO>: Unable to load custom SqlConfigurableRetryLogicConnection. Default value of T type returns.
<sc.AppConfigManager.FetchConfigurationSection|INFO>: Unable to load custom SqlConfigurableRetryLogicCommand. Default value of T type returns.
<prov.DbConnectionPool.TransactedConnectionPool.TransactedConnectionPool|RES|CPOOL> 1, Constructed for connection pool 1
<prov.DbConnectionPool.DbConnectionPool|RES|CPOOL> 1, Constructed.
<prov.DbConnectionPool.Startup|RES|INFO|CPOOL> 1, CleanupWait=190000
<prov.DbConnectionPool.GetConnection|RES|CPOOL> 1, Getting connection.
<prov.DbConnectionPool.GetConnection|RES|CPOOL> 1, Creating new connection.
<prov.DbConnectionPool.ReclaimEmancipatedObjects|RES|CPOOL> 1
<sc|SqlAuthenticationProviderManager|Ctor|Info>Neither SqlClientAuthenticationProviders nor SqlAuthenticationProviders configuration section found.
<sc.SqlInternalConnectionTds.LoginNoFailover|ADV> 4, host=tcp:YOURSQLSERVER.database.windows.net,1433
TdsParserStateObject.IncrementPendingCallbacks | ADV | State Object Id 1, after incrementing _pendingCallbacks: 1
<sc.SqlInternalConnectionTds.AttemptOneLogin|ADV> 4, timout=29996[msec], server=tcp:YOURSQLSERVER.database.windows.net,1433
TdsParser.Connect | SEC | Connection Object Id 4, Authentication Mode: SqlPassword
<sc.AppConfigManager.FetchConfigurationSection|INFO>: Unable to load custom AppContextSwitchOverrides. Default value of T type returns.
<sc.SqlAppContextSwitchManager.ApplyContextSwitches|INFO> Entry point.
<sc.SqlAppContextSwitchManager.ApplyContextSwitches|INFO> Exit point.
SNITCPHandle
SNITCPHandle..ctor | INFO | Connection Id ebc37884-3839-4010-8844-c7d891e07338, Setting server name = YOURSQLSERVER.database.windows.net,1433
SNITCPHandle..ctor | INFO | Connection Id ebc37884-3839-4010-8844-c7d891e07338, Connecting to serverName YOURSQLSERVER.database.windows.net,1433 and port 1433
SNITCPHandle.IPv4First | INFO | IP preference : {0}
SNITCPHandle..ctor | ERR | Connection Id ebc37884-3839-4010-8844-c7d891e07338, Cached DNS Info not found, exception occurred thrown: Name or service not known
SNITCPHandle..ctor | ERR | Connection Id ebc37884-3839-4010-8844-c7d891e07338 Socket exception occurred: Error Code HostNotFound, Message Name or service not known
SNICommon.ReportSNIError | ERR | Provider = TCP_PROV, SNI Error = 35, Exception = Name or service not known
Exit SNI Scope 1
SNIProxy.CreateConnectionHandle | Info | Session Id ebc37884-3839-4010-8844-c7d891e07338, SNI Handle Type: Microsoft.Data.SqlClient.SNI.SNITCPHandle
TdsParserStateObjectManaged.CreatePhysicalSNIHandle | Info | State Object Id 1, Session Id ebc37884-3839-4010-8844-c7d891e07338, ServerName tcp:YOURSQLSERVER.database.windows.net,1433, Async = True
<sc.TdsParser.ProcessSNIError|ERR>.ProcessSNIError | INFO | SCOPE | Entering Scope 2
< sc.TdsParser.ProcessSNIError |ERR|ADV > Error message Detail:
<sc.TdsParser.ProcessSNIError |ERR|ADV > Empty error message received from SNI. Error Message = , SNI Error Number =35
<sc.TdsParser.ProcessSNIError |ERR|ADV > SNI Native Error Code = 0
<sc.TdsParser.ProcessSNIError |ERR|ADV > SNI Error Message. Native Error = 0, Line Number =0, Function =, Exception =System.Net.Internals.SocketExceptionFactory+ExtendedSocketException (00000005, 0xFFFDFFFF): Name or service not known
at System.Net.Dns.InternalGetHostByName(String hostName)
at System.Net.Dns.GetHostAddresses(String hostNameOrAddress)
at Microsoft.Data.SqlClient.SNI.SNITCPHandle.Connect(String serverName, Int32 port, TimeSpan timeout, Boolean isInfiniteTimeout, SqlConnectionIPAddressPreference ipPreference, String cachedFQDN, SQLDNSInfo& pendingDNSInfo)
at Microsoft.Data.SqlClient.SNI.SNITCPHandle..ctor(String serverName, Int32 port, Int64 timerExpire, Boolean parallel, SqlConnectionIPAddressPreference ipPreference, String cachedFQDN, SQLDNSInfo& pendingDNSInfo), Server =
SqlError.ctor | ERR | Info Number 0, Error State 0, Error Class 20, Error 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: 35 - An internal exception was caught)', Procedure '', Line Number 0
Exit Scope 2
TdsParserStateObjectManaged.Dispose | Info | State Object Id 1, Session Id ebc37884-3839-4010-8844-c7d891e07338, Disposing session Handle and counters.
TdsParserStateObjectManaged.Dispose | Info | State Object Id 1, Session Id null, sessionHandle is available, disposing session.
SNITCPHandle.Dispose | INFO | Connection Id ebc37884-3839-4010-8844-c7d891e07338, All streams disposed.
TdsParserStateObject.DecrementPendingCallbacks | ADV | State Object Id 1, after decrementing _pendingCallbacks: 0
<sc.TdsParser.Connect|ERR|SEC> Login failure
<sc|ActiveDirectoryAuthenticationTimeoutRetryHelper|GetCachedToken|Info>Retrieved cached token null.
<sc|ActiveDirectoryAuthenticationTimeoutRetryHelper|CheckCanRetry|Error>Cannot retry when cached token is null.

In above log we can see if using th-TH, the port ",1433" is still being passed to the "serverName" parameter of the "SNITCPHandle..ctor" function, causing DNS query fail:

SNITCPHandle..ctor | INFO | Connection Id ebc37884-3839-4010-8844-c7d891e07338, Setting server name = YOURSQLSERVER.database.windows.net,1433
SNITCPHandle..ctor | INFO | Connection Id ebc37884-3839-4010-8844-c7d891e07338, Connecting to serverName YOURSQLSERVER.database.windows.net,1433 and port 1433

A good one should look like below if we use zh-CN or don't specify culture:

SNITCPHandle..ctor | INFO | Connection Id 49871fba-6fc2-45b7-8ac7-313e3bf4d846, Setting server name = YOURSQLSERVER.database.windows.net
SNITCPHandle..ctor | INFO | Connection Id 49871fba-6fc2-45b7-8ac7-313e3bf4d846, Connecting to serverName YOURSQLSERVER.database.windows.net and port 1433

To reproduce

Below is the sample console app code which can stably reproduce the issue:

sqltest.csproj:

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

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

  <ItemGroup>
    <PackageReference Include="Microsoft.Data.SqlClient" Version="4.1.0" />
  </ItemGroup>

</Project>

Program.cs:

using System;
using System.Data;
using System.Globalization;
using System.Threading;
using Microsoft.Data.SqlClient;
using System.Diagnostics.Tracing;

namespace sqltest
{
    public class SqlClientListener : EventListener
    {
        protected override void OnEventSourceCreated(EventSource eventSource)
        {
            if (eventSource.Name.Equals("Microsoft.Data.SqlClient.EventSource"))
            {
                EnableEvents(eventSource, EventLevel.Verbose, (EventKeywords)8191);
            }
        }

        protected override void OnEventWritten(EventWrittenEventArgs eventData)
        {
            Console.WriteLine(eventData.Payload[0]);
        }
    }

    class Program
    {

        private static void Main(string[] args)
        {
            Thread.CurrentThread.CurrentCulture = new CultureInfo("th-TH");
            Thread.CurrentThread.CurrentUICulture = new CultureInfo("th-TH");

            string commandText = "select @@VERSION";
            string conString = "Server = tcp:YOURSQLSERVER.database.windows.net,1433; Initial Catalog = YOURDB; Persist Security Info = False; User ID = USERNAME; Password = PWD; MultipleActiveResultSets = False; Encrypt = True; TrustServerCertificate = False; Connection Timeout = 30;";
            Console.WriteLine("ConnectionString: " + conString);

            for (int i = 0; i < 1; i++)
            {
                Console.WriteLine("Try: " + i);
                try
                {
                    using (SqlClientListener listener = new SqlClientListener())
                    {
                        using (SqlConnection con = new SqlConnection(conString))
                        {
                            con.Open();
                            using (SqlCommand cmd = con.CreateCommand())
                            {
                                cmd.CommandType = CommandType.Text;
                                cmd.CommandText = commandText;
                                var result = cmd.ExecuteScalar();
                                Console.WriteLine("Done! Result of server version: ");
                                Console.WriteLine(result);
                            }
                        }
                    }

                }
                catch (Exception ex)
                {
                    Console.WriteLine("Exception: " + ex.ToString());
                    Console.WriteLine(ex.StackTrace);
                }
            }

            Console.WriteLine("Done. Press Enter to Quit");
            Console.ReadLine();
        }
    }
}

Expected behavior

It should not contain the port in the "serverName" parameter of the function "SNITCPHandle..ctor", and the DNS should be successful.

Further technical details

Microsoft.Data.SqlClient version: Latest 4.1.0
.NET target: Core 3.1 LTS. (I have tested Core 5.0 and 6.0 and they don't have this problem.)
SQL Server version: Azure Sql Db
Operating system: I tested and can stably repro the issue in my Ubuntu 20.04 LTS in WSL.

Additional context
This may be related to #154

[JRahnama]

@mszixie we will look into this and will get back to you soon.

[JRahnama]

I was able to repro the issue on my side. I tested with WSL and got the same issue with only one difference with or without port I had the issue when th-TH is selected. We will dig deeper and will let you know.