Description

On-Prem deployment of Sitecore Experience Platform instance might fail when installing it using Sitecore Installation Framework PowerShell module on a machine with 22.0.xx version of SqlServer. The errors similar to the following can be found in all tasks of the "InvokeSqlcmd" type:

Install-SitecoreConfiguration : 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.)

The root cause of the issue is that a connection to the SqlServer 22.0.xx is established without the "TrustServerCertificate" parameter.

Solution

As a solution, add the "TrustServerCertificate" parameter to the "Invoke-Sqlcmd" cmdlets for *.json templates. For example:

"CreateShardApplicationDatabaseServerLoginInvokeSqlCmd": { 
            "Description": "Create Collection Shard Database Server Login.", 
            "Type": "InvokeSqlcmd", 
            "Params": { 
                "ServerInstance": "[parameter('SqlServer')]", 
                "Credential": "[variable('Sql.Credential')]", 
                "InputFile": "[variable('Sharding.SqlCmd.Path.CreateShardApplicationDatabaseServerLogin')]", 
     "TrustServerCertificate": true, 
             "Variable": [ 
                    "[concat('UserName=',variable('SqlCollection.User'))]", 
                    "[concat('Password=',variable('SqlCollection.Password'))]" 
                ] 
            }, 
            "Skip": "[or(parameter('SkipDatabaseInstallation'),parameter('Update'))]" 
        }

Notes:

• In 22.0.59 version, this parameter was added as mandatory.
• For more detailed information on parameters of the "Invoke-Sqlcmd" function, refer to the following the link:
  https://learn.microsoft.com/en-us/powershell/module/sqlserver/invoke-sqlcmd?view=sqlserver-ps