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

Question

I have a simple C# project. This is my connection string in my web.config for the database:

<connectionStrings>
   <add name="DefaultConnection" 
        connectionString="Data Source=172.17.0.47;Initial Catalog=DevSystemListe;User ID=web_access;Password=123456" 
        providerName="System.Data.SqlClient" />
</connectionStrings>

I already made sure that this connection is working. I can connect to my database from the Visual Studio with this connection and I can also see the tables and data.

When I want to update my database with update-database, this error occurs:

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)

I uninstalled Entity Framework and then installed it again. Nothing changed.

Answer 1

I finally found out. I have a website project and my API in my solution. The problem was that the website was set as startup project. So that entity framework searched the connection string in the website's web.config and not in the api's web.config. Changing the startup project to my API solved the problem.

If you want to have the website project as startup project. Just copy the connection string into the websides web.config.

Nevertheless thanks for everyones help.

Answer 2

Also for migrations you can use OnConfiguring EntityFramework will check it by default. Add this code to your DbContext.

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.UseSqlServer(@"connection_string_here");
}

Answer 3

You say you can use this connection string to see data & tables... can you also use this connection to insert new records ?

For each user, SQL Server lets you define if they have read access and/or write access. Perhaps your user is just missing the db_datawriter permission...?

Answer 4

If the application is being run under full IIS, then there is some configuration for IIS needed to ensure that the localDB instance can be used in the application. See https://blogs.msdn.microsoft.com/sqlexpress/2011/12/08/using-localdb-with-full-iis-part-1-user-profile/

Answer 5

This is a late response, but I determined that the cause of this error for me was that I had tried to eliminate an extra web.config connection string without making a necessary change to my DbContext class.

I had one connection string for my DefaultConnection and another generated by EF Migrations.

<connectionStrings>
    <!-- PRODUCTION-->
    <add name="DefaultConnection" connectionString="…" providerName="System.Data.SqlClient"/>
    <add name="Clients.Link.Models.ClientsLinkDB" connectionString="…" providerName="System.Data.SqlClient"/>
</connectionStrings>

I decided to eliminate the second connection string thinking that EF would fall back or was already using my DefaultConnection.

<connectionStrings>
    <!-- PRODUCTION-->
    <add name="DefaultConnection" connectionString="…" providerName="System.Data.SqlClient"/>
</connectionStrings>

When I attempted to run update-database, I got the error message reported above:

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)

The fix turned out to be extremely simple. I simply needed to instruct EF to use my DefaultConnection connection string instead of the one generated by migrations.

To do that, you just need to create or edit the constructor of your DbContext and pass the name of the DefaultConnection as a string to the base class constructor:

public class ClientsLinkDB : DbContext
{
    public ClientsLinkDB() : base("DefaultConnection")
    {
        
    }

    // The rest of your implementation follows
}