[Bug]: DAB fails to connect to Azure SQL server

[vishms]

What happened?

We have deployed data-api-builder as a pod in Azure kubernetes service (AKS). DAB fails to start because it is unable to connect to the Azure SQL server.
SQL server's networking: Public access is blocked. apps connect to the SQL server through private endpoint.
Connection mode is redirect.

If I change the connection mode to 'proxy' then DAB connects to the database fine but it fails to connect when the SQL's connection mode is redirect.
Other applications in the same AKS and same node are connecting to the same SQL server database without any issue.

This has been raised with Azure database support. Investigation involved collaboration with SQL server, networking and AKS teams and couldn't figure out the root cause.

I have been suggested by the Azure support team to raise it as a bug in DAB for further investigation

connection string that DAB is using:
Server=tcp:,1433;Initial Catalog=;Persist Security Info=False;User ID=;Password=*;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

Version

1.4.35, 1.2.14 and 1.5.29-rc

What database are you using?

Azure SQL

What hosting model are you using?

Local (including CLI)

Which API approach are you accessing DAB through?

REST

Relevant log output

info: Azure.DataApiBuilder.Core.Services.ISqlMetadataProvider[0]
      [ms_groups] REST path: /reporting-api/ms_groups
fail: Azure.DataApiBuilder.Service.Startup[0]
      Unable to complete runtime initialization. Refer to exception for error details.
      Azure.DataApiBuilder.Service.Exceptions.DataApiBuilderException: Cannot obtain Schema for entity field_definitions with underlying database object source: dbo.field_definitions due to: 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)
         at Azure.DataApiBuilder.Core.Services.SqlMetadataProvider`3.HandleOrRecordException(Exception e) in /_/src/Core/Services/MetadataProviders/SqlMetadataProvider.cs:line 100
         at Azure.DataApiBuilder.Core.Services.SqlMetadataProvider`3.PopulateObjectDefinitionForEntity(String entityName, Entity entity) in /_/src/Core/Services/MetadataProviders/SqlMetadataProvider.cs:line 1116
         at Azure.DataApiBuilder.Core.Services.SqlMetadataProvider`3.PopulateObjectDefinitionForEntities() in /_/src/Core/Services/MetadataProviders/SqlMetadataProvider.cs:line 1054
         at Azure.DataApiBuilder.Core.Services.SqlMetadataProvider`3.InitializeAsync() in /_/src/Core/Services/MetadataProviders/SqlMetadataProvider.cs:line 289
         at Azure.DataApiBuilder.Core.Services.MetadataProviders.MetadataProviderFactory.InitializeAsync() in /_/src/Core/Services/MetadataProviders/MetadataProviderFactory.cs:line 92
         at Azure.DataApiBuilder.Service.Startup.PerformOnConfigChangeAsync(IApplicationBuilder app) in /_/src/Service/Startup.cs:line 718
fail: Azure.DataApiBuilder.Service.Startup[0]
      Could not initialize the engine with the runtime config file: dab-config.json
info: Microsoft.Hosting.Lifetime[0]
      Application is shutting down...
fail: Microsoft.Extensions.Hosting.Internal.Host[11]
      Hosting failed to start
      System.Threading.Tasks.TaskCanceledException: A task was canceled.
         at Microsoft.AspNetCore.Server.Kestrel.Core.KestrelServerImpl.BindAsync(CancellationToken cancellationToken)
         at Microsoft.AspNetCore.Server.Kestrel.Core.KestrelServerImpl.StartAsync[TContext](IHttpApplication`1 application, CancellationToken cancellationToken)
         at Microsoft.AspNetCore.Hosting.GenericWebHostService.StartAsync(CancellationToken cancellationToken)
         at Microsoft.Extensions.Hosting.Internal.Host.<StartAsync>b__15_1(IHostedService service, CancellationToken token)
         at Microsoft.Extensions.Hosting.Internal.Host.ForeachService[T](IEnumerable`1 services, CancellationToken token, Boolean concurrent, Boolean abortOnFirstException, List`1 exceptions, Func`3 operation)
Unable to launch the Data API builder engine.

Code of Conduct

• I agree to follow this project's Code of Conduct

[Aniruddh25]

hi @vishms, thank you for reporting this.

Looking at the error from your logs:

Azure.DataApiBuilder.Service.Exceptions.DataApiBuilderException: Cannot obtain Schema for entity field_definitions with underlying database object source: dbo.field_definitions due to: 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 to be specific to how the server is configured to connect to apps.

Since dab cannot locate the server, have you tried ensuring SQL can accept connections from the pod in AKS that hosts dab?
Have you tried if dab is able to find SQL if it was granted Public access? If this works, we can narrow down the issue to usage of the private endpoint.

If you already have an Azure Support Ticket with SQL DB Connectivity team, please have them direct the ticket to the dab team internally, so we can take a look at the logs and identify why the connection from dab is failing explicitly. We will need additional information from the logs to identify what is going on.

P.S. I have also informed the support team from my end (I had missed their Teams message earlier) thanks for your patience.

[crushonme]

@Aniruddh25 This is a bug in the SqlClient 5.2.0 version. In DAB, we used 5.2.0 version.

Could you please upgrade the docker image for DAB to 5.2.3 which fixed the issue? Thanks.

Refer to dotnet/SqlClient#2395

[vishms]

Thanks for finding the root cause of this issue. We are currently pulling the image from mcr.microsoft.com/azure-databases/data-api-builder Microsoft registry and not building the DAB locally. So, wondering if the SqlClient version be updated in any upcoming DAB release please? Thanks

[Aniruddh25]

We expect to create a new 1.5 release candidate by the end of this week 30 May Pacific Time unless we encounter any new issues in our release pipeline. Thanks for your patience.

[vishms]

Fantastic. I'll wait for the new build and then test application on new image. Thanks