Establish SQL Server connection encryption for Sitecore 10.4.1 successful connectivity

Sitecore 10.4.1 released last month. Prior to XM Cloud, one of the age-old practices of setting up a local Sitecore instance is by running the Sitecore Install Assistant(SIA). Good that Sitecore still supports running latest releases locally (apart from the Dockerised approach) in spite of maintaining XM Cloud with latest features/updates/hotfixes and is also a compelling reason to move your projects to the SaaS-based XM Cloud setup.  The SIA tool also has a Quick Install Guide for XP Developer Workstation with hardware/software details apart from other pre-requisites. In those lines, Sitecore 10.4.1 supports SQL Server 2022 and 2019. One of the most important pre-requisites is to enable sql server ssl for secure connection between Sitecore instance and SQL Server. Although the Quick Install Guide for XP Developer Workstation has a workaround to bypass this secured connection in case of local setup, in this blog note, I’m going to cover the cleaner approach of setting up an ssl certificate locally, then attach that certificate to the sql server instance. Moreover, you always want a freshly installed instance to work as-is, without tampering the Sitecore connection strings and so on. Although the Quick Install Guide for XP Developer Workstation has a link to sql server documentation about setting up ssl connection to SQL server, I’m just putting pictures to words here since it is easy to relate once you visually see things that too in the Sitecore installation context. 

Overview of the secured communication process components:

Before we go too far, let’s first replicate the issue that is to be fixed. 

OS: Windows 11 Pro

First of all, install SQL Server 2022 developer edition from SQL Server downloads.Then, install SQL Server Management Studio 2021 and enable mixed mode authentication for SQL Server since Sitecore depends on SQL Server authentication mode. 

Also, ensure SQL Server sa user is enabled after a fresh SQL Server installation.

Now, once the Sitecore installation is successful, when you login to the Sitecore instance, the login will fail. As stated in this Konabos blog post, there isn't any error in the logs except this network connectivity error :

So, this being the context, let’s setup the much necessary transport-level secure connection using the below PS Script since encrypted communication between client and server is a prerequisite for  Sitecore 10.4.1 installation.

In other words, here are the basic steps to be accomplished with the PS Script:

1. Create a personal certificate and export the certificate to trusted public store using the below PS script.

Note that the most important parameter is the dns name and this must be the SQL server machine name or a fully qualified domain name depending on what is used to connect to the instance.

2. Next grant permission to SQL server instance' NT service account

In other words, someone holding this certificate is authorised to connect to the SQL Server instance

3. Finally, via the SQL Server configuration manager, bind the certificate to the SQL Server instance 

Steps 1 and 2 are accomplished by the below script while step 3 is done manually:

#######

#######

End-result:

Certificate in Personal Store with proper ACL:

Certificate exported to Trusted root:

Manually bind the certificate to SQL Server instance:

Note that this is done in the SQL Server 2022 configuration manager:

Click OK button above and restart the service. Also note that the logon as column value is what is granted access in step 1 above. In a named instance, this value will change so, script must be adjusted based on the value.

Since all the above ssl setup is done in the same machine as the Sitecore instance, all instances will now connect to the SQL Server instance in a secure manner.

Prior to testing your secured connectivity using Sitecore, you could use SSMS 2021 to test the connection by explicitly specifying the domain name and clicking connect button. If this works, you are one step closer to the end-goal. If not, you can check the SQL Server logs to get better understanding of the problem.

Now, once an iisreset is done, login to the Sitecore instance and you should be able to get the much-needed breakthrough of viewing the launch pad that too in a secured manner!

The main advantage of this approach is, when you move to higher environments, you now know how certificates work and you can use the same approach with slight deviation depending on changes to fully qualified domain name to get/bind the certificate and establish the Sitecore setup/connection.

Also note that in case of your old Sitecore instances (prior to 10.4.1), in the same machine, they will still continue working fine as before. Only difference is, they aren’t bothered if connectivity to sql server is secured!

A  couple of observations, 

1. Since your dns name will be the machine name or a fully qualified domain name, while connecting to SQL Server via SIA, ensure to give the machine name rather than (local) for server name since (local) isn't treated as secure or the same name as the actual machine name. Hence, Sitecore login will still fail

2. If you use SQL Server 2017 configuration manager or in other words, SQL Server version prior to SQL Server 2019, in the configuration manager, the certificates won't be populated for selection although certificate is created correctly in the stores.

Demo:

P.S.

The following script is tested to automate all 3 steps of creating cert, assign to cert stores and bind to SQL instance with the end-result of secured SQL Server connection but SQL config manager wouldn't show the cert assignment/binding for the instance although the cert will appear in the list - tested on Win 11 Pro with SQL Server 2022 Developer edition - successfully running this script with proper params has resulted in subsequent Sitecore login success:

##############

##############