ManagedIdentityCredential authentication failing

In our Azure Pipeline we add members to roles of a SQL Server database:

ALTER ROLE [db_datareader] ADD MEMBER [$(user)]
ALTER ROLE [db_datawriter] ADD MEMBER [$(user)]

This has worked years without problem.

However since 17.6, I am getting following error:

Invoke-Sqlcmd : ManagedIdentityCredential authentication failed: [Managed Identity] Authentication unavailable. Either the requested identity has not been assigned to this resource, or other errors could be present. Ensure the identity is correctly assigned and check the inner exception for more details. For more information, visit https://aka.ms/msal-managed-identity.
Status: BadRequest
Content: { "error": "invalid_request", "error_description": "Identity not found" }

Also simpler queries fail, like this one:

SELECT * 
FROM [sys].[database_principals];

We are using Microsoft Entra authentication for the Azure SQL Server. But also haven't changed anything there.

The service principle of the azure pipeline is owner of the resource. Although I'm not even sure if that is needed for this use case.

The system identity configured on the SQL Server has the following permissions and seems to be fine:

We are using ubuntu-latest hosted agents. I have tried using an older versions of the agents 22.04 but also no success. Did maybe something change that we are missing?

After trying different approaches and banging my head against a wall for a few hours I finally managed to get this working. Here's what I did

1. Install the Az.Accounts module

   Install-Module Az.Accounts -force
   

2. If you haven't got one already, create a secret on your service connection's app registration in Entra ID

3. Call Connect-AzAccount

   $ApplicationId = '<client id of your service connections app registration>'
   $Password = '<secret of your service connections app registration>' | ConvertTo-SecureString -AsPlainText -Force
   $Credential = [PSCredential]::New($ApplicationId,$Password)
   $TenantId = '<tenant id>'
   Connect-AzAccount -ServicePrincipal -TenantId $TenantId -Credential $Credential
   

4. Retrieve an access token

   $access_token = (Get-AzAccessToken -ResourceUrl https://database.windows.net).Token
   

5. Call Invoke-Sqlcmd with the following parameters

   Invoke-Sqlcmd -ServerInstance $(sqlServerName).database.windows.net -Database $(sqlDbName) -AccessToken $access_token
   

You can then add Query or InputFile parameters to the statement.

Warning!

Rather inexplicably, I found that if I wrap the call to Invoke-Sqlcmd over multiple lines I got the following error:

Invoke-Sqlcmd : Login failed for user '<token-identified principal>'.

I changed this to be a single line statement and it worked 🤷‍♂️