Unable to connect to named instance of SQL Server from .Net Core application

[saklis]

Describe the bug

Open() method in SqlConnection throws SqlException when connecting to named instance of SQL Server. This only happen when the client is a .Net Core app. It connects correctly for .Net Framework application.

Exception 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)"

Stack trace:
"   at Microsoft.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, Boolean applyTransientFaultHandling, String accessToken)
   at Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   at Microsoft.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
   at Microsoft.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at Microsoft.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
   at Microsoft.Data.SqlClient.SqlConnection.Open()
   at SqlClientStandard.DataLayerClass.Run() in C:\Visual Studio 2017\Projects\NewSqlClientCacheEngine\SqlClientStandard\DataLayerClass.cs:line 12
   at TestCoreConsoleApp1.Program.Main(String[] args) in C:\Visual Studio 2017\Projects\NewSqlClientCacheEngine\TestCoreConsoleApp1\Program.cs:line 7"

Common data layer project (.NET Standard DLL) have just this one class:

using Microsoft.Data.SqlClient;

public class DataLayerClass {
    private SqlConnection conn = new SqlConnection(new SqlConnectionStringBuilder {
        DataSource         = "hostName\\instanceName",
        InitialCatalog     = "dbName",
        IntegratedSecurity = true
    }.ToString());

    public void Run() {
        conn.Open();
    }

    public void Close() {
        conn.Close();
    }
}

Both client projects (.NET Framework and .NET Core console applications) look like this:

using Microsoft.Data.SqlClient;

internal class Program {
    private static void Main(string[] args) {
        DataLayerClass clss = new DataLayerClass();
        clss.Run();
        clss.Close();
    }
}

To reproduce

1. Create a .Net Standard DLL project which is your common data access layer.
2. Add reference to Microsoft.Data.SqlClient and add code to create SqlConnection object and calling Open() method, connecting to named instance of SQL Server.
3. Create a .Net Framework Console project.
4. Add reference to .Net Standard project and execute the code calling Open() method - connection should be established correctly.
5. Create a .Net Core Console project.
6. Add reference to .Net Standard project and execute the code calling Open() method - connection will fail with Exception.

Expected behavior

Consistent behavior between .Net Framework and .Net Core clients. Preferably established connection in both cases.

Further technical details

Microsoft.Data.SqlClient version: 1.0.19128.1-Preview
.NET target: Standard 2.0, Framework 4.7.2 and Core 2.1
SQL Server version: SQL Server 2008 R2
Operating system: Win 10 Pro

[David-Engel]

That error typically signifies a network or configuration issue. I reproduced your scenarios and was able to connect successfully in both cases once I had correctly configured the named instance (need TCP/IP enabled, SQL Browser running, and correct firewall configuration if remote).

Microsoft.Data.SqlClient version: 1.0.19128.1-Preview
.NET target: Standard 2.0, Framework 4.6.1 and Core 2.1
SQL Server version: SQL Server 2008 R2 Service Pack 2
Operating system: Win 10 Pro

It is odd that your Framework project worked while the Core one didn't. Were they from the same machine to the same SQL Server instance?

[saklis]

Yes, I was performing both tests from same machine and to same server.
I also updated first post to include the code I'm using.

[David-Engel]

I still get a successful connection in both scenarios when I use your code against my server. Does this work if you use System.Data.SqlClient?

What version does SQL Server report? (select @@version):
Microsoft SQL Server 2008 R2 (SP2) - 10.50.4042.0 (X64) Mar 26 2015 21:18:04 Copyright (c) Microsoft Corporation Express Edition (64-bit) on Windows NT 6.2 (Build 9200: ) (Hypervisor)

[saklis]

Sorry for late reply.
Yes, using System.Data.SqlClient also result in same exception. I suspect it's connected with Core client.

My version of SQL Server is:
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) (Hypervisor)

[cheenamalhotra]

Hi @saklis

I tested connecting to Named Instance from both .Net Core 2.1 and .Net Framework 4.6.2 applications. The target SQL Server versions I have verified include SQL 2008 R2, 2012, 2017. I haven't come across any issues. I verified with both Microsoft.Data.SqlClient and System.Data.SqlClient on NetFx and NetCore.

Please recheck your application if you're using something unusual. The simplest connection test that works for me is under:

static void Main(string[] args)
{
    var server = @"localhost\MSSQLSERVER";
    using (SqlConnection con = new SqlConnection($"Server={server};UID=sa;Password=***;"))
    {
        con.Open();
        Console.WriteLine("Connected successfully.");
    }
}

[cheenamalhotra]

Closing as issue not reproducible for dev team.