Azure Runbook Error: AADSTS700016 Application with identifier 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx' was not found in the directory 'XXXX'

Mike Welborn 46

I have an Automation Runbook (PowerShell 5.1) that is failing with the following message

Principal 'xxxxxxx' could not be resolved. Error message: 'AADSTS700016: Application with identifier 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx' was not found in the directory 'XXXX'. This can happen if the application has not been installed by the administrator of the tenant or consented to by any user in the tenant. You may have sent your authentication request to the wrong tenant. Trace ID: 44ad3532-d0fd-48dd-ba9e-d8d0e7cebf00 Correlation ID: 3db5fcb3-62b4-4e3c-b11d-71401397748c Timestamp: 2024-03-26 17:03:42Z'

User or role 'xxxxxxxxx' does not exist in this database.

Msg 33134, Level 16, State 1, Procedure , Line 2

The PowerShell script is

#Connect with Managed Identity
try {
    $AzureContext = (Connect-AzAccount -Identity).context
}
catch {
    Write-Output "There is no system-assigned user identity. Aborting."
    exit
}


Set-AzContext -Subscription 'EDHC Cloud' -InformationAction SilentlyContinue

$access_token = (Get-AzAccessToken -ResourceUrl https://database.windows.net).Token 

$query = `
    "IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE [name] = N'xxxxx' AND TYPE = 'X')
        CREATE USER [xxxxx] FROM EXTERNAL PROVIDER;

    EXEC sp_addrolemember 'db_owner','xxxxx'"

    Invoke-Sqlcmd -ServerInstance "edhmcusvpwsql01.database.windows.net" -AccessToken $access_token -Database DBA -Query $query

The error occurs when executing the Invoke-Sqlcmd command.

As a side note if I change the query to a simple SELECT command it completes successfully

So I thought the issue is permissions related, but my system assigned managed identity has the Directory Reader role in Microsoft Entra.

Additionally, I couldn't figure out what the application identifier in the error message relates to. I ran

Get-AzADApplication -ApplicationId xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx

but it returned no results.

I have no idea where to look to resolve this issue

1 answer

Fabio Andrade 640 Reputation points Microsoft Employee

2024-03-26T23:10:19+00:00

Hi @Mike Welborn , Thanks for reaching out to Microsoft Q&A

It looks like you are getting an error when trying to get an access token from Entra ID.

I'm not sure if this had worked in the past, but try to use the example 13 from the Invoke-Sqlcmd documentation as per https://learn.microsoft.com/en-us/powershell/module/sqlserver/invoke-sqlcmd?view=sqlserver-ps#example-13-connect-to-azure-sql-database-(or-managed-instance)-using-a-managed-identity. That's how the connection is made using a Managed Identity instead of an access token from an interactive user.

Let me know if you need further help.

Thanks,

Fabio
Please sign in to rate this answer.
Mike Welborn 46 Reputation points

2024-03-26T23:28:11.86+00:00

Thank you, but example 13 doesn't apply in my case. "the sample assumes that you or your DBA configured the server to accept connections using that VM Identity you are running on". I am not running anything on a VM.

And as I noted in the question, there is no problem connecting to and running a SQL SELECT statement (or INSERT, UPDATE, MERGE). The issue is with one specific command CREATE USER <> FROM EXTERNAL PROVIDER.

Fabio Andrade 640 Reputation points Microsoft Employee

2024-03-27T18:54:57.51+00:00

Hi @Mike Welborn, the idea is the same, a resource requesting an access token to Entra ID, it could be a VM, Azure SQL or others.
The error you are getting is at the moment your script tries to get a token, and the request you are sending to Entra ID has incorrect parameters.

I found a very good documentation from the SQL team for Invoke-sqlcmd, please review it an let me know if you have followed the steps from it:

https://techcommunity.microsoft.com/t5/azure-database-support-blog/invoke-sqlcmd-with-azure-automation-for-azure-sql-database/ba-p/3633548

Thanks,

Fabio

Mike Welborn 46 Reputation points

2024-03-28T14:15:52.0566667+00:00

Thank you, but I have seen this before. The problem is with the note on that page

Note: it’s required to set “create Azure Run As Account” to yes in order to run the PowerShell script later successfully. In case this value cannot be set to yes in your environment it will require to enable managed identity to Azure automation and you can check this link for more information about this preview.

Run As accounts have been retired

Nevertheless, I do have a System Assigned Managed Identity on my Automation Account and dual authentication has been configured on the server

My automation account has the directory reader role

So, I still do not know what is missing or what else needs to be configured.

And back to my original post, it would probably be helpful to know what the application being referenced is

"Application with identifier 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx' was not found in the directory 'XXXX'"

But I can't find anything with that GUID

Private rant "Why supply the GUID rather than the application name in the error message, especially when there is no GUID lookup in Azure (at least I haven't been able to find one?"

Fabio Andrade 640 Reputation points Microsoft Employee

2024-03-28T21:11:58.0866667+00:00

Hi @Mike Welborn.

I'm trying to help you from the Identity perspective, but we might need someone with Azure SQL knowledge as well :)

One last question about it: Does your System Assigned Managed identity account also has the Directory Readers permission? If it doesn't, could you please assign it and test it once more?

Thanks,

Fabio Andrade

Mike Welborn 46 Reputation points

2024-03-29T16:16:58.79+00:00

Does your System Assigned Managed identity account also has the Directory Readers permission?

Yes - that is what I noted in my prior response. Here is a more thorough illustration

This is my automation account - note the application id and the object id

These are the accounts with Directory Reader permission in Microsoft Entra - note the principal name

To complete the loop, here are the details for the system assigned managed identity - note the object (principal) id

Your response prompted me to test something else. I added the following command to my runbook: Get-AzAdUser. This worked successfully confirming that my automation account has directory reader.

So now I am wondering if the server on which the SQL command is being run) needs directory reader permissions. I am not even sure if that is an option.

Mike Welborn 46 Reputation points

2024-03-29T18:39:50.67+00:00

Tagging on to my earlier comment. I created a user assigned managed identity(UAMI). It has the directory reader role in Microsoft Entra and I assigned that UAMI to both the server and the database. Additionally, I have granted that user db_owner access to the database and it still fails attempting to CREATE USER <> FROM EXTERNAL PROVIDER.

I know the SQL command is OK because I was running the process manually without issue. It is also a command that I use all the time when configuring access to Azure SQL databases.

I decided to move it into an automation runbook so it could be scheduled, run on demand, run from an ADF pipeline call, or as part of a CI/CD pipeline. As of yet, I have been unable to find the issue, identify the app that the error code references, or find a work-around.

It seems clear that it is a permissions issue reaching out to Microsoft Entra, but I don't know what more it needs.

Fabio Andrade 640 Reputation points Microsoft Employee

2024-04-01T22:21:51.4366667+00:00

Hi @Mike Welborn

Thanks for sending such detailed information.

Your automation job is sending that AppID (GUID) to EntraID when requesting an access token, since EntraID does not know that specific AppID, it's denying providing this access token. We need to find out why that incorrect AppID is being sent to EntraID, I'll try to reach out to an Azure SQL engineer to help us in here, I'll keep you posted as soon as I have someone available to collaborate with us.

Thanks,

Fabio

Mike Welborn 46 Reputation points

2024-04-02T13:31:38.93+00:00

Many thanks. Additionally, if there is some way to pull up the app by that AppID, I would love to know how.
Sign in to comment