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

Error we are getting while accessing API
Error we are getting while accessing API

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.

Error we are getting while accessing API
Error we are getting while accessing API

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

Working from localhost
Working from 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.

VS solution Web config error mode added
VS solution Web config error mode added

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 –

Detailed error report with IP address
Detailed error report with IP address

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

Azure SQL database “Set server firewall settings”
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”.
Azure SQL database “Set server firewall settings” 2
Azure SQL database “Set server firewall settings” 2

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

Final result after changes
Final result after changes

You may also like...

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: