Unable to connect to SQL Server 2016 LocalDB

Question

I have setup a local Sql Server set up and it's running. I can also connect to it and run queries from within Visual Studio. However, when I make a POSTMAN request to a .net core API which uses this database (both the database and api is on localhost of the same machine) I get the error:

Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware: An unhandled exception has occurred while executing the request.

System.Data.SqlClient.SqlException (0x80131904): 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: 50 - Local Database Runtime error occurred. Specified LocalDB instance name is invalid.

However, if I try sqlcmd -s "(localdb)\MSSQLLocalDB", it is working and I'm able to connect.

A bit more information on the set up is below:

Result of the command sqllocaldb i MSSQLLocalDB is

Name:               mssqllocaldb
Version:            13.1.4001.0
Shared name:
Owner:              MyDomain\My.User
Auto-create:        Yes
State:              Running
Last start time:    9/3/2018 xx:xx:xx xx
Instance pipe name: np:\\.\pipe\LOCALDB#2690D11D\tsql\query

Connection String:

{
  "ConnectionStrings": {
    "ModelA": "server=(localdb)\\MSSQLLocalDB;Initial Catalog=ModelA.Test.DB;Trusted_Connection=True;",
    "ModelB": "server=(localdb)\\MSSQLLocalDB;Initial Catalog=ModelB.Test.DB;Trusted_Connection=True;"
  }
}

StartUp.cs Config services:

services.AddDbContext<ModelAContext>(options => options.UseSqlServer(this.Configuration.GetConnectionString("ModelA")));
services.AddDbContext<ModelBContext>(options => options.UseSqlServer(this.Configuration.GetConnectionString("ModelB"), b => b.MigrationsAssembly("Migrations.Project")));

Answer 1

Make sure your appsettings.json is the only config that has ConnectionStrings section as appsettings.Development.json can be overriding it. For the sake of testing I'd try to hardcode it and you can also try a different connection string format like:

services.AddDbContext<ModelAContext>(options => options.UseSqlServer("Server=(localdb)\\MSSQLLocalDB;Integrated Security=True;Database=ModelA.Test.DB"));

---

Edit: If you want to be able to use environment based connection strings you should always have ever only development connection string in either appsettings.json or appsettings.Development.json so your production credentials don't leak through source control. You can simplify it by naming it after environment like

"ConnectionStrings": {
    "Development": <--your connection string for localhost development-->
},

Now if you want to add production connection string for remote database, right click your ASP.NET Core Website project in Visual Studio > Manage User Secrets. This will generate and open secrets.json file in %APPDATA%\Microsoft\UserSecrets\. There insert your "Production" connection string.

{
    "ConnectionStrings": {
        "Production": <!--your connection string for remote server-->
    }
}

Then you can load proper connection string by the environment variable.

configuration.GetConnectionString(Environment.GetEnvironmentVariable("ASPNETCORE_ENVIRONMENT"))

Now if you want to temporarily switch context to production database you can open Package Manager Console and type:

$Env:ASPNETCORE_ENVIRONMENT = "Production"

Now only thing left is to set connection string on remote server, because secrets.json doesn't publish and connection string is most likely different on remote server anyway. This is done again with environment variable ConnectionStrings__Production:

Environment="ASPNETCORE_ENVIRONMENT=Production" "ConnectionStrings__Production=<--your connection string from remote server to a database-->"