Enabling public access to SQL Server in Azure devops release pipelines

Question

In order to run migrations on my Test Azure SQL which is configured with PrivateLink I temporarly enable public access and then disable it again.

Set-AzSqlServer
  -ServerName $sqlServerName
  -ResourceGroupName $(IntegrationResourceGroupName)
  -PublicNetworkAccess "Enabled"

And it worked for months, but recently I started receiving this error message.

Cannot find the Azure Active Directory object 'My_DB_Admins_Group_Name'.
Please make sure that the user or group or application you are authorizing is registered
in the current subscription's Azure Active directory. To get a list of Azure Active Directory
groups use Get-AzADGroup, or to get a list of Azure Active Directory
users use Get-AzADUser or to get a list of Azure Active Directory applications use Get-AzADApplication.

Release pipeline was not modified. What might be the reason?

I can execute this PS command from my local machine, as me, and it works fine.

Answer 1

As per the error message : Only azaduser and azadgroup are filtered by azsqlserveractivedirectoryadministrator. It is unlikely that it will look for service principles. You might make an azure ad group called dbas or something similar. To that group, then add the service principal to it.

Then add the group to the sql server using that set-azsqlcommand

    $sp = Get-AzADServicePrincipal -DisplayName "theserviceprincipalname"
Add-AzADGroupMember -MemberObjectId $($sp.id) -TargetGroupDisplayName "AAD Group Name"

Set-AzSqlServerActiveDirectoryAdministrator -ResourceGroupName 'data-eastus2'  -ServerName 'data-eastus2-sqlsvr' -DisplayName "AAD Group Name"