Resolve error "A network-related or instance-specific error occurred while establishing a connection to SQL Server"

Home > Search
  by

Ensure you can sign into SQL Server Management Studio.

Follow these steps if the following error appears when attempting to create a login system in Visual Studio: 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)

Note: This should only appear if using Window 7 or Windows Server 2008 R2 and using SQL Server Express. This workaround should not be used in a production environement, because giving the Network Service read and write access creates a vulnerability.

According to the book Beginning ASP.NET 4 by Imar Spaanjaars, the Login control talks to the ASP.NET application service providers, which is a software layer that sits between the login control and the SQL Server database. The very first time you try to log in, the application service providers check to see if your application is using a database with the necessary database objects, such as the appropriate tables and roles. If not, the application service providers should automatically create the ASPNETDB.MDF. Ensure Visual Studio is pointing to the correct instance of SQL Server.

  1. In Visual Studio, I select Tools > Options
  2. Select Database Tools > Data Connections, and enter the name of your SQL Server in the instance name file (example: server1\database1)

Follow the instructions in the article on How to get started with Microsoft SQL Server (MSSQL) in Visual Studio to ensure the Test Connection is successful when using SQL Server Authentication. Once there is a successful Test Connection, there should be a connectionString in the Web.config file.

<configuration>
  <connectionString>
    <add name="databasenameConnectionString" connectionString="Data Source=server1\database1; Initial Catalog=databasename; Persist Security Info=True; User ID=username1; Password=SuperSecretPassword" providerName="System.Data.SqlClient" />

 

Add the following markup to the web.config file in Visual Studio. Notice the name of the connectionString in this example is databasenameConnectionString. Use the same in the following markup.

<system.web>
    <authentication mode="Forms" >
    </authentication>
    <authorization>
      <deny users="?" />
    </authorization>
  <membership defaultProvider="SqlProvider" userIsOnlineTimeWindow="15">
    <providers>
      <clear />
      <add
        name="SqlProvider"
        type="System.Web.Security.SqlMembershipProvider"
        connectionStringName="databasenameConnectionString"
        applicationName="MyApplication"
        enablePasswordRetrieval="false"
        enablePasswordReset="true"
        requiresQuestionAndAnswer="true"
        requiresUniqueEmail="true"
        passwordFormat="Hashed" />
    </providers>
  </membership>
</system.web>
  1. Save any changes made in Visual Studio, and close Visual Studio
  2. In IIS, expand the root folder and select Application Pools
  3. If using ASP.NET 2.0, 3.0 or 3.5, right-click DefaultAppPool and select Advanced Settings. If using ASP.NET 4.0, right-click ASP.NET v4.0 and select Advanced Settings.
  4. In the Advanced Settings dialog box, under Process Model, highlight the Identity row and select the small icon with the three dots.
  5. In the Application Pool Identity dialog box bullet Built-in Account and select Network Service.
  6. Select OK
  7. Select OK

In a command line prompt, type net stop iisadmin /y or iisreset /stop

 

Follow these steps to give the network service read access to Visual Studio.

  1. In Windows Explorer, right-click on the users Visual Studio folder and select Properites (example: C:\Users\user1\Documents\Visual Studio 2015)
  2. In the Properties dialog box, select the Security tab
  3. Select Edit
  4. Select NETWORK SERVICE, ensure Read is checked, and select Apply, OK.

Note: If NETWORK SERVICE is not listed, select Add. In the Select Users or Groups dialog box, type NETWORK SERVICE, select Check Names, and select OK.

  1. Ensure the Read permission is checked, select Apply, and then select OK
  2. Select OK

 

Follow these steps to give the network service read and write access your Visual Studio project.

  1. In Windows Explorer, right-click on the root folder of the website and select Properites (example: C:\Users\user1\Documents\Visual Studio 2015\Projects\MyWebsite)
  2. In the Properties dialog box, select the Security tab
  3. Select Edit
  4. Select NETWORK SERVICE, ensure Read and Write are checked, and select Apply, OK.
  5. Select OK

 

Follow these steps to give the network service read and write access your App_Data folder.

  1. In Windows Explorer, right-click on the App_Data folder of the website and select Properites (example: C:\Users\user1\Documents\Visual Studio 2015\Projects\MyWebsite\MyWebsite\App_Data)
  2. In the Properties dialog box, select the Security tab
  3. Select Edit
  4. Select NETWORK SERVICE, ensure Read and Write are checked, and select Apply, OK.
  5. Select OK

In a command line prompt, type net start iisadmin /y or iisreset /start

More information here: https://support.microsoft.com/en-us/kb/2002980



Add a Comment




We will never share your name or email with anyone. Enter your email if you would like to be notified when we respond to your comment.




Please enter in the box below so that we can be sure you are a human.




Comments