'Azure api works locally but not in Azure deployment, for the endpoint which need data from Azure sql db. Endpoint with hardcoded data is fine though

i have deployed a .net core web api to Azure App service. one endpoint of API which does not have anything to get from Database works fine.

[Route("api/[controller]")]
[ApiController]
public class HomeController : ControllerBase
{
    [HttpGet]
    public JsonResult Get()
    {
        return new JsonResult("Test data 333"); // Works fine  
                                               // after deployment and get the data  
    }
}

But the other endpoint of API which has to get data from database ( Azure SQL Database ) doesnot work and i get error like "apiName.azurewebsites.net/api/controllerName/gtalbsds" is not working as below screenshot. But the same endpoint works fine locally with AZURE SQL Database configured in connection string.

appsettings.json

"DefaultConnection": "Server=tcp:<azureServereName>,1433;Initial Catalog=<myDBName>;   
Persist Security Info=False;User ID=<CorrectUserName>;    
Password=<correctPassword>;MultipleActiveResultSets=False;
Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"

C# api code as below

[Route("gtalbeds")]
    [HttpGet]
    public JsonResult Get()
    {
        ItemBasicDetailsList result = new ItemBasicDetailsList();
        using (var context = new myDBContext(this._config))
        {

            var items = context.ItemBasicDetails.ToList();
            if (items != null)
            {
                result = new ItemBasicDetailsList()
                {
                    PackageFee = 150,
                    DeliveryFee = 0,
                    ItemBasicDetails = items
                };
            }
            return new JsonResult(result);
        }
    }

enter image description here

azureapiasp.net-coreazure-sql-databaseazure-api-management


Solution 1:[1]

Check in Azure , SQL Server linked with your app if "Firewall settings" --> Allow Azure services and resources to access this server" is Yes. If it is set to No, then change it to Yes . This will resolve the issue.

enter image description here

Solution 2:[2]

I was able to resolve it. Actually in Azure SQL Database instance, "Firewall settings" --> Allow Azure services and resources to access this server was set to No. After setting it to YES, the issue was resolved.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Rahul Jagga
Solution 2

Related Questions