Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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

like image 249
Brent Kilboy Avatar asked Jan 21 '20 20:01

Brent Kilboy


1 Answers

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

like image 68
Brent Kilboy Avatar answered Oct 12 '22 23:10

Brent Kilboy