Connection String Issue: System.Data.SqlClient.SqlException (0x80131904) - Unable to connect to SQL Server Management Studio

Question

I am trying to connect Visual Studio 2017 to SQL Server 2016 Express using the following code in my app.config:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <configSections>
<!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
<section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
  </configSections>
  <startup>
<supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
  </startup>
  <entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />
<providers>
  <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
</providers>
  </entityFramework>

  <connectionStrings>
<add name="BlogDbContext" connectionString="Data Source=DESKTOP-I3K90LQ\SQL2016;Initial Catalog=CodeFirstDemo;Integrated Security=SSPI;" providerName="System.Data.SqlClient"/>
  </connectionStrings>
 </configuration>

I created a sample repository to test in my program.cs file

using System;
using System.Collections.Generic;
using System.Data.Entity;
sing System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ConsoleApp1
{
public class Post
{
    public int Id { get; set; }
    public DateTime DatePublished { get; set; }
    public DateTime DateEdited { get; set; }
    public string Title { get; set; }
    public string Body { get; set; }
}

public class BolgDbContext : DbContext
{
    public DbSet<Post> Posts { get; set; }
}

class Program
{
    static void Main(string[] args)
    {
    }
}
}

I installed Entity Framework into my Console Application using the following command in the Package Manager Console

PM>install-package EntityFramework -version 6.1.3

Then I enable Migration and create the first migration:

PM>enable-migrations
PM>add-migrations CreatePost
PM>update-database

after submitting update-database command I get following error message:

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: 26 - Error Locating Server/Instance Specified)

ClientConnectionId:00000000-0000-0000-0000-000000000000
Error Number:-1,State:0,Class:20
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)

So my Visual Studio is not able to connect to my SQL Server Management Studio to create a database.

If this work right, my Code First console project should be able to use a Sql instance instead of LocalDb instance to create and update a database from Visual Studio. ### I have searched google for solutions for hours last night. Some suggest that could firewall issues, that I should create a port 1433 and enable TCP/IP in the Sql Server Configuration Manager to allow VS connecting to SQL. I have all those solutions, but none of them worked for my test project.

If anyone out there have an answer, please help.

Many thanks!

Further technical details

Target Framework: .NET Framework 4.5 Operating system: IDE: (e.g. Visual Studio 2017 15.4)

Answer 1

I found solution as Following:

<connectionStrings>
<add name="BlogDbContext" connectionString="Data Source=DESKTOP-I3K90LQ\SQL2016;Initial Catalog=CodeFirstDemo;Integrated Security=SSPI;" providerName="System.Data.SqlClient"/>
</connectionStrings>
<entityFramework>
<defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework">
<parameters>
<parameter value="Data Source=localhost; Integrated Security=True; MultipleActiveResultSets=True"/>
</parameters>
</defaultConnectionFactory>

<providers>
<provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
</providers>
</entityFramework>