Azure Sql Provisioning Stopped Working

[kijanawoodard]

Is there an existing issue for this?

• I have searched the existing issues

Describe the bug

I'm not sure where to file this, so I'm starting here since I hit it using aspire 9.3.

Long story short, today my builds starting failing in the provision step hooking up web to azure sql.
Everything has been working for a couple weeks. I found an error on azure, linked from GitHub action, that pointed to https://www.powershellgallery.com/packages/SqlServer/22.4.5.1.

I did an azd infra generate and forced the package back to the previous version and everything is deploying again.

22.4.5.1 was released ~16 hours ago. I noticed a problem ~7 hours ago.

I can't find the source code this script, so, I started here. Happy to resubmit in the right repo if I knew where it was.

Expected Behavior

Deploys successfully.

Steps To Reproduce

I would think anyone deploying to azure sql with aspire and identity would have a problem today.

Exceptions (if any)

No response

.NET Version info

.NET SDK:
Version: 9.0.101
Commit: eedb237549
Workload version: 9.0.100-manifests.3068a692
MSBuild version: 17.12.12+1cce77968

Runtime Environment:
OS Name: Mac OS X
OS Version: 15.5
OS Platform: Darwin
RID: osx-arm64
Base Path: /usr/local/share/dotnet/sdk/9.0.101/

.NET workloads installed:
There are no installed workloads to display.
Configured to use loose manifests when installing new manifests.

Host:
Version: 9.0.0
Architecture: arm64
Commit: 9d5a6a9aa4

.NET SDKs installed:
8.0.300 [/usr/local/share/dotnet/sdk]
8.0.401 [/usr/local/share/dotnet/sdk]
9.0.101 [/usr/local/share/dotnet/sdk]

.NET runtimes installed:
Microsoft.AspNetCore.App 8.0.5 [/usr/local/share/dotnet/shared/Microsoft.AspNetCore.App]
Microsoft.AspNetCore.App 8.0.8 [/usr/local/share/dotnet/shared/Microsoft.AspNetCore.App]
Microsoft.AspNetCore.App 9.0.0 [/usr/local/share/dotnet/shared/Microsoft.AspNetCore.App]
Microsoft.NETCore.App 8.0.5 [/usr/local/share/dotnet/shared/Microsoft.NETCore.App]
Microsoft.NETCore.App 8.0.8 [/usr/local/share/dotnet/shared/Microsoft.NETCore.App]
Microsoft.NETCore.App 9.0.0 [/usr/local/share/dotnet/shared/Microsoft.NETCore.App]

Other architectures found:
None

Environment variables:
Not set

global.json file:
Not found

Learn more:
https://aka.ms/dotnet/info

Download .NET:
https://aka.ms/dotnet/download

Anything else?

No response

[davidfowl]

How is the powershell package related to this error?

[acarville-ic]

I believe I'm experiencing the same issue - our pipeline began failing during the same timeframe described above.

Our Azure 'sql-roles' deployment is failing, with all individual deployment scripts unable to execute successfully.
Upon examining the deployment script logs, I can see an error that it cannot locate the required System.Runtime version.

�[31;1mImport-Module: �[31;1mCould not load file or assembly 'System.Runtime, Version=8.0.0.0,
Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a'. The system cannot find the
file specified.
�[0m
�[31;1mImport-Module: �[0m/mnt/azscripts/azscriptinput/userscript.ps1:8
�[36;1mLine |
�[36;1m   8 | �[0m �[36;1mImport-Module SqlServer�[0m
�[36;1m     | �[31;1m ~~~~~~~~~~~~~~~~~~~~~~~
DECLARE @name SYSNAME = 'mi-*********';
DECLARE @id UNIQUEIDENTIFIER = '*********-8298-402d-9ee5-89333184ad33';

-- Convert the guid to the right type
DECLARE @castId NVARCHAR(MAX) = CONVERT(VARCHAR(MAX), CONVERT (VARBINARY(16), @id), 1);

-- Construct command: CREATE USER [@name] WITH SID = @castId, TYPE = E;
DECLARE @cmd NVARCHAR(MAX) = N'CREATE USER [' + @name + '] WITH SID = ' + @castId + ', TYPE = E;'
EXEC (@cmd);
�[31;1m�[36;1m     | �[31;1mThe module to process 'SqlServer.psm1', listed in field
�[36;1m     | �[31;1m'ModuleToProcess/RootModule' of module manifest

-- Assign roles to the new user
DECLARE @role1 NVARCHAR(MAX) = N'ALTER ROLE db_owner ADD MEMBER [' + @name + ']';
EXEC (@role1);

�[36;1m     | �[31;1m'/root/.local/share/powershell/Modules/SqlServer/22.4.5.1/SqlServer.psd1' was not processed because no valid module was found in any module directory.
�[0m
�[33;1mWARNING: Resulting JSON is truncated as serialization has exceeded the set depth of 2.�[0m

Appears to be using SqlServer PowerShell module version 22.4.5.1, as referenced in the report above.
This appears to be the only component that has changed for us - as all versions of Aspire and Azd are unchanged.

[davidfowl]

Ah hah!

@sebastienros

[davidfowl]

I can't find the source code this script, so, I started here. Happy to resubmit in the right repo if I knew where it was.

It's here

aspire/src/Aspire.Hosting.Azure.Sql/AzureSqlServerResource.cs

Lines 181 to 214 in 16dbd2f

	scriptResource.ScriptContent = $$"""
	$sqlServerFqdn = "$env:DBSERVER"
	$sqlDatabaseName = "$env:DBNAME"
	$principalName = "$env:PRINCIPALNAME"
	$id = "$env:ID"
	# Install SqlServer module
	Install-Module -Name SqlServer -Force -AllowClobber -Scope CurrentUser
	Import-Module SqlServer
	$sqlCmd = @"
	DECLARE @name SYSNAME = '$principalName';
	DECLARE @id UNIQUEIDENTIFIER = '$id';
	-- Convert the guid to the right type
	DECLARE @castId NVARCHAR(MAX) = CONVERT(VARCHAR(MAX), CONVERT (VARBINARY(16), @id), 1);
	-- Construct command: CREATE USER [@name] WITH SID = @castId, TYPE = E;
	DECLARE @cmd NVARCHAR(MAX) = N'CREATE USER [' + @name + '] WITH SID = ' + @castId + ', TYPE = E;'
	EXEC (@cmd);
	-- Assign roles to the new user
	DECLARE @role1 NVARCHAR(MAX) = N'ALTER ROLE db_owner ADD MEMBER [' + @name + ']';
	EXEC (@role1);
	"@
	# Note: the string terminator must not have whitespace before it, therefore it is not indented.
	Write-Host $sqlCmd
	$connectionString = "Server=tcp:${sqlServerFqdn},1433;Initial Catalog=${sqlDatabaseName};Authentication=Active Directory Default;"
	Invoke-Sqlcmd -ConnectionString $connectionString -Query $sqlCmd
	""";

No version 😢

[kijanawoodard]

@davidfowl

How is the powershell package related to this error?

SMH. Thanks for the assist @acarville-ic. I completely forgot to post details I had written up on X and in a "contact owner" post on powershell gallery.

https://x.com/kijanawoodard/status/1935158517729739171

System.IO.FileNotFoundException: Could not load file or assembly 'System.Runtime, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a'. The system cannot find the file specified.
...
at <ScriptBlock>, /root/.local/share/powershell/Modules/SqlServer/22.4.5.1/SqlServer.psm1: line 61

Here's a workaround I'm using:

azd infra generate.

find Install-Module -Name SqlServer
replace with Install-Module -Name SqlServer -RequiredVersion 22.3.0

[sebastienros]

Here is the module on the powershell gallery: https://www.powershellgallery.com/packages/SqlServer/22.4.5.1

Does it mean that a new version of the module got deployed and required net8.0 but it's not on the containers that run the script? It was released yesterday.

Does anyone know where the releases are documented with changelogs and requirements? NB: I assume this will break more than just Aspire

cc @Matteo-T