Could not connect to Azure SQL when using private link

Question

We encountered an error in the application (a .NET 8.0 console app running in AKS) when trying to connect to an Azure SQL database hosted on a standalone Azure SQL server using private link, its public access is disabled.

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.

The error occurs at this line

await

Code snippet

        await using var connection = DalHelpers.GetDbConnection(options);
        await connection.OpenAsync();

    public static DbConnection GetDbConnection(CommandOptions options)
    {
        DbConnection connection = null;
        connection = new SqlConnection(options.ConnectionString);
        return connection;
    }

Exception details

2024-09-19 09:24:21.0366|ERROR|Temenos.InsightTools.ContentConsole.ConsoleRunner|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)|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.WaitForPendingOpen()
--- End of stack trace from previous location ---
   at Temenos.InsightTools.ContentConsole.Bootstrapper.Preflight(CommandOptions options) in d:\j\workspace\Integration\Insight(Analytics)\Release_InsightApplicationBuildL1\Temenos.Insight\Temenos.InsightTools.ContentConsole\Bootstrapper.cs:line 36
   at Temenos.InsightTools.ContentConsole.Bootstrapper.Preflight(CommandOptions options) in d:\j\workspace\Integration\Insight(Analytics)\Release_InsightApplicationBuildL1\Temenos.Insight\Temenos.InsightTools.ContentConsole\Bootstrapper.cs:line 89
   at Temenos.InsightTools.ContentConsole.DeploymentProcessor.Process(CommandOptions options) in d:\j\workspace\Integration\Insight(Analytics)\Release_InsightApplicationBuildL1\Temenos.Insight\Temenos.InsightTools.ContentConsole\DeploymentProcessor.cs:line 21
   at Temenos.InsightTools.ContentConsole.ConsoleRunner.Run(CommandOptions options) in d:\j\workspace\Integration\Insight(Analytics)\Release_InsightApplicationBuildL1\Temenos.Insight\Temenos.InsightTools.ContentConsole\ConsoleRunner.cs:line 56
ClientConnectionId:54f86ed5-64f8-4a76-ab3f-775128728f03
Routing Destination:r23testtdlsaassql.database.windows.net\fcb7675aad0d,9478
2024-09-19 09:24:21.0366::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)

It seems the application tried to connect routed sql server with different port.

Routing Destination:r23testtdlsaassql.database.windows.net\fcb7675aad0d,9478

Looking for guidance.

Thanks,

Michael