MECM - Failed to connect to the SQL Server

[CHiLL]

I was looking at upgrading our MECM site server from 2303 to 2309 and it's getting stuck in a checking prerequisites loop without proceeding and not timing out (unless I use CMUpdateReset.exe). Looking at the C:\ConfigMgrPrereq.log file, I'm seeing a lot of issues with the SQL server, specifically:

*** [08001][-2146893022][Microsoft][ODBC Driver 18 for SQL Server]SSL Provider: The target principal name is incorrect.
*** [08001][-2146893022][Microsoft][ODBC Driver 18 for SQL Server]Client unable to establish connection
*** Failed to connect to the SQL Server, connection type: SMS ACCESS.
ERROR: Failed to connect to SQL Server to check application catalog roles

I've also noticed weird behaviour when accessing the database via SQL Server Management Studio 19.2. If I have the FQDN, I can connect fine. If I have only the NetBIOS name, I have to have "Trust server certificate" checked in the Connection Properties, otherwise it throws the following error:

Cannot connect to .
Additional information:
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.) (Microsoft SQL Server)
Target principal name is incorrect

Microsoft ODBC Driver 18 for SQL Server is already installed (manually), which is required for this update to installed. It isn't installed as part of the upgrade and without it, the prerequisites check will fail.

I have checked the SPN and it appears to be correct (both NetBIOS and FQDN entries for the site server (also SQL server) on port 1433 with the domain service account for SQL.

I have also checked the site's server certificate, which was issued in 2015 when the server was commissioned and has an expiry of 2115.

 

While our MECM appears to be working correctly at the moment, with deploying OS and updates, adding and deploying apps, etc...I wonder if this could be a sign of more significant issues to come.

Edited November 30, 2023 by CHiLL

[CHiLL]

For those that encounter this, you need changed "Force Encryption" from "On" to "Off". To do this:

 

1) Log onto the SQL server that hosts your MECM database

2) Open MMC and add the snap-in "SQL Server Configuration Manager"

3) Expand "SQL Server Configuration Manager (Local)"

4) Expand "SQL Server Network Configuration"

5) Right click "Protocols for MSSQLServer" and select "Properties"

6) In the "Flags" tab, change "Force Encryption" to "No"

7) Press "Apply" then "OK"

8) Restart the SQL service (SQL Server (MSSQLSERVER)), either in services.msc or via "SQL Server Services" in SQL Server Configuration Manager

 

Note: I had to keep "Force encryption" set to "No" even after the update completed. I had set it back to "On" and restarted the SQL service, but I found that after the admin console updated on the server, it couldn't connect to the site with the error "Configuration Manager cannot connect to the site ". Turning Force Encryption back to off and restarting the SQL service again allowed me to connect the admin console immediately.