Upgrade PS script fails because of sql connection string in incorrect format

[hroger1030]

I am encountering an error in the upgrade.ps1 script that is used to update previously installed SaaS accelerator resources to the latest versions. I performed a vanilla install of the saas accelerator using the install.ps1 script and then I tried to run the upgrade script to apply a few cosmetic css changes to the client web application. Update script failed with:

Invoke-Sqlcmd : Login failed for user 'tcp:test1-sql.privatelink.database.windows.net'.
At C:\Users\Roger\code\saas-azure-marketplace\deployment\Upgrade.ps1:106 char:1
+ Invoke-Sqlcmd -query $compatibilityScript -ServerInstance $Server -da ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], SqlException
    + FullyQualifiedErrorId : SqlExceptionError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

I believe that the error is caused by the install script creating a connection string that uses AD creds, and the update script expecting a connection string with a user name and password. Running a test of both scripts reveals this problem.

(pulled from Azure resource group KV after creation)
Server=tcp:test1-sql.privatelink.database.windows.net;Database=test13AMPSaaSDB;TrustServerCertificate=True;Authentication=Active Directory Managed Identity;

(from update script, line 106)
Invoke-Sqlcmd -query $compatibilityScript -ServerInstance $Server -database $Database -Username $User -Password $Pass
Write-host "## Ran compatibility script against database"

To Reproduce
Steps to reproduce the behavior:

1. Run the MS provided install script. I don't believe that it matters if you run this locally or via the azure cloud shell.

wget https://dotnet.microsoft.com/download/dotnet/scripts/v1/dotnet-install.sh; chmod +x dotnet-install.sh;
./dotnet-install.sh -version 8.0.303; $ENV:PATH="$HOME/.dotnet:$ENV:PATH";
dotnet tool install --global dotnet-ef --version 8.0.6; git clone https://github.com/Azure/Commercial-Marketplace-SaaS-Accelerator.git -b 8.0.0 --depth 1;
cd ./Commercial-Marketplace-SaaS-Accelerator/deployment; `
.\Deploy.ps1 -WebAppNamePrefix "test1" -ResourceGroupForDeployment "sandbox" -PublisherAdminUsers "[email protected]" -Location "eastus2" -TenantID "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx" -AzureSubscriptionID "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"

2. Upon completion of step 1, run the MS provided update script.

wget https://dotnet.microsoft.com/download/dotnet/scripts/v1/dotnet-install.sh; chmod +x dotnet-install.sh;
./dotnet-install.sh -version 8.0.303; $ENV:PATH="$HOME/.dotnet:$ENV:PATH";
dotnet tool install --global dotnet-ef --version 8.0.6; git clone https://github.com/Azure/Commercial-Marketplace-SaaS-Accelerator.git -b 8.0.0 --depth 1;
cd ./Commercial-Marketplace-SaaS-Accelerator/deployment; `
.\Upgrade.ps1 -WebAppNamePrefix "test1" -ResourceGroupForDeployment "sandbox"

4. Update script should halt on line 106 with the error I posted at the top of the ticket. Further confirmation of the problem can be had by inserting some comments prior to line 106 of the update script:

Write-host "## User"
Write-host $User
Write-host "## Password"
Write-host $Pass

Expected behavior
I would expect that running an update against a freshly installed azure instance with zero changes to run to completion w/o error, and reasonable minor changes should be successfully deployed.

Screenshots
N/A

Environment (please complete the following information):

• Are you using the CloudShell? I tried both locally and using cloudshell
• If not using the CloudShell:
  • OS:

Platform ServicePack Version VersionString
Win32NT 10.0.26100.0 Microsoft Windows NT 10.0.26100.0

- PowerShell version:

Major Minor Build Revision
5 1 26100 2161

- Modules installed

dotnet tool install --global dotnet-ef --version 8.*
Install-PackageProvider -Name NuGet -MinimumVersion 2.8.5.201 -Force
Install-Module sqlserver

Additional context
I noticed that the linked video tutorial that is accompanying the instructions also shows a user name and password in the connection string generated by the install script:

https://github.com/Azure/Commercial-Marketplace-SaaS-Accelerator/blob/main/docs/Installation-Instructions.md
Under 'Setting up a development environment' (https://www.youtube.com/watch?v=H8p9n1bVTjY) Look at the time of 18:30.