1. Prepare for deploying

Configure encrypted communication with SQL Server

Version: 10.5

New versions related applications may require encrypted communication with SQL Server, for example Sitecore Identity Server module version 8.0 and later, and Sitecore Publishing Service version 8.0 and later. This change is due to the adoption of a newer version of Microsoft.Data.SqlClient that enforces increased security, as described in Microsoft’s Breaking changes in EF Core 7.0 article.

To configure encrypted communication with SQL Server:

  1. Pull the latest version of the mssql-developer image by running command:

    docker pull scr.sitecore.com/sxp/nonproduction/mssql-developer:2022-<windows version>

  2. Install the SitecoreDockerTools module of version 10.4.4 or newer:

    Install-Module -Name SitecoreDockerTools -MinimumVersion 10.4.4 -Repository SitecoreGallery -Force
Import-Module -Name SitecoreDockerTools -MinimumVersion 10.4.4 -Force

  3. Ensure the folder that stores certificates for Traefik is empty, otherwise the certificates will not be re-created:

    compose\<windows version>\<topology>\traefik\certs

  4. Run the compose-init.ps1 initialization script to generate certificates (described in the following section). You should see two new certificates were created, which are required to configure trusted encrypted communication between the application and SQL Server:

    • compose\<windows version>\<topology>\mssql\certs\mssql.pfx - contains private key and public certificate which will be used by Sql Server for encryption.
    • compose\<windows version>\<topology>\id\certs\RootCA.crt - public certificate of the certification authority which issued this and other certificates for the Sitecore deployment.

    You can provide your own certificates; however, they must meet certain requirements to be used with SQL Server. For more details, refer to the Certificate requirements for SQL Server article.

  5. If any Sitecore application requires an encrypted communication with SQL Server, provide the RootCA.crt file to it also. Details about the configuration are provided in the application's documentation.

Alternative Configuration: SQL Server-Generated Self-Signed Certificates

To simplify the installation process for a non-production deployment, you can skip configuring an encryption certificate for SQL Server. If the certificate is not explicitly configured, SQL Server automatically generates a self-signed certificate (fallback certificate) at startup and uses it for encryption.

To accommodate this configuration in a Sitecore application, update connection strings of Sitecore databases that the application is referencing in one of the following ways:

1. Disable encryption: Update the connection strings to use parameter:

Encrypt=false;

2. Enable encryption but disable the client's validation of certificate (trust the server's certificate). Update connection strings to use parameters:

Encrypt=true;TrustServerCertificate=true;

For example, for Identity Server 8.0 and above, modify the following line of the identity service in the docker-compose.yml file:

from

Sitecore_Sitecore__IdentityServer__SitecoreMemberShipOptions__ConnectionString: Data Source=${SQL_SERVER};Initial Catalog=${SQL_DATABASE_PREFIX}.Core;User ID=${SQL_SA_LOGIN};Password=${SQL_SA_PASSWORD};Encrypt=true;TrustServerCertificate=false;

to

Sitecore_Sitecore__IdentityServer__SitecoreMemberShipOptions__ConnectionString: Data Source=${SQL_SERVER};Initial Catalog=${SQL_DATABASE_PREFIX}.Core;User ID=${SQL_SA_LOGIN};Password=${SQL_SA_PASSWORD};Encrypt=false;
If you have suggestions for improving this article, let us know!