0

Here is the situation:

  • SQL Server database is hosted in the cloud.
  • Remote access is allowed.
  • User ID & Password are provided in the connection string in web.config file in root folder.
  • Test connection is successful.
  • The Web app has multiple folders with role based authorization. Each folder has its own web.config file for authorization.

Everything works fine when I run the app in debug mode from Visual Studio 2019.

Problem:

When I publish/host the app on shared server provided by same service provider that hosts the database, the app is unable to locate the server/database.

I have narrowed down that the block of code in web.config file responsible for authorization is causing the problem.

When authorization is removed, access to database is fine. But when I put folder level authorization, app is unable to locate the server/database.

This is the 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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

Following two methods were tried separately and are causing this problem.

Method 1:

<authorization>
      <allow roles="ADMIN"/>
      <deny users="*"/>
</authorization>

Method 2:

<location path="PagesAdmin">
    <system.web>
      <authorization>
        <allow roles="ADMIN"/>
        <deny users="*"/>
      </authorization>
    </system.web>
</location>
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
YATIN SHAH
  • 23
  • 3
  • 1
    This really makes no sense. The database connection has no relationship to the authorisation of access to folders via the webserver. – ADyson May 27 '20 at 13:28
  • I fully agree to you. But trust that I have more than 3 days to figure out that this block of code is causing the problem. – YATIN SHAH May 27 '20 at 14:09
  • Actually I thought of something...if you're adding role based restrictions using config, then this will make asp.net try to access the identity database to check the user's role. Whereas previously maybe it didn't need to do that for the folder in question. So you only see the error when that's added because previously it didn't need to read from that database at all. that would make sense as an explanation of why the error only occurs after you add the config. But the root cause is still going to be one of the same root causes it always is for that error message... – ADyson May 27 '20 at 14:35
  • ... i.e. either you wrote the connection string wrong, or the SQL server is turned off, or it doesn't allow remote access, or there's a firewall blocking the connection on the SQL port (usually 1433 unless you changed it) between the application server and the database server – ADyson May 27 '20 at 14:36

0 Answers0