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.

Winding

What kind of service principal,registried it by yourself or created by MSI? If it is created by MSI function, I also could reproduce the issue with not admin account. If registried Application I can't reproduce it. – Puiia

Hi @TomSun, thank you very much for helping. The service principal is registered by myself. The service principal has a certificate credential. It gets an access token from Azure and then use this token to open an connection against Azure SQL database, then executes T-SQL script inside the database. – Winding

I'm having the same issue :( – Anfractuosity

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

Finned

What if I like to add a Managed Identity as a user? – Tenishatenn

@Tenishatenn I've updated my answer with info on Service Principals (which includes Managed Service Identities) – Finned

how to get serviceprincipal of a appService – Exudation

@TijuJohn Create an MSI following instructions here. Then you can query for the Service Principal object using az ad sp show --id <objectId> to get the appId. – Finned

@Finned Thank you so much. My problem is resolved with your answer. – Winding

Anyone know if this works for Postgress as well? – Finned

@jschmitter, yes it works. The basic idea behind the problem is that "create user xx from external provider" command require read access to AD to retrieve user ID. On the other hand, "create user xx with sid = <sid>, type = X/E" does the same, but does not require read access to AD because user ID is already supplied in the command. – Phototelegraph