Windows 11 Pro - Sitecore 10.1.x Installation Error Resolution - "The certificate chain was issued by an authority that is not trusted."

Note: I was on May Windows Update when this issue occurred and in the June 13 Windows update both "TrustServerCertificate": true and "Encrypt": "Optional" produce the same effect in resolving the issue but the former seems more secure and latter is more of a workaround. So, use "Encrypt": "Optional" only as a workaround if "TrustServerCertificate": true doesn't work. Also, this change is relevant for all topologies involving xconnect json and the location of change is the 4 occurrences discussed in the blog article here. I also have created a restore point now in my machine so that I can revert a windows update in case of issues. In case of higher environments, better turn-off windows updates.

--------------------------------------------------------------------------------------

The issue/solution here is applicable for all Sitecore 10.x installations including the latest version, 10.3.

Although Sitecore is moving towards Composable DXP, I have this weakness for  Sitecore installation issues. As I was perusing through the Sitecore Slack Channel, I had to setup a Sitecore 10.1.0 instance for one of the issues.  I decided to setup the instance on a Windows 11 Pro brand new machine. I then realized that Sitecore 10.1.0 fails on Windows 11 Pro just with the GUI installation:

Installation log:

[ XConnectXP0_CreateShardApplication... : InvokeSqlcmd ------------------------]

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

[TIME] 00:04:17

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

Some google search, sent me to this StackExchange link. So, this seemed a known issue that needed a concrete solution. 

This was enough for me to get interested. Based on the error information, I could find that the issue was happening in xconnect-xp0.json - InvokeSqlCmd.

I added the "TrustServerCertificate": true to all the 4 occurrences of InvokeSqlcmd in the json file, 

The error still occurred at the same step. 

The irony was, I was able to connect to the local sql server via SSMS irrespective of if the Encrypt Connection or Trust Server Certificate options were selected or not:

Since now my prime suspect was the Encrypt Connection option, I made it Optional as follows in the json file and that did the trick for me:

"Encrypt": "Optional"

When I triggered the SIF install script now, the installation was successful. Since this is a local instance, I'm not too worried about making the flag optional at this point rather than see Sitecore 10.1.0 installation break in my local Windows 11 Pro.

SIA also happy now since the change was to xconnect-xp0.json used by both SIA and SIF installations:

Remember, a small bullet is what makes a big difference so, don't hold yourself back at any point ;)

Post Update: I have now updated Sumit Helaiya's Sitecore StackExchange answer since it is close enough!

Additional Error:

Failed to start service 'Sitecore Marketing Automation Engine

Resolution:

If happening while using Sitecore Install Assistant, ensure to run Install Assistant as Administrator

------------------------------------------------------------------------------------------

Even in case of Sitecore 9.3 on Windows 11, once Encrypt is set to optional in xConnect-xp0.json, the installation was successful. Then, the site wasn't coming up. Only when Disable TLS 1.3 over TCP was checked, the site appeared (ensure to check for xConnect and identity server instance too):