data-api-builder [Bug]: Cannot obtain Schema for entity

I'm trying to start dab with this configuration

  "$schema": "https://github.com/Azure/data-api-builder/releases/download/v1.1.7/dab.draft.schema.json",
  "data-source": {
    "database-type": "mssql",
    "connection-string": "@env('cnn')",
    "options": {
      "set-session-context": false
    }
  },
  "runtime": {
    "rest": {
      "enabled": true,
      "path": "/api",
      "request-body-strict": true
    },
    "graphql": {
      "enabled": true,
      "path": "/graphql",
      "allow-introspection": true,
      "multiple-mutations": {
        "create": {
          "enabled": true
        }
      }
    },
    "host": {
      "cors": {
        "origins": [],
        "allow-credentials": false
      },
      "authentication": {
        "provider": "StaticWebApps"
      },
      "mode": "development"
    }
  },
  "entities": {
    "MGAnaArt": {
      "source": {
        "object": "dbo.MG_AnaArt",
        "type": "table"
      },
      "graphql": {
        "enabled": true,
        "type": {
          "singular": "MGAnaArt",
          "plural": "MGAnaArts"
        }
      },
      "rest": {
        "enabled": true
      },
      "permissions": [
        {
          "role": "anonymous",
          "actions": [
            {
              "action": "*"
            }
          ]
        }
      ]
    }
  }
}

where the user used in the connection string is a SQL user. Running the dab from one computer starts fine, however when running on another PC with the same user and password the error shown in the "Relevant log output" section appears

Using the sql profiler, we saw that there is no query to the database when run with dab start. If instead the dab validate command is used, dab correctly accesses the database (activity is always displayed via profiler) and dab detects, for example, the triggers present on the table as per the output

Information: Microsoft.DataApiBuilder 1.1.7
Information: Config not provided. Trying to get default config based on DAB_ENVIRONMENT...
Information: Environment variable DAB_ENVIRONMENT is (null)
Information: Validating config file: dab-config.json
Loading config file from dab-config.json.
Information: The config satisfies the schema requirements.
Information: Validating entity relationships.
Information: [MGAnaArt] REST path: /api/MGAnaArt
Information: An insert trigger is enabled for the entity: MGAnaArt
Information: An update trigger is enabled for the entity: MGAnaArt
Information: An update trigger is enabled for the entity: MGAnaArt
Information: An insert trigger is enabled for the entity: MGAnaArt
Information: Config is valid.

Version

1.1.7

What database are you using?

Azure SQL

What hosting model are you using?

No response

Which API approach are you accessing DAB through?

GraphQL

Relevant log output

Information: Microsoft.DataApiBuilder 1.1.7
Information: Config not provided. Trying to get default config based on DAB_ENVIRONMENT...
Information: Environment variable DAB_ENVIRONMENT is (null)
Loading config file from dab-config.json.
Information: Loaded config file: dab-config.json
Information: Setting default minimum LogLevel: Debug for Development mode.
Starting the runtime engine...
Loading config file from dab-config.json.
info: Microsoft.AspNetCore.DataProtection.KeyManagement.XmlKeyManager[63]
      User profile is available. Using 'C:\Users\myuser\AppData\Local\ASP.NET\DataProtection-Keys' as key repository and Windows DPAPI to encrypt keys at rest.
info: Azure.DataApiBuilder.Core.Services.ISqlMetadataProvider[0]
      [MGAnaArt] REST path: /api/MGAnaArt
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 MGAnaArt with underlying database object source: dbo.MG_AnaArt due to: Invalid object name 'dbo.MG_AnaArt'.
         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 65
         at Azure.DataApiBuilder.Service.Startup.PerformOnConfigChangeAsync(IApplicationBuilder app) in /_/src/Service/Startup.cs:line 613
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.
Error: Failed to start the engine.

Code of Conduct

[X] I agree to follow this project's Code of Conduct

May 21 '24 14:05 MauroVevy

Hi @MauroVevy a few things to try:

There looks to be an issue accessing dbo.MG_AnaArt

Cannot obtain Schema for entity MGAnaArt with underlying database object source: dbo.MG_AnaArt due to: Invalid object name 'dbo.MG_AnaArt'.

There may be an issue with the connection string: validate that the environment variable cnn is populated.
Run dab start --config "Full_Path_To_Config" to ensure dab is resolving the correct config file
Hard code connection string (temporarily) to your config file, and run dab with dab start --config "Full_Path_To_Config" to rule out any issues resolving the environment variable value.

If none of the above work please supply logs of those mitigation attempts. there may also be an issue with the db account referenced in the connection string

May 21 '24 15:05 seantleonard

Downloading the sources and debugging I found the reason. I have in my computer an environment variable called connectionstring which contains the connectionstring to an old database.

In the methods public static ITelemetryChannel? CustomTelemetryChannel { get; set; } of file data-api-builder\src\Service\Startup.cs there is this code:

string configFileName = Configuration.GetValue<string>("ConfigFileName") ?? FileSystemRuntimeConfigLoader.DEFAULT_CONFIG_FILE_NAME;
string? connectionString = Configuration.GetValue<string?>(
    FileSystemRuntimeConfigLoader.RUNTIME_ENV_CONNECTION_STRING.Replace(FileSystemRuntimeConfigLoader.ENVIRONMENT_PREFIX, ""),
    null);

Where the variable RUNTIME_ENV_CONNECTION_STRING is equal to DAB_CONNSTRING and FileSystemRuntimeConfigLoader.ENVIRONMENT_PREFIX is equal to DAB_ The result of Replace is CONNSTRING the value of variable that contains the connection string to the wrong database.

In my opinion there are two errors:

Even though the connection string is written explicitly in the dab-config.json file, dab takes the connection string present in the environment variable.
If the environment variable is called 'DAB_CONNECTION_STRING' there should be no name substitutions.

How to test the bug

set this environment variable set connstring=Server=myserver; Database=MY_WRONG_DATABASE; Trusted_Connection=True; Integrated Security=true; TrustServerCertificate=true;

Create a dab-config.json like this:

{
  "$schema": "https://github.com/Azure/data-api-builder/releases/download/v1.1.7/dab.draft.schema.json",
  "data-source": {
    "database-type": "mssql",
    "connection-string": "Server=myserver; Database=MY_CORRECT_DATABASE; Trusted_Connection=True; Integrated Security=true; TrustServerCertificate=true;",
    "options": {
      "set-session-context": false
    }
  },

run dab start

dab in this way search the schema on MY_WRONG_DATABASE

May 22 '24 07:05 MauroVevy

@seantleonard It would be precious if DAB could write the Server and Database names when starting. This way:

it would be easier to find out errors during development
it would be nicer for the logs when running in the container

Thanks

May 22 '24 13:05 raffaeler