ASP.NET Web API deployed to Azure fails to connect to Azure SQL Database

Data and Databases
1

Hey everyone, I’m stuck with a problem. My ASP.NET Web API works fine locally with Azure SQL, but when I deploy it to Azure, it throws an HTTP 500 error. I’m using the same connection string in both places.

Here’s a snippet of my program.cs:

var dbConnection = string.Empty;

if (app.Environment.IsDevelopment()) {
    config.AddEnvironmentVariables().AddJsonFile("dev-settings.json");
    dbConnection = config.GetConnectionString("SQL_DB_CONNECTION");
} else {
    dbConnection = Environment.GetEnvironmentVariable("SQL_DB_CONNECTION");
}

app.Services.AddDbContext<AppDbContext>(opts => opts.UseSqlServer(dbConnection));

var webApp = app.Build();

I’ve set up the firewall rules to allow Azure access in SQL settings. The connection string is added to the web app settings too. I even tried using the service connector as suggested in Microsoft’s docs.

What am I missing? Any ideas on how to fix this connection issue in the cloud?

2

hey there! have u tried checking the logs in azure? sometimes they give clues about whats going wrong. also, maybe try using a tool like postman to test ur API endpoints directly? it could help narrow down if its a db issue or something else. what kinda authentication r u using for the database connection?

3

I encountered a similar issue when deploying my ASP.NET Web API to Azure. The problem might be related to how you’re retrieving the connection string in production. Instead of using Environment.GetEnvironmentVariable(), try using Configuration.GetConnectionString() for both environments. This approach ensures consistency and leverages Azure’s configuration management.

Also, double-check your Azure Web App’s configuration. Ensure the connection string is correctly set in the ‘Configuration’ section under ‘Connection strings’, not just in ‘Application settings’. The name should match what you’re using in your code.

Lastly, verify that your Azure SQL Server’s firewall settings allow connections from Azure services. There’s a specific option for this in the SQL Server’s networking settings. Enabling it often resolves connectivity issues from Azure-hosted applications.

4

hey man, i had this problem too. check ur web.config file in azure. sometimes it doesnt update properly when u deploy. also, make sure ur using the right server name in the connection string. it should be something like yourserver.database.windows.net. hope this helps!