While working on an Azure API which was created to access Azure SQL data, we have a requirement where we need to access Azure SQL table data and display it in a React-based SPFX web part on SharePoint online modern site. To achieve that we have created an 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 the error “An error has occurred” while accessing the API in the browser.
While debugging I figured that the api is working well on localhost.
It was a bit surprising to me that at first it was 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 I 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” 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.
Solutions
We can solve this by using 2 approaches.
Step 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 to Azure.
Now after that if you access the API in browser again, you will get a detailed error like the 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.
Step 2
The second option is 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.