Below errors may be seen at a primary site server with the remote SQL when trying to run site reset and move DB to new server. These could be possibly seen during other standard setup or recovery
ConfimgrSetup.log
04-13-2021 18:39:07.193 Configuration Manager Setup 5876 (0x16f4) *** [08001][-2146893019][Microsoft][SQL Server Native Client 11.0]SSL Provider: The certificate chain was issued by an authority that is not trusted.~~
04-13-2021 18:39:07.193 Configuration Manager Setup 5476 (0x18f4) *** [08001][-2146893019][Microsoft][SQL Server Native Client 11.0]Client unable to establish connection
04-13-2021 18:39:07.193 Configuration Manager Setup 5476 (0x18f4) *** Failed to connect to the SQL Server, connection type: SMS ACCESS.
04-13-2021 18:39:07.193 Configuration Manager Setup 5476 (0x18f4) Failed to get sql connection
04-13-2021 18:39:07.193 Configuration Manager Setup 5476 (0x18f4) Failed to get site exchange certificate 87d20002
04-13-2021 18:39:07.193 Configuration Manager Setup 5476 (0x18f4) Failed to decrypt data using format 0.
04-13-2021 18:39:07.193 Configuration Manager Setup 5476 (0x18f4) ERROR: Failed to decrypt SQL Server machine serialized pfx certificate (LastError=0)
04-13-2021 18:39:07.193 Configuration Manager Setup 5476 (0x18f4) ERROR: Failed to create SQL Always On certificate.
To fix the above issue import all DB certificate to trusted root certificate of primary site server -Run site reset again.
Exporting the SQL certificate from SQL server
Launch SQL Server configuration manager.
Expand the SQL Server configuration manager node and from here open the properties for the SQL instance we are targeting.
On the protocols for the Server name dialog, select the certificate tab to reveal the certificates which SQL is utilizing and able to see certificates.
Need to manually import and export into MECM server .
.On the Welcome page of the wizard which appears, click Next
.On the Export Private Key page, select to export the Private Key, click Next
.On the Export File Format page, then click Next
.Select Personal Information Exchange,
.Select Include All Certificates in the chain
.Select Export all Extended properties
.On the Password page, set a simple password to protect this file – for example “SQL Cert”
.On the File to Export Page, provide a path and name – for example “c:SQLcert.pfx”
.On the completion page click Finish.
Import the SQL Certificate to our SCCM Server
Back on the SCCM Server, we now will open the Computers Certificates store and import this certificate into the trusted roots.
•Launch MMC and add the Certificates Snap In, select the focus as Local Computer
•Navigate to the Trusted Root Certificate Authorities, Certificates store
•Right click, and select All Tasks, Import…
•The Import page of the wizard will appear with its normal welcome, click Next
•On the file to import page, click Browse
•Set the File type to PFX
•Navigate to the server location you saved the export a moment ago – for example \SQLCert.pfx
•Click Ok to select the certificate
•On the password page, enter the password you used for the export, and click Next
•On the Certificate Store, the currently selected store will be offered, which should be “Trusted Root Certificate Authorities”
•Click Next to complete and finish out the wizard