Allowing a web app on App Service to access Sql Server resource on Azure

Question

I'd like to find the correct way to allow to access Sql Database on Sql Server resource on Azure from a web app hosted on Azure App Service.

I have checked an option "Allow Azure services and resources to access this server" which is present in Azure portal on Networking tab for the Sql Server resource. The explanation says "This option configures the firewall to allow connections from IP addresses allocated to any Azure service or asset, including connections from the subscriptions of other customers."

While having this option checked, I had a problem connecting to the Sql Database. The error log contained this error:

Microsoft.Data.SqlClient.SqlException (0x80131904): Cannot open server 'my-sql-server' requested by the login. Client with IP address '20.215.***.***' is not allowed to access the server. To enable access, use the Azure Management Portal or run sp_set_firewall_rule on the master database to create a firewall rule for this IP address or address range. It may take up to five minutes for this change to take effect.

The error was gone as soon as I added an explicit rule to the Sql Server firewall to allow this particular IP address.

However I am not sure why this was needed. Why the option to allow Azure services to access the server did not work for Azure App Service where my app is hosted. And secondly, whether this IP address will always stay the same for my Web App resource, even after future deployments.

Answer 1

I found this thread you can check it : https://stackoverflow.com/questions/34760223/client-with-ip-address-is-not-allowed-to-access-the-server-azure-sql-database

If you prefer using T-SQL, you can connect to the master database and execute the sp_set_firewall_rule stored procedure: