Failing to connect to SQL Database from Azure Web App.

Question

I got an ASP.NET Core Web App with Angular and MS SQL. Locally, I run MS SQL via Docker, which works good. I can run migrations and seed data without problem. When I uploaded the web app to an Azure resource group, I created a web app, database server and a database inside a resource group. I added the connection string and password to the web app, enabled communication from inside azure services in the database and enabled the ip of my linux in the firewall settings of the database. I can connect and perform queries to the database using Azure Data Studio. Now to my problem:

I cannot get a connection to the database from the web app. When I tried to connect to the database at first try, i got this error:
Inside the logs of web app:
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: TCP Provider, error: 40 - Could not open a connection to SQL Server)

So basically, i got to the point where i migrated the database myself using the dotnet ef database script command and then running the command on azure data studio. Now I can access the website through azure, but I can't do anything with the database at all. The only thing I get back (no matter if I fetch products or try to register) is a 500 internal server error: 

XHRGET

zen-tattoo.azurewebsites.net/api/account/user-info

[HTTP/1.1 204 No Content 56ms]

XHRGET

zen-tattoo.azurewebsites.net/api/artists?pageSize=10&pageIndex=1

[HTTP/1.1 500 Internal Server Error 14791ms]

XHRGET

zen-tattoo.azurewebsites.net/api/profile/style-options

[HTTP/1.1 200 OK 41ms]

Any help is greatly appreciated.

Answer 1

Unless you created a private vlan, on the sql database firewall, be sure allow azure services is allowed.

Answer 2

Hi @Nemanja Tomic,

Please kindly check this official doc to Configure connection strings.

This seems to be your web app not properly connecting to the database, so our troubleshooting idea is to write a test api to check what the database string is currently using.

var connectionString = Environment.GetEnvironmentVariable("SQLAZURECONNSTR__MyDbConnection") ?? configuration.GetConnectionString("MyDbConnection");

Best Regards

Jason