11

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. enter image description here

Brent Kilboy
  • 403
  • 4
  • 14
  • remove the port if it's local instance connection – LinkedListT Jan 21 '20 at 20:40
  • It's not local, that is the correct port, same as it was in the connection string from the old app server. – Brent Kilboy Jan 21 '20 at 20:47
  • 2
    ok, you may have to whitelist your ip then. – LinkedListT Jan 21 '20 at 20:47
  • Try create a SQL Login and check this link https://stackoverflow.com/questions/11132003/how-to-create-a-valid-connection-string-for-entity-framework-the-underlying-pr – Dave Jan 21 '20 at 21:13
  • We are trying to avoid SQL logins and use only domain users. – Brent Kilboy Jan 21 '20 at 21:49
  • Connections from SSMS between the two servers work fine so I don't think it's an ip whitelist problem. – Brent Kilboy Jan 21 '20 at 21:50
  • Test in Powershell whether TCP is really getting through on the specified port: `Test-NetConnection mysqlserver -Port 3050`. If it is not, try adding a Firewall rule (outgoing) allowing connections to that server and port. – Ross Presser Jan 29 '20 at 00:28
  • Answering your 4th edit: Nice!! That's why .Net Core was all along telling you that the connection string wasn't valid. Sometimes text codification is a pain in the ass to debug, never had seen that problem in a Json file tho, nice catch – Cristopher Rosales Jan 30 '20 at 14:00

4 Answers4

5

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."

Cristopher Rosales
  • 476
  • 1
  • 4
  • 14
  • Hmmm bummer... I'm sorry if this seems like a dumb suggestion but for .Net Core apps, the connection string is usually set in JSON file inside the app directory might be appsettings.json or a similar JSON file, perhaps you are modifying the connection string in IIS and that won't work. – Cristopher Rosales Jan 21 '20 at 22:27
  • It is in the appsettings.json that I'm changing it. – Brent Kilboy Jan 21 '20 at 22:35
  • Hmmm you said that it gave you the same error... Are you totally sure it's the same error? The error that you posted said Connection string is not valid) ---> System.ComponentModel.Win32Exception (87): The parameter is incorrect So, in the original error, it's saying that the connection string is not valid, which might have been caused by the instance name... Perhaps now it's giving another error? – Cristopher Rosales Jan 22 '20 at 00:07
  • Exact same error with no instance name. Also, I've never had a problem using both instance name and port in any other application, it has always worked fine that way. – Brent Kilboy Jan 22 '20 at 15:07
  • Well, from the edits you made, I would make sure that the port 3050 is open... Bare in mind that the default port for SQL Server instalattion is 1433, and SMSS will try to connect to that port unless you specify otherwise... As a last idea, you could use Nmap (https://nmap.org/) to scan the ports of your Server and check if the port 3050 is open. – Cristopher Rosales Jan 22 '20 at 17:53
  • Another idea... Try escpaing the "\" in the connection string: "Server=mysqlserver\\myinstance,3050;Database=Idea;Trusted_Connection=True;" – Cristopher Rosales Jan 22 '20 at 18:01
  • Confirmed ports are open, and tried with \\, no luck. – Brent Kilboy Jan 22 '20 at 22:01
5

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

Brent Kilboy
  • 403
  • 4
  • 14
0

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"

Fernando Milanez
  • 75
  • 9
  • I tried with a user ID and pass user like you suggested but I get the same error. I think the error may be at the Server 2019 level blocking something? – Brent Kilboy Jan 28 '20 at 21:55
  • I don't think so... Did you create the user in the database following the first step that I mention? It was what worked for me. Otherwise, you may wanna try configure your environment to Development to see if there's a few more details who will show up on the screen – Fernando Milanez Jan 29 '20 at 11:09
0

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 :)

Momoro
  • 599
  • 4
  • 23