7

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.

enter image description here

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.

enter image description here

Cœur
  • 37,241
  • 25
  • 195
  • 267
Dídac Punyet
  • 315
  • 3
  • 15
  • 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 Apr 24 '16 at 17:30
  • 1
    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 Apr 24 '16 at 18:32
  • 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 Apr 25 '16 at 14:09
  • Just checked and yes, both are hosted in "West Europe" region. – Dídac Punyet Apr 25 '16 at 14:34
  • Were you able to get the IP from the properties tab and try adding it that way? – Joe Raio Apr 25 '16 at 18:43
  • I got the outbound IPs, which I'm not sure what they are. Anyway, I added them in the firewall whitelist and no success. – Dídac Punyet Apr 25 '16 at 19:07
  • You might refer to https://support.microsoft.com/en-us/help/10085/troubleshooting-connectivity-issues-with-microsoft-azure-sql-database for the issue. I am guessing it is your azure webapp which block access 1433 port. – qingsong Apr 25 '16 at 19:11

1 Answers1

1

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

Joe Raio
  • 1,795
  • 1
  • 12
  • 17
  • Thanks for your efforts, but I already did that. And it works perfectly fine running locally. BUT when I publish the website, it doesn't work. Since safety is not my concern now, I even print on the website the connection string being used, and it is the correct one (it works in local IIS, SSMS, desktop app), but I get error 40 when I publish. Can it be related to entity framework? – Dídac Punyet Apr 25 '16 at 19:19
  • I just did a test by setting up a new resource group, db server, db and web app (all in West Euro). I then created an ASP.net 5 application, updated the connection string in appsettings.json and published it. It worked perfect and you can view it here: http://jmrwesteuroweb.azurewebsites.net/. There must be something else that is causing the issue. Can you either email me the URL or the project files and I would be happy to look at them for you? joe.raio@microsoft.com . – Joe Raio Apr 25 '16 at 19:22
  • Something that comes to mind is that the error states theres a "named pipes" problem, but the connections tring has "tcp" on it. I understand there are two different protocols, so maybe that is something usefull. – Dídac Punyet Apr 25 '16 at 19:22
  • 2
    The issue is where you are declaring your dbContext. I would recommend starting from one of the built in ASP.net 5 templates and putting your connection string in appsettings.json. You will see then that the issue lies within your code, not Azure. Hope it all works out! – Joe Raio Apr 25 '16 at 22:00
  • 1
    That was it, the DbContext, created automatically by the tool "dnx ef dbcontext scaffold", was using a local connection string, event if I had the correct connection string at application level. Thanks Joe! – Dídac Punyet Apr 25 '16 at 22:14
  • The publish profile can be setup to override these strings as well. That is something to check in the future. – rollsch Aug 09 '17 at 06:05
  • I have the same issue. Will you please guide me how you solved the problem? – Usman May 29 '20 at 15:10
  • @DídacPunyet "That was it, the DbContext, created automatically by the tool..." THANK YOU!!! This little comment solved a problem I've been stewing over for two hours. My .config file is good, it was a hardcoded connection string that was killing me. – Bob Kaufman Jan 01 '21 at 01:37