CODEWITHSUNDEEP
c#sql-server.net-core
Janneck Lange
Janneck Lange

Reputation: 932

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

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

Upvotes: 2

Views: 11553

Answers (3)

poda
poda

Reputation: 11

In json file, put double \\ like "DefaultConnection": "Data Source=db-name\\test2019; ...

Upvotes: 1

Janneck Lange
Janneck Lange

Reputation: 932

The Problem here is the named instance of the server "Server=[servername]\[instanz]". I can connect to a SQL-Server without a instance.

I did not found a solution for Servers with named instances yet.

Upvotes: 0

Shekar Kola
Shekar Kola

Reputation: 1297

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

  1. If doesn't work after firewall entry: Restart SQL Browser service
  2. No Luck with SQL Browser service, change connection string as follows:
Server=HC-SERVER-04,1433;Database=IBCore_Lange;User Id=sa;password=XXX

Upvotes: 2

Related Questions