SQL Server connection errors when moving .net app to new server

Question

I have a number of .NET web apps on a Server 2008 machine that I'm trying to migrate to a Server 2019 machine, and some of them are giving me problems connecting to SQL Server 2016 instance on another server after moving them.

The error I get is

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: 25 - Connection string is not valid) ---> System.ComponentModel.Win32Exception (87): The parameter is incorrect

This one is a .NET Core 2.2 web app. (.net core 2.2 server package installed on server) the connection string is like "Server=mysqlserver\myinstance,3050;Database=Idea;Trusted_Connection=True;" and is using Entity Framework/DbContext to connect.

The app in IIS has an app pool created for this app, set up the same as it was running on the old server: No Managed Code, Identity set as a domain user "domain\user".

Ping from new app server to database server works. Running SSMS as the domain user on the new server connects to the database fine and can view data through management studio.

So I don't know if there is something different in Server 2019 about the way it's trying to connect to SQL Server or what? I've been banging my head on this for a few days now. .NET 4.0 apps are also having the same errors trying to connect to other databases on this same instance.

The weird thing, is some other apps work fine, connecting to a different SQL 2016 instance on a different server, but they are also different .net versions, like older .NET 4.0 web apps, but they are running as app pools with different domain accounts for each app fine.

It seems all .NET Core or .net 4.0 web apps on this server are having trouble connecting to this one database instance from this server, but back on the old web app server they work fine.

Any ideas of anything else I can check?

Edit: I found the error is actually when connecting to a new SQL Server, even from the old app server it still gets the same error. I have 2 connections in this application, so I thought it was the first one, but it was actually the 2nd one. So it's something more to do with the new SQL server instance. Again, connections from SSMS work fine with this user, but not from the web app.

Edit2: After more testing, it's definitely something to do if there is Server 2019 in the mix. From 2019 app server to 2019 db server fails. From 2008 app server to 2019 db server fails. From 2019 app server to 2016 db server fails. From 2008 app server to 2016 db server succeeds.

Edit 3: I feel like I'm going crazy here. One of the apps, I tried to change ASPNETCORE_ENVIRONMENT to Development so that I could see more detailed errors on screen, so I set the appsettings.Development.json to the exact same as the appsettings.Production.json and then the connection works! Switching it back to production it gets the error again. The entire file is the exact same text. How does that even make any sense? I even tried explicitly setting the environment variable to Production instead of letting it just pick it up as the default.

Edit 4: I've solved half of the problems I've been having now. For whatever reason, the connection string that I copied from one of the spreadsheets in the beginning had crazy hidden characters in it so that’s why it was saying the sql server didn’t exist. I can’t see them at all in any editor and only found it by VS Code compare saying the line was different but not seeing any difference I broke it into chunks and found the spot. We found when we opened it in WordPad, that was the only place that would show it, see below.

Answer 1

I'm fairly sure the solution is to remove the instance name from the connection string...

"Server=mysqlserver,3050;Database=Idea;Trusted_Connection=True;"

Refer to these questions question1 & question2

"It's not necessary to specify an instance name when specifying the port."

Answer 2

Problem was due to hidden characters in the connection string. See my Edit 4. Other problems leftover were unrelated.

Answer 3

Try to create an user in the database using the same AppPool name that you used to configure your application in IIS.

Here's a "how to" create the user in the database: https://engram404.net/grant-sql-permissions-to-iis-apppool-user/

It worked for me.

Otherwise here's an "why" it happends: You're telling in your connectionString that it will be using an trusted connection "Trusted_Connection=True;"

If you do not want to create the user as described earlier, you should remove the Trusted_Connection=True; and use your connection string like this:

Server=mysqlserver\myinstance,3050;Database=Idea;User Id=SetYourUser;Password=SetYourPassword"

Answer 4

I know you happened to solve this, but still..

You can't connect to a MySQL database with System.Data.SqlClient - It is configured for an SQL database, not MySQL

You can find the MySQL Data Connector Here. (You can otherwise download it from Nuget)

If you downloaded it from Nuget, then skip this step.

---

After downloading the package, you can add it as a reference in your project, by right-clicking the References item in the Solution Explorer, then click Add Reference... - The Reference Manager window will then open. Click the Browse item in the left-menu, then click the Browse button, and navigate to the directory of which the package was saved.

---

Now, after successfully downloading and installing the package, add this line to your code:

using MySql.Data.MySqlClient;

The correct syntax for connecting to your MySQL Database, using MySql.Data.MySqlClient;, would be:

string connectionInfo = @"Server=localhost;Database=your_database;User ID=root;Password=123456";

So, overall, your code would look like:

    using(MySqlConnection con = new MySqlConnection(connectionInfo))
    {
        con.Open();
        MessageBox.Show("Successful Connection!");
    }

(Code / part of answer is derived from here.)

I hope I could help anyone else with this problem :)