.NET Core WebAPI app connects locally but fails on the Server with SQL Network Interfaces error: 26 Error Locating Server/Instance Specified – Plesk

I’m developing a new app and giving .NET Core a go.
All is fine until I deploy the app to servers (for staging/testing).

I wrote some custom code where I try/catch and return from WebAPI core endpoints so I am able to use Postman and see the errors there. The error is:

“A network-related or instance-specific error occurred while establishing a connection to SQL Server. … (provider: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified)”.

I also apologize in advance because the error message I am getting is extremely common, but I did try numerous solutions to no avail.

This is day 9 of research and I can’t wait anymore. Asking for help here is the last recourse and If this doesn’t get solved in a day, I’ll move to .NET Framework. So, here are some scenarios that I have:

MACHINE1: DEV machine 1, Visual Studio 2017, SSMS 2016
MACHINE2: DEV machine 2, Visual Studio 2017, SSMS 2008 R2
DB_SERVER1: SQL server 2016 GoDaddy Plesk
SERVER2: GoDaddy Plesk Shared Server Hosting
SERVER3: AWS LightSail Instance Windows Server 2016, SSMS 2016 Installed

The error ONLY happens when SERVER2 or SERVER3 try to connect with DB_SERVER 1.

.NET Core itself seems to work because the endpoint is returning custom code that blows up only at the point of connecting with the database.
So, in the beginning I was having some road bumps and wondering about whether .NET Core was installed properly, or there was a versioning issue, etc.

I have completely turned off firewalls;
I have given the application pool administrator privileges
Tried using a port number in the connection string.

I can connect with DB_SERVER1 via SSMS, sqlcmd from MACHINE1, MACHINE2, and SERVER3 (didn’t try SERVER2 because that’s Plesk so I figured it’d be easier to try things out in a private box)

The code also works connecting to DB_SERVER1 from MACHINE1 Visual Studio, MACHINE1 local IIS.

Interesting side points:

When publishing the app to the server via FTP/IIS, the connection string is ALWAYS the same no matter if I change the connection string in the appsettings.json. It uses my dev machine1’s connection string. Even if I completely remove it from anywhere in the project, no matter what, that same original connection string is the one that shows up in the deployed appsettings.json. Then later, I always have to manually change the connection strings on the server to try things out, which doesn’t matter anyways because the result is always the same as if the connection string never changed. The resulting error message sure comes from as if the connection string never changed.

Is there any sort of obscure caching going on here? From IIS, AWS, .net Core?

There’s nothing wrong with the DB_SERVER1 as far as firewall, ports, etc because as just described I can connect to it via multiple avenues, tools, and my dotnet core code itself.

What else am I missing?

I’ve installed dotnet core runtimes on SERVER3.
So, the app runs, it bombs on the connection with the database.
It does use Entity Framework and I generate the models database first using dotnet commands.
So, would any missing EF component cause error 26?

Ok, I’ll stop here and cross my fingers for any ideas that will lead to finding that missing semicolon.

I’ve tried numerous solutions, here are three as examples of very close problems to what I have.

sql network interfaces error 26 – error locating server/instance specified

.Net Core 2.0 with EF7 connecting to SQL Server Express. Error: 26 – Error Locating Server/Instance

.NET Core can't connect to remote SQL Server database

and many many more already tried.

In appsettings.json

"DefaultConnection": "Server=xxx.xx.xxx.x;Database=dbname;Trusted_Connection=False;MultipleActiveResultSets=True;User ID=username;Password=password"

In startup.cs

            services.AddDbContext<ApplicationDbContext>(
                option => option.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));

            services.AddIdentity<IdentityUser, IdentityRole>(
                option =>
                {
                    option.Password.RequireDigit = false;
                    option.Password.RequiredLength = 6;
                    option.Password.RequireNonAlphanumeric = false;
                    option.Password.RequireUppercase = false;
                    option.Password.RequireLowercase = false;
                }).AddEntityFrameworkStores<ApplicationDbContext>()
                .AddDefaultTokenProviders();