The Sitecore installation process has come a long way since the introduction of SIF. It has been flawless under countless runs, but I was recently tripped up due to a SQL connection error. In this post I'll explain the cause, resolution, and some steps to make your  next try of the installation easier. 

Getting Ready for Installation

You're probably thinking, was it something I didn't do right? Well, if you have the right version of SQL installed per the Sitecore compatibility table, you're good. The SOLR versions are located on a separate page at the SOLR compatibility table, which also needs to be set up (See my post on installing SOLR the easy way). If you're using an environment that can make use of checkpoints, I'd recommend making one before you start.

The SQL Error

The error I received this time around was, “The certificate received from the remote server was issued by an untrusted certificate authority”. Looking into the cause, it turns out that this error occurs when you try to make an encrypted connection to SQL Server using a non-verifiable certificate. I've never seen this before, but the workaround is going to be an easy one.

Working Around This Issue

We can ignore the cause for this issue by setting SQL connections to trust the server certificate, which is done in the package's JSON file(s). In this example I'm installing using Sitecore 10.3.0 rev. 008463 (Setup XP0 Developer Workstation rev. 1.5.0-r11). In that package there's a xconnect-xp0.json file. Obviously, based on what you're installing you'll need to check for other files to modify. 

The following is the area that must be updated. Note the following line, which I've added:

"TrustServerCertificate": true,

Here's the full portion of code:

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

Before You Try Reinstalling

One thing that has not improved with Sitecore installation is the retry cleanup. The following is a list of what must be done before you can run the installer again, unless you used a checkpoint like I mentioned earlier:

• Uninstall Sitecore certificates for SOLR and Sitecore.
• Remove the SOLR service and stop it (solr-8.11.2).
• Remove the SOLR directory at C:\Solr.
• Remove the new sites and application pools in IIS.
• Delete SQL databases.
• Delete the sites in your wwwroot folder.

That's it! After saving your JSON file and cleaning the above items you should be good to re-run your install. Good luck!