Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
789 views
in Technique[技术] by (71.8m points)

c# - .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
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...