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

Question

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.

Answer 1

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.

Answer 2

Most likely though i can't confirm this as i'm not 100% sure, but I think the set-azsqlserveractivedirectoryadministrator, only filters by azaduser or azadgroup. it probably won't search for service principals. as a workaround though, if you want to accomplish this. you could create an azure ad group something called dbas or something. and add the service principal to that group. then add the group to the sql server using that set-azsqlcommand.

so something like this:

$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"

Hope that works for you, not 100% directly answer what you were asking, but I believe it is a viable workaround per your requirements hopefully.