Sitecore Publishing Service Connection Strings Encryption

In default Publishing Service Sitecore Host set-up, database connection strings are stored in plain text. Storing any kind of credentials in plain text is considered as security weakness defined under CWE-256: Plaintext Storage of a Password. This article guides trough how Publishing Service Sitecore Host connection strings can be encrypted and decrypted by writing PowerShell script and creating custom Sitecore Host plugin.

Encryption

In previous article - Sitecore ID Server Connection Strings Encryption - I’ve presented simple PowerShell script that can be used to encrypt any kind of string input. Encryption mechanism is based on Data Protection API and uses Windows OS Machine Key to encrypt connection string. We could use the same approach with Publishing Service Sitecore Host application.

Add-Type -AssemblyName System.Security

$ConnectionStrings = [System.Text.Encoding]::Unicode.GetBytes('....conn_string...') 
$ProtectedString = [System.Security.Cryptography.ProtectedData]::Protect($ConnectionStrings, $null, 'LocalMachine')
$Base64ProtectedString = ([Convert]::ToBase64String($ProtectedString))

Write-Output "$Base64ProtectedString";

In real world example we would encrypt connection strings as part of continuous delivery pipeline in the way that connections string are retrieved from any safe storage, then encrypted and injected into a Publishing Service Host configuration XML template. As a results we would end-up with file similar like below. Finally, the XML config file would need to be put into one of the supported Sitecore Host configuration files location, for example <root>/sitecoreruntime/Production/config or just <root>/config.

<?xml version="1.0" encoding="utf-8"?>

<Settings>
  <Publishing>
    <ConnectionStrings>
       <Core>AQAAANCMnd8BFdERjHo.............19Wk9lf74g==</Core>
       <Master>AQAAANCMnd8BFdER...........Viv5oUbIpK/kTA=</Master>
         <Web>AQAAANCMnd8BFdERjHo...........r4wQXGcsc9g==</Web>
    </ConnectionStrings>
  </Publishing>
</Settings>

Note that as per the Sitecore Publishing Service Installation and Configuration Guide, Publishing Service Host app provides the CLI to set-up connection string in the config file. Sample syntax is Sitecore.Framework.Publishing.Host --environment production configuration setconnectionstring core "value". We can’t use this command to create or update the config file as the command validates the connection string format. With encrypted Base64 string, the validation simply would fail.

Decryption

In order to implement connection string decryption, let’s understand first how they are used by the Publishing Service Host app. After scanning app related DLLs it is clearly visible that connection strings are fetched from configuration file by single SqlDatabaseConnection class. The class is declared in Sitecore.Framework.Publishing.Data DLL and registered in DI by sc.publishing.web.command.services.xml config contained in \Sitecore\Sitecore.Framework.Plugin.Publishing\Config folder.

SqlDatabaseConnection inherits a get-only ConnectionString property from abstract DatabaseConnection<TConnection> class which is defined in IDatabaseConnection interface. It is set by the base class constructor by fetching the connection string value from the app configuration.

The ConnectionString string property is then used by SqlDatabaseConnection class to create new connection in CreateNewConnection method.

It is used as well by SchemaInstaller class from the same DLL to perform health checks or execute schema modification CLI commands.

In mentioned earlier Sitecore ID Server Connection Strings Encryption article, we’ve used Sitecore Host Plugin to perform connection string decryption on app start-up and we’ve overridden settings that contained encrypted connection string value.

Looks like we could use use the same approach here, however let’s first understand at what level SqlDatabaseConnection is registered in DI container. Note that it is not registered by Sitecore Host plugin ConfigureSitecore class and ConfigureServices method as it was in case of Sitecore Identity Server. Instead, it uses XML config file based configuration inside from sc.publishing.web.command.services.xml file as mentioned earlier.

When we look into DefaultSitecoreStartup class and ConfigureServices method from Sitecore.Framework.Runtime.Web DLL, we can see that the framework loads Sitecore Host plugins service configuration as first, then goes with adding health checks related services and finally registers services from Command:Web XML.

This gives confidence that any custom plugin that we create will load before the SqlDatabaseConnection is registered.

Let’s create then a custom Sitecore Host plugin which will decrypt connection strings. In order to create Sitecore Host Plugin, create a .NET compliant class library and depending on the Publishing Service Host version used, install proper dependencies. Below ones are valid for 7.0.0 version that is based on .NET 6.

Install-Package Sitecore.Framework.Runtime -Version 7.0.0 -Source https://sitecore.myget.org/F/sc-identity/api/v3/index.json
Install-Package System.Security.Cryptography.ProtectedData -Version 8.0.0

The ConfigureSitecore class will read encrypted connection string from app settings, decrypt it using the same Data Protection API and overwrite the connection strings app settings with decrypted value as below.

using Microsoft.Extensions.Configuration;
using Sitecore.Framework.Runtime.Configuration;
using System.Security.Cryptography;
using System.Text;

namespace SitecoreGroove.Plugin.PublishingService.CredentialsEncryption
{
    public sealed class ConfigureSitecore
    {
        public ConfigureSitecore(ISitecoreConfiguration scConfig)
        {
            IEnumerable<IConfigurationSection> connectionStringSections = scConfig.GetSection("Publishing:ConnectionStrings").GetChildren();

            foreach (IConfigurationSection section in connectionStringSections.Where(x=>x.Key != "Service"))
            {
                scConfig[$"Publishing:ConnectionStrings:{section.Key}"] = Decrypt(section.Value);
            }
        }

        private static string Decrypt(string value)
        {
            byte[] encryptedConnectionString = Convert.FromBase64String(value);
            byte[] decryptedConnectionString = ProtectedData.Unprotect(encryptedConnectionString, null, DataProtectionScope.LocalMachine);

            return Encoding.Unicode.GetString(decryptedConnectionString);
        }
    }
}

Notice that we don’t want to decrypt Service connection string, because by default it uses Master connection string.

Another crucial part is Sitecore Host Plugin manifest file. Without it the Publishing Server Host would not load our custom plugin. Lets create Sitecore.Plugin.manifest in sitecore\SitecoreGroove.Plugin.PublishingService.CredentialsEncryption folder and set it’s properties:

• Build Action - Content

• Copy to Output Directory - Copy always

Inside the manifest file, we define the plugin and assembly name.

<Dependencies> defines at which point the plugin should be loaded. Since this plugin does not depend on any other plugin we can leave it empty.

Copy

<?xml version="1.0" encoding="utf-8"?>
<SitecorePlugin PluginName="SitecoreGroove.Plugin.PublishingService.CredentialsEncryption" AssemblyName="SitecoreGroove.Plugin.PublishingService.CredentialsEncryption" Version="1.0.0">
    <Dependencies />
    <Tags>
        <Sitecore>Sitecore</Sitecore>
    </Tags>
    <EnvironmentVariablePrefixes />
</SitecorePlugin>

After deploying custom plugin to the Publishing Service Host app, the plugin should load and encrypted connection string will be decrypted during app start-up.

Summary

We have made the app safer as connection string is not kept in plain text anymore. The risk is mitigated in case the files will be in some way transferred out of it’s designated area. The only one way of decryption requires direct access to the machine which is protected by authentication provided by Windows operating system.

Storing credentials in plain text CWE can be addressed in other ways as well for example by utilizing Azure Key Vault and configuring credentials-related Sitecore Host app setting trough Sitecore Host plugin. I am going to focus on that one in one of upcoming articles.

Related source code is available at SitecoreGroove.Plugin.PublishingService.CredentialsEncryption