10

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.

Ding Liu
  • 103
  • 1
  • 6
  • 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 Oct 30 '18 at 09:52
  • 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 Nov 01 '18 at 01:16
  • 1
    I'm having the same issue :( – Peter Nov 27 '18 at 15:50

1 Answers1

27

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
jschmitter
  • 1,669
  • 19
  • 29
  • What if I like to add a Managed Identity as a user? – Rookian Jun 10 '20 at 16:04
  • 1
    @Rookian I've updated my answer with info on Service Principals (which includes Managed Service Identities) – jschmitter Jul 14 '20 at 15:53
  • how to get serviceprincipal of a appService – Tiju John Jul 15 '20 at 14:27
  • @TijuJohn Create an MSI following instructions [here](https://learn.microsoft.com/en-us/azure/app-service/overview-managed-identity?tabs=dotnet). Then you can query for the Service Principal object using [az ad sp show --id ](https://learn.microsoft.com/en-us/cli/azure/ad/sp?view=azure-cli-latest#az-ad-sp-show) to get the appId. – jschmitter Jul 15 '20 at 17:09
  • 1
    @jschmitter Thank you so much. My problem is resolved with your answer. – Ding Liu Dec 13 '20 at 02:03
  • 1
    Anyone know if this works for Postgress as well? – jschmitter Sep 07 '22 at 20:16
  • @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 = , type = X/E" does the same, but does not require read access to AD because user ID is already supplied in the command. – Konstantin Salavatov Jun 15 '23 at 00:55