c# - .NET Core can't connect to remote SQL Server database

Question

I have a .NET Core application as API. I can connect it to a local SQL Server database, but not to other databases on my network.

When I try to access a remote database using a dbContext, I get this error:

System.Data.SqlClient.SqlException:
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)

appsettings.json for local:

  "ConnectionStrings": {
    "DefaultConnection": "Data Source=(localdb)\mssqllocaldb;Initial Catalog=ib;Integrated Security=True;"
  }

appsettings.json for remote:

  "ConnectionStrings": {
    "DefaultConnection": "Server=[servername][instanz];Database=IBCore_Lange;User Id=sa;password=XXX"
  }

I can access the database via SQL Server Management Studio, or a previous (ASP.NET) version of my system. In Core, I can only access to a local database.

Startup.cs

public void ConfigureServices(IServiceCollection services)
{
    services.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version_2_2)
            .AddJsonOptions(x => x.SerializerSettings.ReferenceLoopHandling = Newtonsoft.Json.ReferenceLoopHandling.Ignore);

    services.AddDbContext<ibContext>(options => options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));
}

The DbContext is generated:

Scaffold-DbContext "Connection String"
         Microsoft.EntityFrameworkCore.SqlServer -Verbose -o {output folder} -t {table to update} -force

Answer 1

Try following:

1. Make sure TCP/IP protocol enabled via SQL Server configuration manager. Also, ensure the custom port not configured, for detailed info
2. Do telnet HC-SERVER-04 1433 from command prompt of the remote host (from where you tried to connect SQL server)

If Step 2 fails: Create rule in Firewall of the SQL Server with port# 1433 and 1434 to accept incoming connections, then try again telnet, if telnet works, application should be able to connect

3. If doesn't work after firewall entry: Restart SQL Browser service
4. No Luck with SQL Browser service, change connection string as follows:

Server=HC-SERVER-04,1433;Database=IBCore_Lange;User Id=sa;password=XXX