Can't Create Azure SQL Database Users Mapped to Azure AD Identities using Service Principal

Question

As part of an Azure SQL database automation solution, I'm trying to create Azure SQL database users mapped to Azure AD Identities, using a service principal.

The result is an error message saying: Principal 'AAD_User_UPN_or_Group_Name' could not be found at this time. Please try again later.

The database users can be created using my own user account, following exactly the same procedure.

Please find more details below:

• The service principal is a member of an Azure AD security group
• The group is set as the Active Directory Admin of an Azure SQL server
• My own user account is also a member of this group
• The service principal has Directory Reader and Directory Writer role in the Azure Active Directory
• My own user account is a regular member without any admin role in the Azure Active Directory

The service principal executes following T-SQL statement inside the Azure SQL database:

CREATE USER [AAD_User_UPN_or_Group_Name] FROM EXTERNAL PROVIDER;

The returned error message is:

Principal 'AAD_User_UPN_or_Group_Name' could not be found at this time. Please try again later.

When the same T-SQL statement is triggered by my own user account, it runs successfully and the user is created.

Your help or suggestions are highly appreciated.

Answer 1

I opened a ticket with Azure support and they gave me this solution.

The sql statement needs to be:

 -- type X for AAD Group
create user [myAADGroupName] with sid = <sid>, type = X;

-- type E for AAD User or Service Principal/MSI
create user [myAADUserName] with sid = <sid>, type = E;

The sid needs to be generated from the AAD Principal ObjectID in most cases. However, for Service Principals/MSIs, it needs to come from the AppId. Here's a powershell script to generate the sid value:

param (
    [string]$objectIdOrAppId
)

[guid]$guid = [System.Guid]::Parse($objectIdOrAppId)

foreach ($byte in $guid.ToByteArray())
{
    $byteGuid += [System.String]::Format("{0:X2}", $byte)
}

return "0x" + $byteGuid