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
Install the Az.Accounts module
Install-Module Az.Accounts -force
If you haven't got one already, create a secret on your service connection's app registration in Entra ID
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
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.
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 🤷♂️