Active Directory Interactive broken on macOS

[esbenbjerre]

Describe the bug

When using Active Directory Interactive on macOS the browser no longer opens for authentication. It appears to reach a timeout and throws the below exception.

Exception message:
Unhandled exception. System.AggregateException: One or more errors occurred. (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))
Stack trace:
 ---> 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): Undefined error: 0
   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.WaitForPendingOpen()
--- End of stack trace from previous location ---
   at Dapper.SqlMapper.QueryAsync[T](IDbConnection cnn, Type effectiveType, CommandDefinition command) in /_/Dapper/SqlMapper.Async.cs:line 433
ClientConnectionId:8f69aab9-34c2-4c54-9f73-253bcba6a36b
Routing Destination:xxxcl.in-commodities.local\ca55c290c2a7,27650
   --- End of inner exception stack trace ---
   at Microsoft.FSharp.Control.AsyncResult`1.Commit() in D:\a\_work\1\s\src\FSharp.Core\async.fs:line 454
   at Microsoft.FSharp.Control.AsyncPrimitives.QueueAsyncAndWaitForResultSynchronously[a](CancellationToken token, FSharpAsync`1 computation, FSharpOption`1 timeout) in D:\a\_work\1\s\src\FSharp.Core\async.fs:line 1139
   at Microsoft.FSharp.Control.AsyncPrimitives.RunSynchronously[T](CancellationToken cancellationToken, FSharpAsync`1 computation, FSharpOption`1 timeout) in D:\a\_work\1\s\src\FSharp.Core\async.fs:line 1166
   at Microsoft.FSharp.Control.FSharpAsync.RunSynchronously[T](FSharpAsync`1 computation, FSharpOption`1 timeout, FSharpOption`1 cancellationToken) in D:\a\_work\1\s\src\FSharp.Core\async.fs:line 1515

To reproduce

open Microsoft.Data.SqlClient

let connection = new SqlConnection(connectionString)
connection.Open()

Expected behavior

Browser should open to authenticate.

Further technical details

Microsoft.Data.SqlClient version: 5.2.0
.NET target: 8.0
SQL Server version: Microsoft SQL Azure (RTM) - 12.0.2000.8
Operating system: macOS 14.3.1

Additional context

It works with Microsoft.Data.SqlClient 5.1.4 and 5.2.0-preview5.24024.3.

[ErikEJ]

@esbenbjerre Your connection string and SQL Server info?

[esbenbjerre]

Server=<server>;
Database=<database>;
ApplicationIntent=ReadOnly;
Authentication="Active Directory Interactive";

Microsoft SQL Azure (RTM) - 12.0.2000.8

[esbenbjerre]

I can add that it works with this connection string (without AAD)

Server=<server>;
Database=<database>;
ApplicationIntent=ReadOnly;
Uid=<username>;
Pwd=<password>;

[David-Engel]

Can you show what the value of your <server> setting is? Based on the stack trace, it looks like it probably has a \ in it. Feel free to change the server values to dummy ones, but show any non-alphanumeric characters you have included.

[esbenbjerre]

Can you show what the value of your <server> setting is? Based on the stack trace, it looks like it probably has a \ in it. Feel free to change the server values to dummy ones, but show any non-alphanumeric characters you have included.

It does not include backslash. It is abcd-efghi.database.windows.net (abcd-efghi being placeholders for two words containing only [a-z].

[ErikEJ]

What is:

xxxcl.in-commodities.local\ca55c290c2a7,27650 ??

That looks like a bad server name mixing instance name and port.

[esbenbjerre]

What is:

xxxcl.in-commodities.local\ca55c290c2a7,27650 ??

That looks like a bad server name mixing instance name and port.

I see that's a bit misleading. It's a local alias which points to abcd-efghi.database.windows.net. I normally use the Azure DNS. While troubleshooting I attempted to use our local alias as well as the IP address to see if it would solve the issue. The stack trace was from the first attempt.
If I use the Azure DNS I get this stack trace.

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): Undefined error: 0
   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 <StartupCode$FSI_0006>.$FSI_0006.main@() in /xxxx/test.fsx:line 35
   at System.RuntimeMethodHandle.InvokeMethod(Object target, Void** arguments, Signature sig, Boolean isConstructor)
   at System.Reflection.MethodBaseInvoker.InvokeWithNoArgs(Object obj, BindingFlags invokeAttr)
ClientConnectionId:da136438-e482-4aa9-b158-05fa56bc3f87
Routing Destination:abcd-efghi.database.windows.net\d87d8c2aceac,44202
Stopped due to error

If I enable event tracing as per this article then I see

SqlConnectionFactory.CreateConnectionPoolGroupOptions | Set connection pool CreateTimeout '150000' when Authentication mode 'ActiveDirectoryInteractive' is used.
<prov.DbConnectionHelper.ConnectionString_Set|API> 2, 'Server=abcd-efghi.database.windows.net;
  ApplicationIntent=ReadOnly;
  Authentication=Active Directory Interactive'
<sc.SqlConfigurableRetryLogicManager.ConnectionProvider|INFO> Requested the ConnectionProvider value.
TdsParser.Connect | SEC | Connection Object Id 6, Authentication Mode: ActiveDirectoryInteractive
SNIProxy.CreateConnectionHandle | Info | Session Id ef119642-67c0-4fa7-9fe6-09d981d135bb, SNI Handle Type: Microsoft.Data.SqlClient.SNI.SNITCPHandle
TdsParserStateObjectManaged.CreatePhysicalSNIHandle | Info | State Object Id 11, Session Id ef119642-67c0-4fa7-9fe6-09d981d135bb, ServerName abcd-efghi.database.windows.net, Async = True
TdsParserStateObjectManaged.GetConnectionId | Info | Session Id ef119642-67c0-4fa7-9fe6-09d981d135bb
<sc.TdsParser.Connect|SEC> Sending prelogin handshake
<sc.TdsParser.SendPreLoginHandshake|INFO> ClientConnectionID ef119642-67c0-4fa7-9fe6-09d981d135bb, ActivityID ca95fd4c-a45a-4eb4-8369-9167f44e534c:2
TdsParserStateObjectManaged.WritePacket | Info | Session Id ef119642-67c0-4fa7-9fe6-09d981d135bb, SendAsync Result 0
<sc.TdsParser.Connect|SEC> Consuming prelogin handshake
TdsParserStateObjectManaged.ReadSyncOverAsync | Info | State Object Id 11, Session Id ef119642-67c0-4fa7-9fe6-09d981d135bb
TdsParserStateObjectManaged.ReleasePacket | Info | State Object Id 11, Session Id ef119642-67c0-4fa7-9fe6-09d981d135bb, Packet DataLeft 54
TdsParserStateObjectManaged.EnableSsl | Info | Session Id ef119642-67c0-4fa7-9fe6-09d981d135bb
<sc.TdsParser.Connect|SEC> Prelogin handshake successful
<sc.TdsParser.TdsLogin|SEC> Sending federated authentication feature request & wirte = False
<sc.TdsParser.TdsLogin|SEC> Sending federated authentication feature request
<sc.TdsParser.TdsLogin|SEC> Sending federated authentication feature request & wirte = True
TdsParserStateObjectManaged.WritePacket | Info | Session Id ef119642-67c0-4fa7-9fe6-09d981d135bb, SendAsync Result 0
TdsParserStateObjectManaged.ReadSyncOverAsync | Info | State Object Id 11, Session Id ef119642-67c0-4fa7-9fe6-09d981d135bb
TdsParserStateObjectManaged.ReleasePacket | Info | State Object Id 11, Session Id ef119642-67c0-4fa7-9fe6-09d981d135bb, Packet DataLeft 205
<sc.TdsParser.TryRun|SEC> Received login acknowledgement token
<sc.SqlInternalConnectionTds.LoginNoFailover> Routed to abcd-efghi.database.windows.net
TdsParserStateObjectManaged.Dispose | Info | State Object Id 11, Session Id ef119642-67c0-4fa7-9fe6-09d981d135bb, Disposing session Handle and counters.
TdsParserStateObjectManaged.Dispose | Info | State Object Id 11, Session Id ef119642-67c0-4fa7-9fe6-09d981d135bb, sessionHandle is available, disposing session.
TdsParser.Connect | SEC | Connection Object Id 6, Authentication Mode: ActiveDirectoryInteractive
SNIProxy.CreateConnectionHandle | Info | Session Id null, SNI Handle Type: null
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: 26 - Error Locating Server/Instance Specified)', Procedure '', Line Number 0, Batch Index -1
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: 26 - Error Locating Server/Instance Specified)', Procedure '', Line Number 0, Batch Index -1
TdsParserStateObjectManaged.Dispose | Info | State Object Id 12, sessionHandle not available, could not dispose session.
<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.
TdsParser.Connect | SEC | Connection Object Id 6, Authentication Mode: ActiveDirectoryInteractive
SNIProxy.CreateConnectionHandle | Info | Session Id null, SNI Handle Type: null
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: 26 - Error Locating Server/Instance Specified)', Procedure '', Line Number 0, Batch Index -1
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: 26 - Error Locating Server/Instance Specified)', Procedure '', Line Number 0, Batch Index -1
TdsParserStateObjectManaged.Dispose | Info | State Object Id 13, sessionHandle not available, could not dispose session.
<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.
TdsParser.Connect | SEC | Connection Object Id 6, Authentication Mode: ActiveDirectoryInteractive
SNIProxy.CreateConnectionHandle | Info | Session Id null, SNI Handle Type: null
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: 26 - Error Locating Server/Instance Specified)', Procedure '', Line Number 0, Batch Index -1
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: 26 - Error Locating Server/Instance Specified)', Procedure '', Line Number 0, Batch Index -1
TdsParserStateObjectManaged.Dispose | Info | State Object Id 14, sessionHandle not available, could not dispose session.
<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.
TdsParser.Connect | SEC | Connection Object Id 6, Authentication Mode: ActiveDirectoryInteractive
SNIProxy.CreateConnectionHandle | Info | Session Id null, SNI Handle Type: null
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: 26 - Error Locating Server/Instance Specified)', Procedure '', Line Number 0, Batch Index -1
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: 26 - Error Locating Server/Instance Specified)', Procedure '', Line Number 0, Batch Index -1
TdsParserStateObjectManaged.Dispose | Info | State Object Id 15, sessionHandle not available, could not dispose session.
<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.
TdsParser.Connect | SEC | Connection Object Id 6, Authentication Mode: ActiveDirectoryInteractive
SNIProxy.CreateConnectionHandle | Info | Session Id null, SNI Handle Type: null
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: 26 - Error Locating Server/Instance Specified)', Procedure '', Line Number 0, Batch Index -1
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: 26 - Error Locating Server/Instance Specified)', Procedure '', Line Number 0, Batch Index -1
TdsParserStateObjectManaged.Dispose | Info | State Object Id 16, sessionHandle not available, could not dispose session.
<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.
TdsParser.Connect | SEC | Connection Object Id 6, Authentication Mode: ActiveDirectoryInteractive
SNIProxy.CreateConnectionHandle | Info | Session Id null, SNI Handle Type: null
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: 26 - Error Locating Server/Instance Specified)', Procedure '', Line Number 0, Batch Index -1
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: 26 - Error Locating Server/Instance Specified)', Procedure '', Line Number 0, Batch Index -1
TdsParserStateObjectManaged.Dispose | Info | State Object Id 17, sessionHandle not available, could not dispose session.
<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.
TdsParser.Connect | SEC | Connection Object Id 6, Authentication Mode: ActiveDirectoryInteractive
SNIProxy.CreateConnectionHandle | Info | Session Id null, SNI Handle Type: null
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: 26 - Error Locating Server/Instance Specified)', Procedure '', Line Number 0, Batch Index -1
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: 26 - Error Locating Server/Instance Specified)', Procedure '', Line Number 0, Batch Index -1
TdsParserStateObjectManaged.Dispose | Info | State Object Id 18, sessionHandle not available, could not dispose session.
<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.
TdsParser.Connect | SEC | Connection Object Id 6, Authentication Mode: ActiveDirectoryInteractive
SNIProxy.CreateConnectionHandle | Info | Session Id null, SNI Handle Type: null
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: 26 - Error Locating Server/Instance Specified)', Procedure '', Line Number 0, Batch Index -1
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: 26 - Error Locating Server/Instance Specified)', Procedure '', Line Number 0, Batch Index -1
TdsParserStateObjectManaged.Dispose | Info | State Object Id 19, sessionHandle not available, could not dispose session.
<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.
TdsParser.Connect | SEC | Connection Object Id 6, Authentication Mode: ActiveDirectoryInteractive
SNIProxy.CreateConnectionHandle | Info | Session Id null, SNI Handle Type: null
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: 26 - Error Locating Server/Instance Specified)', Procedure '', Line Number 0, Batch Index -1
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: 26 - Error Locating Server/Instance Specified)', Procedure '', Line Number 0, Batch Index -1
TdsParserStateObjectManaged.Dispose | Info | State Object Id 20, sessionHandle not available, could not dispose session.
<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.
<sc.SqlInternalConnectionTds.LoginFailure|RES|CPOOL> 6
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): Undefined error: 0
   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 <StartupCode$FSI_0008>.$FSI_0008.main@() in /xxxx/test.fsx:line 35
   at System.RuntimeMethodHandle.InvokeMethod(Object target, Void** arguments, Signature sig, Boolean isConstructor)
   at System.Reflection.MethodBaseInvoker.InvokeWithNoArgs(Object obj, BindingFlags invokeAttr)
ClientConnectionId:fcb72224-99b9-4898-9114-b8606fe460ea
Routing Destination:abcd-efghi.database.windows.net\d87d8c2aceac,44202
Stopped due to error

[esbenbjerre]

Note that I do not have the \ca55c290c2a7,27650 or \d87d8c2aceac,44202 part in my connection string. That appears to come from SqlClient.

[David-Engel]

It looks like your connection of getting routed/redirected to a server with both port and instance name specified. This is probably related to the same regression in 5.2.0 on non-Windows. The driver should be ignoring the instance name for the purposes of the socket connection since the user has already specified the port. We do plan to hotfix the issue.

[esbenbjerre]

Inspired by #2394 I can confirm that it works if I specify the port number

Server=abcd-efghi.database.windows.net,1433;
Database=<database>;
ApplicationIntent=ReadOnly;
Authentication="Active Directory Interactive"

[cheenamalhotra]

Checking in, is this issue resolved with v5.2.1? As it contains a related fix: #2436

[esbenbjerre]

Yes this issue was resolved and the issue can be closed.