SqlConnection Fails to Open on .NET Core 3.1 but Succeeds on 4.6.1 Framework

Question

I created two sample C# Console apps, one running on .NET Framework 4.6.1 and the other running .NET Core 3.1. BOTH apps are identical with the following code for Program.cs:

using System;
using Microsoft.Data.SqlClient;

namespace DBConnectTest
{
    class Program
    {
        static void Main(string[] args)
        {
            string connStr;
            Console.WriteLine("Type Connection String: ");
            connStr = Console.ReadLine();

            using (SqlConnection connection = new SqlConnection(connStr))
            {
                SqlCommand sqlCommand = new SqlCommand("SELECT 'Hello' UNION SELECT 'World';", connection);
                sqlCommand.Connection.Open();
                sqlCommand.ExecuteNonQuery();
            }

            Console.WriteLine("Succeeded! Done.");
            Console.Read();
        }
    }
}

NOTE 1: I am using nuget package Microsoft.Data.SqlClient on both projects for consistency.

For input, I would copy/paste a Connection String. Sample:

Data Source=UATSOMEENV01;Initial Catalog=SomeDB_Name;Persist Security Info=True;Integrated Security=SSPI;App=SampleApp

Note 2: The SQL Server is NOT local to the server running the Apps.

Here's where it gets interesting. I run the .NET Framework 4.6.1 App executable and it connects to the DB just fine. However, when I run the .NET Core 3.1 App executable, it fails to open a connection to the DB. Here's the error message:

Core-Error-JPEG

I have read many other questions on the internet regarding issues with connecting to the DB, but it always seems to be some issue with getting the Connection String just right. In this case, I know it's right because it works on 4.6.1 (unless .NET Core expects it differently).

Other note-worthy items:

As an alternative, I have generated a Connection String from a .udl file that connects successfully. However, that also does not work when running the .NET Core 3.1 executable.
DB Server referenced in Connection String is an MSSQL server on an AON cluster.
Using Sql Server Authentication (sa account) instead of Windows Authentication on Connection String outputs the same behavior.
Running the apps from the local DB server works on BOTH apps. ie: Using the following Connection String works when the executables are ran locally from the DB server:

Data Source=localhost;Initial Catalog=SomeDB_Name;Persist Security Info=True;Integrated Security=SSPI;App=SampleApp

Any help with this would be greatly appreciated. Thanks in advance!

Not sure if it's the case here, but apparently .NET Core doesn't support aliases in the connection string: https://stackoverflow.com/q/45327293/5803406 — devNull, Oct 04 '20 at 04:26

score 2 · Accepted Answer · answered Nov 05 '20 at 22:39

After some investigation and reading, I found I had to add a port number to the connection string in order to get this to work. From before, which did not work:

Data Source=UATSOMEENV01;Initial Catalog=SomeDB_Name;Persist Security Info=True;Integrated Security=SSPI;App=SampleApp

Working Connection String:

Data Source=UATSOMEENV01,6000;Initial Catalog=SomeDB_Name;Persist Security Info=True;Integrated Security=SSPI;App=SampleApp

If you're unsure which port number to use, try 6000 or 1433 - these seem to be commonly used ones if not set otherwise. If neither of those work, try the following steps to verify which port number your Database is using:

Open SQL Server Configuration Manager (on SQL Server where the Database lives on).
Left pane, expand SQL Server Network Configuration.
Select Protocols for MSSQLSERVER. Your SQL Server name might be different.
Right pane, right-click on TCP/IP and select Properties.
Go to the IP Addresses tab and scroll to the bottom.
Under IPAll, the port number you're looking for is the value for TCP Port.

Some useful links for further reading:

Unable to create connectionstring for a remote desktop for a C# application

.NET Core can´t connect to SQL DB

score 0 · Answer 2 · answered Oct 04 '20 at 05:00

0

Check Compatibility of your sql client version with .NET 3.1 There must be issue there. You will need to get older sql connection client in order to match your .NET core 3.1

answered Oct 04 '20 at 05:00

Black_Fogg

38
6

SqlConnection Fails to Open on .NET Core 3.1 but Succeeds on 4.6.1 Framework

2 Answers2