Azure App service API error while accessing Azure SQL database through entity framework: An error has occurred.

While working on Azure API which is created to access Azure SQL data. We have a requirement where we need to access Azure SQL table data and display it in React based SPFX web part on SharePoint online modern site. To achieve that we have created a Azure SQL database and created a table in that. We have created a RESTful web api which will be deployed as Azure APP. After deployment we are getting error “An error has occurred.” While accessing the API in browser.

While debugging I figured that api is working well on localhost.

It was bit surprising to me first that it is working on localhost but not after deployment of Azure app.

When I checked Azure SQL database “Set server firewall settings” I found that I have added my local machine IP in the allowed IP list to access DB using SQL management studio. Hence found it is working from my localhost. So, after finding the reason I started checking which IP I need to provide in the allowed IP list in Azure SQL database “Set server firewall settings” settings so that my Azure APP deployed API will be able to access the Azure SQL, but the IP address is not known to me as I have not explicitly configured it for using any specific IP address.

Solution –

We can solve this by using 2 approaches.

1. Finding the IP address of the Azure app and provide that in “Set server firewall settings” in Azure SQL database.

For that, I added <customErrors mode=”Off” /> in the web.config of VS solution.

And re-published the App again to Azure.

Now after that if you access the API in browser again, you will get the detail error like following –

This will also have the App IP address which you can use to add in the Azure SQL database “Set server firewall settings”.

And it will start working. This approach will work well in case you don’t want to share the SQL DB with other Azure resources as well.

• Second option is bit easy, in Azure SQL database “Set server firewall settings” there is a flag, “Allow Azure services and resources to access this server”.

After any of the above changes my API started working from Azure web app.