4

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.

rossotron
  • 63
  • 1
  • 5
  • 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. – flytzen Mar 22 '18 at 18:33
  • 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 Mar 22 '18 at 18:42
  • 3
    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 Mar 22 '18 at 19:09

6 Answers6

8

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

Tuco
  • 1,620
  • 1
  • 14
  • 15
  • 1
    Exactly my issue with Net 5. I'll have to look into why later. Thanks – tdahman1325 Feb 25 '21 at 17:42
  • Thank you lord! this did it for me. I migrated two databases from SSMS and one of them worked fine every time. Tried multiple times. But the second one keept failing. But no more. – AllramEst Jul 20 '21 at 07:53
  • I ran into this same issue, and your solution worked, but strangely, I didn't need to do this for all the other environments I had set up. I am also using .NET 5. – mike gold Jun 03 '22 at 14:53
  • 1
    The configurations in the Azure app service are confusing and a touch misleading because they are actually setting environment variables. By longstanding convention dotnet apps acquire their configuration via 1.) passed in parameters, 2.) configuration files, 3.) environment variables; each successive source taking precedence over the previous ones. That explains why the connection string set on the Azure app service was clobbering the one in your config file. – themooserooster Jul 06 '23 at 14:15
3

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.

Malith
  • 506
  • 5
  • 9
0

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

King Jk
  • 1,069
  • 14
  • 22
0

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.

Dan
  • 1,011
  • 9
  • 6
0

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

Vavan
  • 21
  • 3
0

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

Harvey Darvey
  • 706
  • 8
  • 16