Unable to configure stored procedure

[jshallBRI]

I am trying to use Data API Builder to access a stored procedure in my Azure SQL Server database.

I am using Microsoft.DataApiBuilder 0.7.6+61de247acf65280d93783072226f695536591ffb.

The stored procedure can be accessed successfully with SQL Server Management Studio (SSMS) using:

EXEC AMAP.SPOpenTaxLots @reportDate='2023-08-23', @account='xxxx'

But when I try to start dab the initialization errors out with the following message:

Azure.DataApiBuilder.Service.Exceptions.DataApiBuilderException: No stored procedure definition found for the given database object SPOpenTaxLots

My configuration file looks like:

...
    "SPOpenTaxLot": {
      "source": {
        "type": "stored-procedure",
        "object": "AMAP.SPOpenTaxLots",
        "parameters": {
          "reportDate": "2100-12-31",
          "account": "xxxx"
        }
      },
      "permissions": [
        {
          "role": "anonymous",
          "actions": [
            "execute"
          ]
        }
      ],
      "rest": {
        "methods": [
          "get"
        ]
      },
      "graphql": {
        "operation": "query"
      }
    }
  }
...

dab can't seem to find ANY of my stored procedures during initialization (including those located in the dbo schema).

Interestingly, dab DOES initialize successfully if I point the source.object to a table-valued function. It then (as would be expected) errors out when I try to access it, though, saying "The request for procedure TVFOpenTaxLots failed because TVFOpenTaxLots is a table valued function object."

Could someone suggest how I might go about troubleshooting this?

[jshallBRI]

So, it turns out that the database user must have EXECUTE permissions in order to run a stored procedure. I had originally set it up for read/write permissions, but failed to grant the dab application EXECUTE permissions.

I granted EXECUTE permission to the dab application in SQL Server and the problem was resolved!

GRANT EXECUTE TO [dabUsername]