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

5
votes

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.

1
azureazure-sql-databaseazure-active-directoryazure-sql-server
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. – Tom Sun - MSFT
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. – Ding Liu
I'm having the same issue :( – Peter

1 Answers

17
votes

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