Share via

Unable to connect App Service(.net core application) to SQL Server database

Aleksandar Peychev 1 Reputation point
2020-08-31T19:56:16.817+00:00

I have configured an App Service(hosted on Linux) which is unable to connect to an SQL Server database.

Log error: 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)

The SQL Server database firewall contains a rule which includes the app service IP. I have successfully connected to the DB server from my machine.
The App Service contains a connection string setting added in the Configuration -> App settings following the naming conventions. Using the /api/settings on the scm route I found that the app setting for the connection string is present in the application.
I tried using the __ naming convention for the App Settings but there was not effect.

The .net core application is retrieving the db connection string from an appsettings.json file.

Azure App Service
Azure App Service
Azure App Service is a service used to create and deploy scalable, mission-critical web apps.
8,964 questions
SQL Server | Other
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator
    2020-08-31T21:35:43.677+00:00

    I can only comment this from the SQL Server side of things. The error message means that you are not making contact. This could be because the instance is not running, does not accept remote connections or there is a firewall in the way. It could also be that the server or instance name is wrong, or in case of a named instance that the browser service is not running or inaccessible due to a firewall.

    It appears that you seem uncertain how to configure the connection settings for AppService (with which I am completely unfamiliar with). To make the equation easier, try to connect to SQL Server from something else on the same machine, preferably a tool like SQLCMD. If you can get that connection to work, the problem has to be in the AppService configuration.

    1 person found this answer helpful.
    0 comments
  2. CathyJi-MSFT 22,396 Reputation points Microsoft External Staff
    2020-09-01T02:31:54.787+00:00

    Hi @Aleksandar Peychev ,

    I can only help you troubleshot on SQL server side, please try to follow below steps to troubleshot this issue,

    1. Make sure SQL Server Service is running
    2. If a named instance, make sure SQL Server browser service is running. Make sure the instance name is spelled correct.
    3. Make sure SQL Server is configured to allow remote connections
    4. Examine the SQL Server error log for messages confirming that SQL is listening on the expected network interfaces and ports
    5. Test server connectivity with PING from the client machine
    6. Test port connectivity using TELNET to the server and port (from step 4) from the client machine. For example
      TELNET <server-name> 1433
    7. Check firewall settings if step 5 or 6 connectivity test fails

    If you have some confuse about the steps, please refer to Resolving could not open a connection to SQL Server errors or Troubleshoot connecting to the SQL Server Database Engine to get more information.

    As Erland mentioned, you can try to using other tools(such as SSMS ) on the same machine to connect to the SQL server instance. If it works well, the problem has to be in the AppService configuration（But i do not know more about that).

    Best regards,
    Cathy

    1 person found this answer helpful.

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.