SqlClient: Connection to Sql Server over network with named instance fails to connect

[Diablofan]

When attempting to connect to a Sql Server instance over the network, with named instances, SqlConnection throws error 26 on .NET Core with the SqlClient nuget library.

Sample Code

using System;
using System.Data.SqlClient;
namespace ConsoleApp1 {
    class Program {
        static void Main(string[] args) {
            var sqlString = "Data Source=192.168.0.3\\SQLEXPRESS;Initial Catalog=Database;User ID=sa;Password=<password>";
            var conn = new SqlConnection(sqlString);
            conn.Open();
            conn.Close();
            Console.WriteLine("Hello World!");
        }
    }
}

This sample code was tested working on .NET 4.5 and 4.6.1, but breaks on .NET Core 2.0, 1.1 and 1.0, along with versions 4.4, 4.3, and 4.1 of the System.Data.SqlClient library on nuget. I've been able to only reproduce this on Windows.

The server is configured for remote access as I'm able to connect to the remote instance using the same sample code full .NET Framework. The expected output should just be Hello World! and the program terminating, which happens on the full framework. However the following exception gets thrown instead on .NET Core:

System.Data.SqlClient.SqlException occurred
  HResult=0x80131904
  Message=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)
  Source=Core .Net SqlClient Data Provider
  StackTrace:
   at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, Boolean applyTransientFaultHandling)
   at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.Open()
   at TestSqlAccess.Program.Main(String[] args) in C:\src\PlaneStatus\TestSqlAccess\Program.cs:line 9

Temporary Workaround

I figured out for now was using hostname,port instead of the hostname\\instance works and connects as intended, however I'd like to use a named instance instead of having to rely on ports.

This workaround does not work for Linux, at least according to my tests, as it throws other exceptions even with this workaround, mostly being able to not connect. I haven't checked Mac since I don't have access to one.

[corivera]

@geleems Can you take a look at this?

[EgorBo]

@Diablofan is "SQL Server Browser (SQLBrowser)" windows service active?

[Diablofan]

@EgorBo Yes, it is running, thought that might have been implied in my initial post, as stated here:

This sample code was tested working on .NET 4.5 and 4.6.1, but breaks on .NET Core

But I can connect with named instances in Visual Studio, SSMS, and the sample code posted using .NET Framework 4..5 and newer.

[EgorBo]

@Diablofan well SQLBrowser should translate your instance name into a port (it's probably blocked by a firewall - UDP:1434) - it looks like an issue I faced a while ago - I could connect to SQL Server without SQLBrowser in .NET 4.5 and couldn't do it using .NET Core https://github.com/dotnet/corefx/issues/22236#issuecomment-315229159

[Diablofan]

Just asked the server admin, and SQL Server Browser is (still) running. The firewall on the server is configured to allow traffic on UDP:1434. I tested it with the firewall enabled, and then disabled, on my machine and in both cases it failed to connect.

[asfaya]

Hi, I'm having the same issue. I actually cannot connect either using "server,port" as mentioned above. Could you please post a sample conn string on how you did it so I can try the workaround? Thanks

[Diablofan]

@asfaya "Data Source=192.168.0.3,<port>;Initial Catalog=Database;User ID=sa;Password=<password>"

Where <port> is the port to connect to for the instance of SQL Server, for instance 49239. Double check with your instance of SQL Server to find the port. You should be able to paste that bit in, change the port, password and user id and it should allow you to connect

[asfaya]

Thanks! Getting the right port made it. Regards

[linusaurus]

MySql work fine, but for me the Sqlserver provide refuse to return any error of Data

NOthing-- protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) { optionsBuilder.UseSqlServer(@"Server=192.168.XX.XX;DataBase=Badger;user id=sa; password=XXXXXXx"); } Works great! protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) => optionsBuilder .UseMySql(@"Server=192.168.10.3;database=Badger;uid=root;pwd=Kx09a32x;");

[asfaya]

Is it a named instance?

[linusaurus]

I t is a named instance. This is the same con string I always use with most connection on other applications. The pomelo MySQL provider work flawlessly, but sqlserver can not connect, not error, just no date returns

[asfaya]

It's the same issue I was having. You have to change the: Server=192.168.XX.XX

with

Server=192.168.XX.XX,

You can check for the instance port name at the SQL Server Configuration manager --> Protocols --> TCP/IP --> IP Address tab --> IPAll (by default, if it was not changed)

Hope it helps. Regards

[Diablofan]

Paging @geleems, any updates on this?

[geleems]

@Diablofan can you put tcp: in front of the ip adress, and try again?

[Diablofan]

Just tried it, same error as before.

[geleems]

@Diablofan Are you getting the same error with recent version of .NET Core?

[geleems]

No response from customer. Closing this issue.

[Diablofan]

I apologize for not replying sooner. I had a busy weekend and forgot about looking into this again until I saw the email about not responding.

Anyway, just tried it on .NET Core 2.1.300 Preview and with Latest Stable (4.4.3 as of writing) and Latest Preview (4.5.0-preview1-26216-02) and both of them throw the same exception as above.

[TurinTurambar]

I'm having the same problem. I can connect to the server with everything: Management Studio, Visual Studio, dotNetFramework apps, telnet... everything works except SqlClient library in dotNetCore.

[dziedrius]

I'm also having same problem. Apps, that are running under 4.7 .net with EF6.1 have no issues connecting to same sqlexpress instance, EF Core 2.0.2 does not.

 public class DataContext : DbContext
    {
        public DbSet<TodoItem> TodoItems { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer(@"Server=.\sqlexpress;Database=TodoApp;Trusted_Connection=True;");
        }
    }

    public class TodoItem
    {
        public long Id { get; set; }
        public bool Done { get; set; }

        [StringLength(128)]
        public string Text { get; set; }
    }

Here's verbose output:

Using project 'c:\Projects\TodoApp\TodoApp\TodoApp.BusinessLogic\TodoApp.BusinessLogic.csproj'.
Using startup project 'c:\Projects\TodoApp\TodoApp\TodoApp.BusinessLogic\TodoApp.BusinessLogic.csproj'.
Writing 'c:\Projects\TodoApp\TodoApp\TodoApp.BusinessLogic\obj\TodoApp.BusinessLogic.csproj.EntityFrameworkCore.targets'...
dotnet msbuild /target:GetEFProjectMetadata /property:EFProjectMetadataFile=C:\Users\giedrius.banaitis.JUVARE\AppData\Local\Temp\tmpEC01.tmp /verbosity:quiet /nologo c:\Projects\TodoApp\TodoApp\TodoApp.BusinessLogic\TodoApp.BusinessLogic.csproj
Writing 'c:\Projects\TodoApp\TodoApp\TodoApp.BusinessLogic\obj\TodoApp.BusinessLogic.csproj.EntityFrameworkCore.targets'...
dotnet msbuild /target:GetEFProjectMetadata /property:EFProjectMetadataFile=C:\Users\giedrius.banaitis.JUVARE\AppData\Local\Temp\tmpEE05.tmp /verbosity:quiet /nologo c:\Projects\TodoApp\TodoApp\TodoApp.BusinessLogic\TodoApp.BusinessLogic.csproj
dotnet build c:\Projects\TodoApp\TodoApp\TodoApp.BusinessLogic\TodoApp.BusinessLogic.csproj /p:GenerateRuntimeConfigurationFiles=True /verbosity:quiet /nologo

Build succeeded.
    0 Warning(s)
    0 Error(s)

Time Elapsed 00:00:01.85
dotnet exec --depsfile c:\Projects\TodoApp\TodoApp\TodoApp.BusinessLogic\bin\Debug\netcoreapp2.0\TodoApp.BusinessLogic.deps.json --additionalprobingpath C:\Users\giedrius.banaitis.JUVARE\.nuget\packages --additionalprobingpath "C:\Program Files\dotnet\sdk\NuGetFallbackFolder" --runtimeconfig c:\Projects\TodoApp\TodoApp\TodoApp.BusinessLogic\bin\Debug\netcoreapp2.0\TodoApp.BusinessLogic.runtimeconfig.json "C:\Program Files\dotnet\sdk\NuGetFallbackFolder\microsoft.entityframeworkcore.tools.dotnet\2.0.2\tools\netcoreapp2.0\ef.dll" database update --assembly c:\Projects\TodoApp\TodoApp\TodoApp.BusinessLogic\bin\Debug\netcoreapp2.0\TodoApp.BusinessLogic.dll --startup-assembly c:\Projects\TodoApp\TodoApp\TodoApp.BusinessLogic\bin\Debug\netcoreapp2.0\TodoApp.BusinessLogic.dll --project-dir c:\Projects\TodoApp\TodoApp\TodoApp.BusinessLogic\ --verbose --root-namespace TodoApp.BusinessLogic
Using assembly 'TodoApp.BusinessLogic'.
Using startup assembly 'TodoApp.BusinessLogic'.
Using application base 'c:\Projects\TodoApp\TodoApp\TodoApp.BusinessLogic\bin\Debug\netcoreapp2.0'.
Using working directory 'c:\Projects\TodoApp\TodoApp\TodoApp.BusinessLogic'.
Using root namespace 'TodoApp.BusinessLogic'.
Using project directory 'c:\Projects\TodoApp\TodoApp\TodoApp.BusinessLogic\'.
Finding DbContext classes...
Finding IDesignTimeDbContextFactory implementations...
Finding application service provider...
Finding BuildWebHost method...
No entry point was found for assembly 'TodoApp.BusinessLogic'.
No application service provider was found.
Finding DbContext classes in the project...
Found DbContext 'DataContext'.
Using context 'DataContext'.
Finding design-time services for provider 'Microsoft.EntityFrameworkCore.SqlServer'...
Using design-time services from provider 'Microsoft.EntityFrameworkCore.SqlServer'.
Finding IDesignTimeServices implementations in assembly 'TodoApp.BusinessLogic'...
No design-time services were found.
Migrating using database 'TodoApp' on server '.\sqlexpress'.
Opening connection to database 'TodoApp' on server '.\sqlexpress'.
An error occurred using the connection to database 'TodoApp' on server '.\sqlexpress'.
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)
   at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, Boolean applyTransientFaultHandling)

when I've used IP with port, it worked right away:

optionsBuilder.UseSqlServer(@"Server=127.0.0.1,1433;Database=TodoApp;Trusted_Connection=True;");

[liben-y]

I had SQL Server running on windows with a linux container on the same machine trying to connect. I tried quite a lot of things suggested here and stack overflow but had no luck.

The error I was getting was a bit different to the others: Unhandled Exception: System.AggregateException: One or more errors occurred. (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: TCP Provider, error: 40 - Could not open a connection to SQL Server)) ---> 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: TCP Provider, error: 40 - Could not open a connection to SQL Server)

I could ping my machine name from a container so I knew that was not the issue.

Looking at the error more closely it looked like something to do with TCP, so I white listed TCP/UDP on ports 1433,1434. Which still didn't work.

I then checked to see if remote connections were enabled on the database and it was already enabled.

I then checked Sql Server Configuration Manager -> Sql Server Network Configuration -> Protocols for MSSQLSERVER -> TCP/IP and it was disabled. I enabled it and tried again and it worked.

So I didn't need to add the port with a comma or use ip address...

Hope this helps someone.