Azure web app can't access azure DB

Question

I have an Azure trial account with an sql database and an asp.net 5 web app. The db server firewall has a rule for my local computer IP, and also the checkbox "Allow access to Azure service" on. I can connect from my local Sql Server Manager to the azure database without incident.

The web app has this connection string set up in its 'Application Settings/Connection strings' section. I'have checked the wep app IS using this connection string:

"Server=tcp:[MyServerName].database.windows.net,1433;Database=[MyDbName];User ID=[MyUserName]@[MyServerName];Password=[MyPassword];Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"

This same connection string is used in a desktop client and works fine. However, the web app is unable to connect to the server, and throws this exception, which is pretty clear, but I don't know how to solve.

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

I know very similar questions have been asked and answered here, but none of them are the exact same problem I'm facing, neither have been a solution for me.

Thanks everyone for your time.

UPDATE 1

As a test, I've configured the website to run on my local IIS, against the azure DB, and it works also. So, desktop applications, sql server manager and IIS can access this database from my local computer. Of course my IP is whitelisted in the server firewall, but azure app services are whitelisted also and don't have access.

UPDATE 2

Joe Raio's comment made me double check the region configuration, and here's an screenshot of the current settings. I believe everything is correct.

Have you also tried adding your web server's address to the DB's firewall settings? I'll try to reproduce your scenario when I get some time to see if I can troubleshoot further. — Shahed C - MSFT
The website is hosted in Azure App Services, so I don't know how to find out the IP. However, in the firewall settings for the sql server, there's a rule enable "Allow access to Azure service", which is supposed to whitelist all applications inside azure. — Dídac Punyet
You can click on the "properties" tab of the web app to view the IP address. Although from what you are describing you should not have to add the IP address of the web app. It should work. Are both the web app and sql database hosted in the same Azure Geo Region? — Joe Raio
Just checked and yes, both are hosted in "West Europe" region. — Dídac Punyet
Were you able to get the IP from the properties tab and try adding it that way? — Joe Raio

Joe Raio Joe Raio · Accepted Answer · 2016-04-25T19:11:47

The web app has this connection string set up in its 'Application Settings/Connection strings' section. I'have checked the wep app IS using this connection string:

This is not where you set your connection string for your ASP.net 5 application.

Open your the file appsettings.json, the first section should contain the following data

  "Data": {
    "DefaultConnection": {
      "ConnectionString": "Server=(localdb)\\mssqllocaldb;Database=aspnet5-WestEuroSODBAccess-96be0407-8bee-4c89-97e5-f6711136f106;Trusted_Connection=True;MultipleActiveResultSets=true"
    }

Place your connection string in there. Similar to this

  "Data": {
    "DefaultConnection": {
      "ConnectionString": "Server=tcp:[MyServerName].database.windows.net,1433;Database=[MyDbName];User ID=[MyUserName]@[MyServerName];Password=[MyPassword];Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"
    }

That will resolve the issue. I did not catch originally that you were setting this up as an ASP.net 5 application.

Additional information can be found here: http://docs.asp.net/en/latest/fundamentals/configuration.html

Azure web app can't access azure DB

1 Answers