Cannot get SQL Server 2022 to work with SSL certificate

Question

I just upgraded my C# project from .NET 6 to .NET 8. I started getting

Microsoft.Data.SqlClient.SqlException (0x80131904): A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The target principal name is incorrect.)

So digging through StackOverflow and lots of googling led me to upgrading SQL Server from 2017 to 2022 and upgrading Windows Server from 2012 to 2022. This was because I had no luck installing my wildcard SSL cert on SQL 2017. Nor would the self-signed cert work. That one gave me

A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.) I have tried using the wildcard cert, which it accepted, but got the same errors above. I tried changing the server name via:

SELECT @@SERVERNAME
EXEC sp_dropserver 'SERVER';
GO
EXEC sp_addserver 'FQDN SERVER NAME', local;
GO

(Removed server names for privacy. e.g. server.domain.com) Then restarted the server. Same errors. Since my valid SSL wildcard cert wasn't working, even when adding the server name to the username, I tried reissuing the self cert via PowerShell:

New-SelfSignedCertificate -Type SSLServerAuthentication -Subject "CN=FQDN Server" -FriendlyName "SQL Server Test self-signed" -DnsName "FQDN Server",'localhost.'  -KeyAlgorithm RSA -KeyLength 2048 -Hash 'SHA256' -TextExtension '2.5.29.37={text}1.3.6.1.5.5.7.3.1' -NotAfter (Get-Date).AddMonths(24) -KeySpec KeyExchange -Provider "Microsoft RSA SChannel Cryptographic Provider"-CertStoreLocation "Cert:\LocalMachine\My"

Reset the cert in SQL Manager and restarted the server. Same errors.

Finally, I broke down and bought a new SSL cert just for this server using MMC to create the request then installed it as above. Restarted the service. Same errors.

I've tried multiple connection strings from Trusted_Connection=True; to variations of Encrypt=false; TrustServerCertificate=true; changing those to true and false in all combinations. I've changed the server names back and forth and run through all the connection string combinations again and got the same errors.

I tried changing the properties for SQL Server Network Configuration forcing encryption on and off. My other apps connect just fine with it off, but nothing connects with it on. I expect this since there seems to be a problem with how it interacts with the SSL cert.

Looking at the certs in MMC, all say they are fine. All have the private key.

I have no idea what else to try, so I come to you all as a last hope. Can someone please tell me what I am missing? It shouldn't be this hard.

UPDATE: I wrote a test routine within the app to connect using the same connections string as in the appsettings.json file. It connects to the database and grabs a record from it. No errors! Now I am completely confused! How can this code work within the same project that says it fails to connect, using the same connection string?

 private async static Task<bool> TestSQL()
    {
        var connectionString = "Server=server.domain.com;Database=myDb;Persist Security Info=True;User ID=MyUser;Password=MyPass;MultipleActiveResultSets=true;Encrypt=true;TrustServerCertificate=true;";
        using (var connection = new SqlConnection(connectionString))
        {
            try
            {
                await connection.OpenAsync();
                Console.WriteLine("Connected successfully!");

                // Retrieve one record from the "Customers" table
                var sqlQuery = "SELECT TOP 1 * FROM Customers";
                using (var command = new SqlCommand(sqlQuery, connection))
                {
                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        if (await reader.ReadAsync())
                        {
                            // Assuming "Customers" table has a column called "CustName"
                            var custName = reader["CustName"].ToString();
                            Console.WriteLine($"Customer Name: {custName}");
                        }
                        else
                        {
                            Console.WriteLine("No records found in the 'Customers' table.");
                        }
                    }
                }

                return true;
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error connecting to SQL server: " + ex.Message);
                return false;
            }
        }
    }

Is this a bug with Entity Framework? .NET?

Answer 1

First, thank you to everyone who made comments above. They were the actual solution. (Using the FQDN in the connection string instead of just the server name as it had been previously.)

The problem that I was facing and why the connection string worked in the test stub but not in the code was because someone had changed one of the connection strings in the appsettings.Development.json that overrode my changes to the main file. It just happened to be the one connection I was testing. I found that out after changing the hard-coded connection string to:

var connectionString = ctx.Database.GetConnectionString();

in order to find out exactly what the context thought it was connecting to.