Sitecore Experience Commerce 9 DeployCommerceDatabase Error: Unable to add user <domain>\CSFndRuntimeUser

As part of Sitecore XC9 Update 2 installation, in the DeployCommerceDatabase and AddCommerceUserToCoreDatabase tasks, there is a step to add the Commerce runtime user (which the Commerce Engine IIS application pool runs under) to the database as a db_owner. This seemed to be failing for me with the error:

Error: Unable to add user domain\CSFndRuntimeUser

First, I confirmed that the user in fact existed on the computer by running the ‘Get-LocalUser‘ command in PowerShell:

Next, I had a look at the actual script at SIF.Sitecore.Commerce.1.2.14\Modules\DeployCommerceDatabase\DeployCommerceDatabase.psm1. The relevant function is AddSqlUserToRole:

By default, the script is trying to create the login from a Windows user. Since my SQL server is on a remote Azure VM, this won’t work because the first step of the script creates a local Windows user and it can’t be used for authentication on the remote SQL Server unless I add the user to Azure AD and then use that for authentication.

The other option is to modify the script to create a SQL user instead and update all installation scripts and JSON configuration files to use SQL authentication for Commerce instead of Windows authentication. By default the setting is TrustedConnection=true which is Windows Authentication.

I went with the latter approach and updated the DeployCommerceDatabase.psm1 script to create a SQL user instead of a Windows user:

Invoke-Sqlcmd -ServerInstance $dbServer -Query "IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE name = '$($userName)') BEGIN CREATE LOGIN [$($userName)] WITH PASSWORD = '$($userPassword)', DEFAULT_DATABASE=[$($dbName)], DEFAULT_LANGUAGE=[us_english] END"

Here's the full source code for the AddSqlUserToRole function:

function AddSqlUserToRole
{
	param
	(
        [String]$dbServer=$(throw 'Parameter -dbServer is missing!'),
		[String]$dbName=$(throw 'Parameter -dbName is missing!'),
        [String]$userName=$(throw 'Parameter -userName is missing!'),
		[String]$userPassword=$(throw 'Parameter -userPassword is missing!'),
        [String]$role=$(throw 'Parameter -role is missing!')
	)
    Write-Host "Attempting to add the user $userName to database $dbName as role $role" -ForegroundColor Green -NoNewline

    try
    {
		#SQL login
		Invoke-Sqlcmd -ServerInstance $dbServer -Query "IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE name = '$($userName)') BEGIN CREATE LOGIN [$($userName)] WITH PASSWORD = '$($userPassword)', DEFAULT_DATABASE=[$($dbName)], DEFAULT_LANGUAGE=[us_english] END"
        
		Invoke-Sqlcmd -ServerInstance $dbServer -Query "IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = '$($userName)') BEGIN USE [$($dbName)] CREATE USER [$($userName)] FOR LOGIN [$($userName)] END"
        Invoke-Sqlcmd -ServerInstance $dbServer -Query "USE [$($dbName)] EXEC sp_addrolemember '$($role)', '$($userName)'"
        Write-Host "     Added" -ForegroundColor DarkGreen
    }
    catch
    {
        Write-Host ""
        Write-Host "Error: Unable to add user $userName`nDetails: $_" -ForegroundColor Red
    }
}