Creating muti tenant container with backup from app.DB failed on user creation

[RonKoppelaar]

To improve pipeline performance we create backups (using Backup-NavContainer) when the build DB is created in correct shape.
I encountered an issue in following scenario:

Iteration 1

• Create multi tenant Onprem container using option -UseNewDatabase
• Publish the relevant MS apps
• Export bacpac for the App.DB
• Create backup of the build DB (only using the app.bak) and store in the local cache folder: c:\bcartifacts\ónprem\version.bak

Iteration 2

• Create multi tenant Onprem container using backup from app.DB from scenario 1
• Validate if any new MS apps were used not yet part of the container
• Export bacpac for the App.DB
• Create backup of the build DB (only using the app.bak) and store in the local cache folder: c:\bcartifacts\ónprem\version.bak

Following error:

_Starting Local SQL Server
Starting Internet Information Server
Using Database .bak file 'C:\ProgramData\BcContainerHelper\Extensions\C186220-ONPREM\database.bak'
Using database server localhost\SQLEXPRESS
Setting CompatibilityLevel for tenant on localhost\SQLEXPRESS
Exporting Application to mydatabase
User, group, or role 'NT AUTHORITY\SYSTEM' already exists in the current database.
at <ScriptBlock>, C:\Run\SetupDatabase.ps1: line 87
at <ScriptBlock>, C:\Run\navstart.ps1: line 141
at <ScriptBlock>, C:\Run\start.ps1: line 390
at <ScriptBlock>, <No file>: line 1Error_

Create statement of the container:

    $DynamicParameters=@{}
    if ($CachedBackupFile -and (Test-Path $CachedBackupFile)) {
        Write-Verbose "Create container from cached backup: $CachedBackupFile" -verbose
        $UseBackupFile = $CachedBackupFile
        $UseNewDatabaseInContainer = $false
        $DynamicParameters.Add("BackupFilePath",$UseBackupFile)
    }
    else {
        Write-Verbose "Create new Onprem container" -Verbose
        $UseBackupFile = ""
        $UseNewDatabaseInContainer = $True
        $bcContainerHelperConfig.usePwshForBc24 = $False  ## Workaround for because of bug in BCHelper with BC24
    }

    New-cdsaBCContainer @DynamicParameters `
        -DockerImageName "$DockerImageName" `
        -BCArtifactURL $OnpremArtifactURL `
        -ContainerName $OnpremContainer `
        -LicenseFileName $LicenseFileName `
        -SharedFolders $SharedFolders `
        -AddInsFolder $AddInsFolder `
        -UseNewDatabase:$UseNewDatabaseInContainer `
        -Multitenant `
        -ErrorAction Stop `
        -Verbose

The above cmdlet is just a wrapper around BCContainerhelper

            New-NavContainer `
                -imageName $DockerImageName `
                -artifactUrl $BCArtifactURL `
                -containerName $ContainerName `
                -auth 'NavUserPassword' `
                -bakFile $BackupFilePath `
                -Credential $BuildCredential `
                -licenseFile $LicenseFileName `
                -memoryLimit '25G' `
                -additionalParameters $AdditionalParameters `
                -accept_eula `
                -accept_outdated `
                -updateHosts `
                -includeAL `
                -doNotExportObjectsToText:(-not $ExportObjectsToText) `
                -includeTestToolkit:$ImportTestToolkit `
                -includeTestLibrariesOnly:$ImportTestLibrariesOnly `
                -alwaysPull `
                -EnableTaskScheduler:(!$DisableTaskScheduler) `
                -multitenant:$MultiTenant `
                -useNewDatabase:$UseNewDatabase `
                -dns 8.8.8.8 `
                -copyTables $TablesToCopy `
                -doNotCheckHealth `
                -isolation process `
                -MyScripts @{"SetupAddIns.ps1" = $SetupAddinsScript }

Full log of creating the container:

Hostname is C186220-ONPREM
PublicDnsName is C186220-ONPREM
Using NavUserPassword Authentication
Starting Local SQL Server
Starting Internet Information Server
Using Database .bak file 'C:\ProgramData\BcContainerHelper\Extensions\C186220-ONPREM\database.bak'
Using database server localhost\SQLEXPRESS
Setting CompatibilityLevel for tenant on localhost\SQLEXPRESS
Exporting Application to mydatabase
User, group, or role 'NT AUTHORITY\SYSTEM' already exists in the current database.
at <ScriptBlock>, C:\Run\SetupDatabase.ps1: line 87
at <ScriptBlock>, C:\Run\navstart.ps1: line 141
at <ScriptBlock>, C:\Run\start.ps1: line 390
at <ScriptBlock>, <No file>: line 1Error
Initializing...
Setting host.containerhelper.internal to 172.29.112.1 in container hosts file
Starting Container
Hostname is C186220-ONPREM
PublicDnsName is C186220-ONPREM
Using NavUserPassword Authentication
Starting Local SQL Server
Starting Internet Information Server
Using Database .bak file 'C:\ProgramData\BcContainerHelper\Extensions\C186220-ONPREM\database.bak'
Using database server localhost\SQLEXPRESS
Setting CompatibilityLevel for tenant on localhost\SQLEXPRESS
Exporting Application to mydatabase
User, group, or role 'NT AUTHORITY\SYSTEM' already exists in the current database.
at <ScriptBlock>, C:\Run\SetupDatabase.ps1: line 87
at <ScriptBlock>, C:\Run\navstart.ps1: line 141
at <ScriptBlock>, C:\Run\start.ps1: line 390
at <ScriptBlock>, <No file>: line 1
New-NavContainer Telemetry Correlation Id: 5a4dd6a6-6cd4-465c-beb9-6a776d35dae1

[RonKoppelaar]

As workaround I did following:

• Before backup "Remove user by Invoke-Sql"
• Create backup
• Recreate user...

[freddydk]

and it only fails on BC24?

[freddydk]

@RonKoppelaar did this work in BC23?
In that case, please test this with the next BcContainerHelper preview version, which ships in a few hours.
In that - the SqlServer powershell module is again available in PS7, which might be the reason for the difference in behavior.