1

I have a .NET Core API backend which is published on Azure. I also have a SQL Server database running on Azure, if I run my backend app locally it successfully connects to the online database, reading/writing etc works fine. When running the online backend however, every API call results in a 500 error. When looking in the logs there is the following error which probably causes the 500:

Microsoft.Data.SqlClient.SqlException (0x80131904): 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)

So for some reason the backend hosted on Azure does not have access to this database. In the Azure portal of my online db I have added the IP of my backend to firewall exception and "Allow remote Azure connections to server" is checked.

TLDR; everything works fine locally, published version on Azure can't connect to/find database.

Edit: this is resolved, thanks everyone who commented. (special thanks to Jason!) Solution was to use the following format of connection string:

Data Source=tcp:servername.database.windows.net,1433;Initial Catalog=db;Persist Security Info=False;User ID=user;Password=mypassword;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;
TylerH
  • 20,799
  • 66
  • 75
  • 101
bomeister
  • 43
  • 6
  • 1
    What Azure service are you publishing your appllication to? Are you running SQL Server in an Azure VM or using Azure SQL Database? Is your SQL Server (either case) deployed with public IP endpoints or private IP endpoints? – David Browne - Microsoft May 06 '20 at 19:53
  • @DavidBrowne-Microsoft I published it by clicking my solution in VS > Publish> Web Deploy. The database I'm using is hosted on Azure. I also tried using a database in an Azure VM but that gave the same error. I don't quite understand what you mean with the last question? I can connect to both databases in SSMS. – bomeister May 06 '20 at 20:07
  • I have updated my answer, also give another document about how to configure `Connection strings` in portal. Hope it useful to u. – Jason Pan May 07 '20 at 09:51
  • When you used ` VS > Publish> Web Deploy.`... where did you deploy it to? Did you deploy it to an Azure App Service? "Allow Azure" should allow this to connect. You say _IP of my backend to firewall exception_. How did you determine this IP? It's still not clear where your backend is. For many services in Azure, IP addresses will change. – Nick.Mc May 07 '20 at 09:54
  • So what was the connection string that you were using originally that only functioned locally? – Nick.Mc May 07 '20 at 12:13
  • @Nick.McDermaid I was using a connectionstring in appsettings.json `"ConnectionStrings": { "TodoContext": "Server=db.database.windows.net;Database=db;User Id = user;Password = password" },` – bomeister May 07 '20 at 12:20

1 Answers1

1

UPDATE

I operate according to the tutorial, see the screenshot for detailed steps. The official documentation contains sample code, which you can download and see. I put my conn string in my code.

If you configure your connnection strings in web.config or other files, u can see this document. And u can configure it in your portal.

enter image description here

Step 1. Modify connection strings in code, and add a interface for test.

enter image description here

Step 2. Create a Todo Table for peoject. [Make sure you have finished Firewall setting]

enter image description here

Step 3. Test in local then deploy. You can see my project run successfully and interface can be used.

enter image description here

PRIVIOUS

You can go to your sqldb ,find Connection strings and copied the one provided there.

This string was in the format:

Data Source=tcp:servername.database.windows.net,1433;Initial Catalog=db;
Persist Security Info=False;
User ID=user;Password=mypassword;
MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;
Connection Timeout=30;

There also have a post which has the same issue. For more details,u can read it.

Jason Pan
  • 15,263
  • 1
  • 14
  • 29
  • Thank you so much for your answer and the effort you put into it. I resolved my issue by copying the string from "Connection strings" and setting it as a connection string in my portal, then using it in my c# code as following: `services.AddDbContext(options =>options.UseSqlServer(Environment.GetEnvironmentVariable("SQLAZURECONNSTR_nameOfMyConnString")));` – bomeister May 07 '20 at 11:52