Set-AzSqlServerActiveDirectoryAdministrator : Cannot find the Azure Active Directory object 'service_principal_name'

I am running the following command

$sp = az ad sp show --id $env:ARM_CLIENT_ID --query '{objectId: objectId, displayName: displayName}'
az sql server ad-admin create --resource-group data-eastus2 `
    --server-name data-eastus2-sqlsvr `
    --display-name $sp.name `
    --object-id $sp.id

which works perflecty fine without providing any Graph API permissions to service principal. Trying to mimick this functionality using Az Powershell module, by running the following

Set-AzSqlServerActiveDirectoryAdministrator -ResourceGroupName 'data-eastus2'  -ServerName 'data-eastus2-sqlsvr' -DisplayName $sp.name -ObjectId $sp.id

yields an exception

Set-AzSqlServerActiveDirectoryAdministrator : Cannot find the Azure Active Directory object 'service_principal_name'. Please make sure that the user or group 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. At line:1 char:1 + Set-AzSqlServerActiveDirectoryAdministrator -ResourceGroupName 'data ... + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : CloseError: (:) [Set-AzSqlServer...ryAdministrator], ArgumentException + FullyQualifiedErrorId : Microsoft.Azure.Commands.Sql.ServerActiveDirectoryAdministrator.Cmdlet.SetAzureSqlServerActiveDirectoryAdministrator

Providing Azure Active Directory Graph - Directory.Read.All and Microsoft Graph - Directory.Read.All API Permissions didn't help.

The Azure CLI az sql server ad-admin create will not call Azure AD Graph to validate the parameters you passed, it just calls the REST API Server Azure AD Administrators - Create Or Update to set the admin. Even if you pass wrong --display-name and --object-id(also need to be Guid format), the command will also work fine. You could check the details with --debug parameter.

The Azure powershell Set-AzSqlServerActiveDirectoryAdministrator will call Azure AD Graph getObjectsByObjectIds: Get objects from a list of object IDs to validate if the object is correct or not. And if the result's type is not an Azure AD security group, it will further call Get a user. So if the result's type is a service principal, it will also call Get a user, then it will cause the issue. You could use fiddler tool to catch the reuqest like below.

So if you want to use the Set-AzSqlServerActiveDirectoryAdministrator, you could create a security group(not office group) in Azure AD, add the service principal to the group, then add the group to the sql server admin, as mentioned in @alphaz18's reply.

$sp = Get-AzADServicePrincipal -ObjectId "<object-id>"
$group = Get-AzADGroup -DisplayName "joysec"
Add-AzADGroupMember -TargetGroupObjectId $group.Id -MemberObjectId $sp.Id
Set-AzSqlServerActiveDirectoryAdministrator -ResourceGroupName "<groupname>" -ServerName "<servername>" -DisplayName $group.DisplayName -ObjectId $group.Id

Note: To run the script above, you need to give a Directory.ReadWrite.All Application permission of Azure Active Directory Graph(not Microsoft Graph) for your AD App, and there is some delay, wait for a while and test.