Azure App Service can't access Azure SQL DB, but local machine works. Allow-All-IP rule already in place.

2
votes

Currently working on an ASP.NET web application. I would like to deploy to Azure App Service but when running on App Service, the connection to my Azure SQL server can not be found. enter image description here

When I run on my local machine, the connection to the Azure SQL server works. I can also connect to my Azure SQL server via SQL Management Studio.

On the Azure SQL server I have set up a Firewall rule to allow all IP addresses, and allowed access to Azure services:enter image description here

I have done considerable searching for solutions to this problem over the past couple days, but I can't find a solution.

6
asp.netsql-serverazureazure-sql-databaseazure-web-app-service
Is your connection string in your web.config or specified in Azure? Try writing some code to output the SQL connection string to your logs; in essence, the error almost certainly means your deployed code is using the wrong connection string. – Frans
It turns out the deployed code was indeed different than the local code. I was using the "attach debugger" option in the server explorer to test changes on the App Service. This apparently does not rebuild the project. I rebuilt the project and it works fine now. – rossotron
Quick tip, Allow access to Azure services already makes sure 1st party Azure services like App Service are whitelisted in the SQL firewall. No need for the extra allow all. – evilSnobu

6 Answers

4
votes

After spending a few hours tweaking my appsettings.json, Azure firewall settings, permissions, ... it ended up being that I had my App Service > Settings > Configuration > Connection String previously set. And Azure seems to completely ignore your appsettings.json in this case.

Azure Screenshot

2
votes

I have added the Virtual IP for the Firewall rule and it fixed the issue.

enter image description here

For more troubleshooting use App insights. Most of the time it shows the exact issue.

0
votes

You can easy find the app service all outbound ip address on properties

but you need setting all of the outbound ips in your firewall enter image description here

0
votes

I ran into what could be this same problem. Changing the server admin password resolved the issue. I was able to connect running locally, but the published application was unable to log in with the server admin credentials until I reset the server admin password.

0
votes

On the azure prortal on sql database overview page click Set Server Firewall Allow Azure services and resources to access this server should be set to Yes and rest of the settings should by default Save your changes It works for me with asp.net and sql db are located in azure env

0
votes

Works with the solution given by @Tuco. Just an additional setting to remember, as you're mentioning azure sql, don't forget to change the type to SQLAzure.

enter image description here