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

Question

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

Opened this issue 6 months ago · 3 comments

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

Answer 1 · 2024-04-16T11:09:59.000Z

As workaround I did following:

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

Answer 2 · 2024-04-16T22:06:17.000Z

and it only fails on BC24?

Answer 3 · 2024-04-23T05:07:37.000Z

@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.