Error in update-database command in code first migration

Question

I am working on Desktop application in WPF and creating SqlRepository with LocalDB to store data. I am using following tools

• Visual Studio 2013 Community with update 2
• Entity Framework 6.1.2 for Code first migration

I have created local database with Microsoft SQL Server Database File (SqlClient) configuration. Database created successfully and below is connection string fetched from Sql

Data Source=(LocalDB)\v11.0;Initial Catalog=D:\USERS\USERNAME\DOCUMENTS\TestDatabase\testdb.MDF;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False

I am using same connection string in app.config. I can view database object in SQL Server Database explorer in Visual Studio. But when run update-database command in Nuget package manager console, I am getting below 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)

Bathurst

shouldn't it be Data Source=(local)\v11.0 - or - Data Source=.\v11.0 – Myosin

My one MVC project on same machine with Data Source=(localDB)\v11.0 is working. However Entity Framework 5.0 is used in that application. Here I am using 6.1.2 – Bathurst

Did you manage to solve this? Having the same issue :( – Orfield

Unfortunately not. I moved to Server based instance. – Bathurst

Answer 1

I had similar problem and fixed it when I changed the "start-up project" from another module to the module containing references to all other projects in the solution. Right-click module >> click "Set as StartUp Project"

Nicolasanicolau

I just got this in VS 2013 and I'm glad I found this answer and didn't go down the "logical" route of starting to debug my network setup with the 6/7 step guides first! I think this could well be the answer the OP was looking for; shame it's not marked as answer. – Homozygous

Why? It works but why?! Surely the "Default Project" drop down does this - wtf... sigh – Pantechnicon

This still occurs in VS2015 as well. This answer fixes it there as well. (several wasted hours later) – Funny

Copy the connection string related sections from the DB (EF) project's AppConfig to the web project's web config. This is what I had to do in my case. – Warrenwarrener

This answer absolutely confirmed that I'm Alice in Microsoft's Magic land!! – Glynda

Answer 2

Diego's answer is correct.

This problem occurs when there is no connection string in project marked as startup project. Then EF tries to connect to some default database engine to perform update. In my case it tried to use express, and for some reason it couldn't connect. And the error was thrown.

Run your "update-database" with option "-Verbose". One of the lines there shows which StartUp project is used. Check your connection string in this project, or change the startup project to the one that has correct connection string. That solves the problem.

Voyageur

Although I had set the connection string in the appsettings.json I was still getting the error. Using "-Verbose" will enable you to find which server and which database connection it is using to setup the connection. Only then I realized that there where many other .yml and .json files which had also stored the connection string(probably through caching mechanism). I found and replaced all the connections strings to get the things running – Threephase

Answer 3

As explained in other answers, the problem usually comes from having the wrong Initial project in Package Manager Console. In my case the console was ignoring the value I selected in the Default Project drop down list, and also the -StartUpProjectName parameter, and reproducing the wrong behavior of trying to connect to some default database engine, as Mikk's answer describes, in my case using a SqlExpress engine.

My problem was caused by a wrong solution configuration: if your solution has several projects and is meant to be run with the configuration option "Multiple startup projects", but you just downloaded it from your source code control repository, then it is possible that the default configuration option "Single startup project" is being applied to the solution (this config value usually is not checked-in in the source code control). In this case the Package Manager Console just ignores the startup project selected in its combo and just applies the default startup project in the solution, which may not have a connection string, as specified in Mikk's answer.

So I fixed it by changing the solution properties: Common properties / Startup project / Select Multiple startup projects instead of Single startup project, and after that the Package Manager Console would accept the Project name and update the right database.

Charissecharita

I tried so many different solutions that didn't work, but yours did. Thank you! – Careful

Answer 4

I was able to solve this by adding parameters to the update-database command:

update-database -StartupProjectName MyApp.Web -ConnectionStringName MyAppConnectStringInWebConfig

Cassicassia

Answer 5

I solved this error changing the initial project to the Entity Project.

Look:

Update-Database -Verbose Using StartUp project 'SCVE.Web'. <-- this ir error Using NuGet project 'SCVE.EntityFramework'.

Ranking

Answer 6

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)

This error message informs you that it is not possible to connect to SQL Server. The possible reasons and their elimination is described below:

1) SQL Server is not started. Starting of it will allow you to see your SQL Server/instance in the drop-down list of available SQL Servers.

• Go to the Start menu -> Control Panel -> Administration Tools -> Services.
• In the list of services find SQL Server (instance name, by default it is EZPARTS5) and check its status, it must be Started
  (if it is not started, then right click on SQL Server and select
  Start from the context menu).

2) Firewall is blocking port 1433 (MSSQL standard port for connections). It can be disabled following the steps below:

• Go to the Start menu -> Control Panel -> Administration Tools -> Services.
• Find Firewall service, it must be disabled (if it is not, then right click the service and select Stop from the context menu).

Note: More information on this can be found on the official Microsoft site: Link

3) TCP/IP protocol is disabled for MSSQL protocols. To enable it, see the steps below:

• Navigate to SQL Server Configuration Manager in the Start menu.
• Specify settings for TCP/IP protocol in SQL Server Configuration Manager.
• Restart the computer.

Note: More information on this can be found on the official Microsoft site: Link

4) Make sure your database engine is configured to accept remote connections (If you are using centralized database):

• Open SQL Server Management Studio.
• Right click SQL Server instance -> Properties -> Connections -> Check the Allow remote connections to this server box.
• Go to the General section and check name of SQL Server specified in the Name field.

5) If you are using a named SQL Server instance, make sure you are using that instance name in your connection strings. Usually the format needed to specify the database server is machinename\instancename.

6) Make sure your login account has access permission on the database you used during login. Alternative: If you still can’t get any connection, you may want to create a SQL account on the server, a corresponding SQL user on the database in question, and just use this username/password login data to connect to SQL Server.

Referred from this link

Unshaped

I am not connecting to any remote server and it is localDB a mdf file created on local machine. I can see instance in SQL Server Object Explorer and can create table/proc in Visual studio. It means it is connect. Problem occurs when I try to run update-database command for code first migration. Any idea what could be wrong? – Bathurst

Answer 7

Try with this Connectionstring:

<connectionStrings>
  <add name="DefaultConnection" connectionString="Data Source=(LocalDb)\v11.0;AttachDbFilename=D:\USERS\USERNAME\DOCUMENTS\TestDatabase\testdb.MDF;Initial Catalog=testdb;Integrated Security=True" providerName="System.Data.SqlClient" />
</connectionStrings>

Inkhorn

Answer 8

If you are trying to connect to Sql Server instead of localdb then make sure the default connection factory in web.config file is as below:

<defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework">
  <parameters>
    <parameter value="Data Source=.\SQLEXPRESS2012; Integrated Security=True; MultipleActiveResultSets=True" />
  </parameters>
 </defaultConnectionFactory>

Groot

Answer 9

I had this same problem with a freshly created ASP.NET project.

First I tried to set the startup project as mentioned above. There was no startup project selected and I was unable to select one.

Ultimately I updated my Visual Studio from 2015 Update 1 to 2015 Update 3 and

update-database

executed without problems.

Malleolus

Answer 10

PM>Update-Database

error and solution which I did today in 4 hours. I am very much new in .net coding, but yes in PHP I have 10+ years experience. It took me 4 hours to solve this. 1.My first Mistake: I was not having Microsoft SQL Server 2019 in my laptop locally I was having this through AZURE which is not works for localhost, so I installed this locally after download, it took around 2 Hours. 2.Second changed mistake=

 "DevConnection": "Server=localhost;Database=PaymentDetailDB;Trusted_Connection=True;MultipleActiveResultSets=True;"

then it works for me

Recti

Sorry for your time, but your assumption that migrations only work on local host is not correct. They can even made to work on Azure (https://mcmap.net/q/581661/-usage-of-update-database-in-ef-migrations-when-deployed-in-azure). Apart from that, if you were right you'd need a lot of space to tell that you installed Sql Server and connected to it. Also, I think the existing answers already point out the essential caveats multiple times. – Launch