2

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
Janneck Lange
  • 882
  • 2
  • 11
  • 34
  • Use SQL Server Management Studio and try to connect to Server. The server/instance on SSMS login window should match connection string. You have Integrated Security=True which means you are using a window credential of the current user. The user group that the database security settings needs to be setup to allow the user to connect. Usually you set the database to a windows user group and then have all users put into the group. The group has to be setup on both local and remote machines (or in a corporate network use Group Policy). – jdweng Aug 15 '19 at 13:04
  • I can accesss the local db with Integrated Security=True and False. Only the remote DB has a network-related or instance-specific error – Janneck Lange Aug 15 '19 at 13:10
  • Did you copy the Connectionstring from the old asp.net Version or did you wrote it down again? – Brezelmann Aug 15 '19 at 13:15
  • I always try to create and test a basic ODBC connection when I have these problems. It will highlight whether it's a connection string or environment issue. I fully agree with Shekar's answer below too. – Fleshy Aug 15 '19 at 13:20
  • @Brezelmann I checked it again with the old connectionString - Same Error – Janneck Lange Aug 15 '19 at 13:21
  • 1
    I hope I am not understanding the first comment correctly. Assuming he is talking about different application users, i disagree with adding windows application users into a single group. I have sql instances with many databases and the applications only need access to their respective database. There is a concept of least privileges that I follow to prevent unnecessary access. The login you use will need access to the resources that it requires and nothing else. I would not put users for different applications in the same group and grant blanket permissions. – Kevin Aug 15 '19 at 13:21
  • 1
    "Network related instance" errors would not be generated by authentication issues...the comments regarding integrated security are red-herrings – Fleshy Aug 15 '19 at 13:22
  • @JanneckLange are both asp applications running on the same server/machine and are there no different firewalls in between? – Brezelmann Aug 15 '19 at 13:23
  • @Brezelmann Yes, running on same PC connecting to the same server. – Janneck Lange Aug 15 '19 at 13:25
  • Are you only able to connect to the remote instances locally on each one. What edition are they using? If they are sql express, check the tcp port in configuration manager. I agree with testing the connection. Do the instances have anything logged in the sql error log? – Kevin Aug 15 '19 at 13:32
  • If it cannot find the instances from the location you are trying, you can also try a simple ping to see if you can even reach the remote instances. – Kevin Aug 15 '19 at 13:35
  • Then use Windows Credential. The user (or group) need to be on both local and remote machines. – jdweng Aug 15 '19 at 13:56

3 Answers3

2

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
Shekar Kola
  • 1,287
  • 9
  • 15
  • Don´t have the configuration manager, but I am sure thats not the error. I can access the remote DB with other applications without "1433" in the connection string – Janneck Lange Aug 15 '19 at 13:29
  • Is other application (which was working) on same sub-net/VLAN?, i still suspect SQL Protocols or network related blocking issue as the error clearly indicates (1st error in the question). I agree, it would work without `1433`, but `SQL Browser service` must be running where SQL Server is installed. – Shekar Kola Aug 15 '19 at 17:06
  • Adding the port number to the server name resolved my problem. It wasted my 5 hours :(. For e.g. "Server=SERVER01\\SQLSERVER,1433;Database=DBName;User Id=ABC; Password=IXYZ;MultipleActiveResultSets=True;". To configure the port follow https://stackoverflow.com/a/57448109/9522887 – Kishan Vaishnav Oct 17 '19 at 12:36
  • 1
    @KishanVaishnav, i'm glad it's helped. However, while using port in connection string, there is no need of INSTANCE NAME i.e. `SERVER01,1433` not `SERVER01\\SQLSERVER,1433` – Shekar Kola Oct 17 '19 at 13:10
1

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

Janneck Lange
  • 882
  • 2
  • 11
  • 34
poda
  • 11
  • 2
0

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.

Janneck Lange
  • 882
  • 2
  • 11
  • 34
  • As mentioned in my answer, either you must ensure SQL Browser service running and UDP:1434 enabled at firewall. or use the custom port for the named instance – Shekar Kola Oct 17 '19 at 13:11