Encrypting Sitecore connection strings for Sitecore Commerce, Azure SQL, and beyond

There’s been a lot of Sitecore Commerce on my plate this Summer, and sadly one limitation of using that product for some customers is the requirement for SQL Server authentication instead of Active Directory and Windows Auth; I won’t get into why they need SQL auth at this point, but trust that in many use-cases this is a necessity.

In an effort to always deliver a secured platform for customers, at Rackspace we encrypt the App_Config/connectionStrings.config file to avoid having plaintext usernames and passwords on disk.    This is a link to our Rackspace GitHub “gist” performing such encryption with the ASP.Net tool aspnet_regiis.exe.  The logic is also there to un-encrypt, in case that’s necessary.

You can update the $configLocation variable at the top of the script to point to where your Sitecore installation is located; you then run this script using PowerShell, and you’ll get an output like this.

Once you’ve run the script, your connectionStrings.config file will resemble this:

Before you get too excited, for Sitecore Commerce in the current incarnation, there are several other plaintext passwords on disk in the \CommerceAuthoring\wwwroot\data\Environments and related .json files for both SQL and Sitecore.  The PowerShell I’ve shared doesn’t address those areas.  The Sitecore Commerce documentation does a good job of cataloging where to find these references, at least, but this still leaves a lot to be desired in terms of security.

I’m not going to go too far down this path, since I mostly wanted to post the PowerShell we use to automate SQL Server connection string encryption.  This technique can be useful for a variety of projects, not just for Sitecore Commerce — although this is the use case we’re repeatedly seeing right now.  If I have time, I’ll share some other Sitecore Commerce tips around Azure SQL friendly deployments (Sitecore’s documentation is a decent start, but lacking in some respects).

Here’s the script to encrypt/decrypt those Sitecore connectionStrings.config file:

	<#
	Note:
	– The encyption is specific to each server, so this needs to be run separately on every IIS server
	– ASPNet_RegIIS requires a web.config file to operate, so we have to massage our Sitecore .config into a web.config format it will understand
	Steps:
	1) Copy current Connectionstrings.config to a file named "web.config"
	2) insert <configuration> node surrounding the <connectionStrings> XML
	3) run this new web.config file through aspNet_RegIIS…
	C:\Windows\Microsoft.NET\Framework\v4.0.30319\aspnet_regiis.exe -pef "connectionStrings" "S:\Sitecore\TEST-CMS\website\App_Config"
	4) take the contents of the — now encrypted — web.config file and pull the information within the
	<connectionStrings>…</connectionStrings> nodes and overwrite what's currently in connectionStrings.config
	#>
	$configLocation = "S:\Sitecore\website\App_Config"
	#this is here only in case you want a back-up, but don't blindly leave a back-up around or it defeats the purpose of encrypting
	#Copy-Item -Path ($configLocation + "\connectionStrings.config") -Destination ($configLocation + "\connectionStrings.PlainText.backup")
	Copy-Item -Path ($configLocation + "\connectionStrings.config") -Destination ($configLocation + "\web.config")
	$plainConnectionStrings = Get-Content ($configLocation + "\web.config")
	$plainConnectionStrings.replace('</connectionStrings>', '</connectionStrings></configuration>') | Set-Content ($configLocation + "\web.config")
	$plainConnectionStrings = Get-Content ($configLocation + "\web.config")
	$plainConnectionStrings.replace('<connectionStrings>', '<configuration><connectionStrings>') | Set-Content ($configLocation + "\web.config")
	#Encrypt
	C:\Windows\Microsoft.NET\Framework\v4.0.30319\aspnet_regiis.exe -pef "connectionStrings" $configLocation
	$encryptedString = Get-Content ($configLocation + "\web.config")
	$encryptedString.replace('</connectionStrings></configuration>', '</connectionStrings>') | Set-Content ($configLocation + "\web.config")
	$encryptedString = Get-Content ($configLocation + "\web.config")
	$encryptedString.replace('<configuration><connectionStrings', '<connectionStrings') | Set-Content ($configLocation + "\web.config")
	#this is now our XML to inject into the Sitecore connectionStrings.config
	$encryptedString = Get-Content ($configLocation + "\web.config")
	Clear-Content -Path ($configLocation + "\connectionStrings.config")
	Set-Content -Path ($configLocation + "\connectionStrings.config") -Value $encryptedString
	Remove-Item ($configLocation + "\web.config")
	Write-Host "$configLocation\webconnectionStrings.config is now encrypted" -ForegroundColor Magenta
	########################################################################
	# to un-encrypt, run the following from the machine that performed the encryption. ConnectionStrings will be revealed in plain text in a new web.config file
	<#
	$configLocation = "S:\Sitecore\website\App_Config"
	Copy-Item -Path ($configLocation + "\connectionStrings.config") -Destination ($configLocation + "\web.config")
	$plainConnectionStrings = Get-Content ($configLocation + "\web.config")
	$plainConnectionStrings.replace('</connectionStrings>', '</connectionStrings></configuration>') | Set-Content ($configLocation + "\web.config")
	$plainConnectionStrings = Get-Content ($configLocation + "\web.config")
	$plainConnectionStrings.replace('<connectionStrings', '<configuration><connectionStrings') | Set-Content ($configLocation + "\web.config")
	C:\Windows\Microsoft.NET\Framework\v4.0.30319\aspnet_regiis.exe -pdf "connectionStrings" $configLocation
	Write-Host "Check $configLocation\web.config for the plain text configuration" -ForegroundColor Magenta
	#>

view raw

SecureSitecoreConnectionStrings.ps1

hosted with ❤ by GitHub