Azure Web App - Connection String works on local but not on server [EntityException: The underlying provider failed on Open]

Question

I have an ASP.NET Framework 4.6.1 application hosted with Azure in an App service. I'm facing a very weird problem after the deployment of the application using the Publish tool of Visual Studio.

I am using Entity Framework library which allow me to retrieve data from a database. When trying to retrieve the result from the task as below:

Task<List<service_req>> serviceRequestsTask = context.service_req.SqlQuery("SELECT * FROM (SELECT ROW_NUMBER() OVER( ORDER BY id ASC) AS RowNum, * FROM [legroupetihd].[dbo].[service_req] WHERE 1 = 1) AS RowNum WHERE RowNum >= 1 AND  RowNum < 20", "service_req").ToListAsync();
//This line is causing an error 404 in production
List<service_req> serviceRequests = serviceRequestsTask.Result;

I'm getting an Error 404 page on the server side, but everything is find on local.

If I remove this line:

//This line is causing an error 404 in production
List<service_req> serviceRequests = serviceRequestsTask.Result; 

The 404 error then disappears and I can access the page, but I need those data.

Is it possible that System.Threading.Tasks; is missing from the azure server? Is there something wrong with this line?

Any help would be appreciated.

UPDATE 1

It turns out that the ERROR 404 was hiding another error:

Win32Exception: The remote computer refused the network connection Unknown location

SqlException: 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: 0 - The remote computer refused the network connection.) System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, object providerInfo, string newPassword, SecureString newSecurePassword, bool redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, string accessToken, bool applyTransientFaultHandling)

EntityException: The underlying provider failed on Open. System.Data.Entity.Core.EntityClient.EntityConnection.Open()

It seems to be linked with Entity Framework and the connection string. I passed hours on this error and I can't figure out why it does not work on server side (which is an Azure Web App).

Here is my connection string, which include MultipleActiveResultSets set to true.

Data Source="DATABASENAME, PORT";Initial Catalog=legroupetihd;Integrated Security=False;User Id=ID;Password=PASSWORD;MultipleActiveResultSets=True;

And this local database is configured to allow remote server connections:

Answer 1

This error usually means that the SQL Server computer can't be found or that the TCP port number is either not known, or is not the correct port number, or is blocked by a firewall.

You coul refer to this steps to check your sql server connection.

1.Gathering Information about the Instance of SQL Server.
2.Enable Protocols.
3.Testing TCP/IP Connectivity.
4.Testing a Local Connection.
5.Opening a Port in the Firewall.
6.Testing the Connection

For more details, you could refer to this article to troubleshoot connecting to the sql server database engine.

Also, someone maunally open the connection from the object context as below and fixed error, you could try to.

using (DatabaseEntities context = new DatabaseEntities())
{
    context.Connection.Open();
    // the rest
}