I have an Azure Automation Account Runbook that is returning an error that references a GUID. How do I find the name of the object with that GUID?

Mike Welborn 56 Reputation points
2024-01-02T23:18:03.14+00:00

I have an Azure Automation Runbook to scrub data in a database. It is a PowerShell script that runs under my security context without issue. When I add that to a Runbook I am using a Managed Identity rather than my personal account. The script runs several SQL commands without issue and comes to a section where I am attempting to drop some database users and it generates an error (see below).

How do I determine the server principal from the guid@guid value?

Under my automation account I have both a system assigned identity and a user assigned identity, but neither of those GUIDs match the display in the error message.

And if it is not possible to determine what the GUID represents, how can I display the database principle and server principal prior to executing the SQL command?

Thanks in advance

The database principal is set as the execution context of one or more procedures, functions, or event notifications and cannot be dropped.
The server principal "ba7da4b7-xxxx-xxxx-xxxx-xxxxxxxxxxxx@682e6fb4-xxxx-xxxx-xxxx-xxxxxxxxxxxx" is not able to access the database "[database name]" under the current security context.
Azure Automation
Azure Automation
An Azure service that is used to automate, configure, and install updates across hybrid environments.
1,306 questions
0 comments No comments
{count} votes

Accepted answer
  1. Luke Murray 11,251 Reputation points MVP
    2024-01-03T22:56:53.2+00:00

    @mike welborn I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this!

    Issue:

    I have an Azure Automation Runbook to scrub data in a database. It is a PowerShell script that runs under my security context without issue. When I add that to a Runbook I am using a Managed Identity rather than my personal account. The script runs several SQL commands without issue and comes to a section where I am attempting to drop some database users and it generates an error (see below).

    How do I determine the server principal from the guid@guid value?

    Solution:

    The whole process involves copying a production database to a non-production environment and de-identifying data in the non-production environment. Queries were running fine until I hit the section that resets users in the database. I had removed the Managed Identity and as a result it was no longer able to access the database. One step I ran for debugging purposes was to record the SYSTEM_USER on a script that ran.

    • If I missed anything please let me know and I'd be happy to add it to my answer, or feel free to comment below with any additional information.
    1 person found this answer helpful.
    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. Luke Murray 11,251 Reputation points MVP
    2024-01-03T09:01:28.44+00:00

    Hi, Mike

    Theres 2 scenarios here I can think of:

    1. At a guess, its client id @ tenant ID, using Entra ID credentials.

    Go to the Application Registrations, in Entra ID, and search for the first GUID (before the @) in this list, the search hopefully should reveal the SPN it's using.

    You can also go to the Overview pane of Entra ID, and make sure the second half *after the @, aligns with your Entra ID tenancy as well.

    User's image

    1. If its not that, then you may need to login to the SQL Database, using SQL Management Studio and have a look at the Logins as per: https://help.insightsoftware.com/s/article/The-server-principal-USER-is-not-able-to-access-the-database-COMPANY-NAME-under-the-current-security-context?language=en_US

  2. Mike Welborn 56 Reputation points
    2024-01-03T22:29:59.8633333+00:00

    I have found the issue. The whole process involves copying a production database to a non-production environment and de-identifying data in the non-production environment. Queries were running fine until I hit the section that resets users in the database.

    I had removed the Managed Identity and as a result it was no longer able to access the database.

    One step I ran for debugging purposes was to record the SYSTEM_USER on a script that ran. The SYSTEM_USER was "ba7da4b7-xxxx-xxxx-xxxx-xxxxxxxxxxxx@682e6fb4-xxxx-xxxx-xxxx-xxxxxxxxxxxx". This appears to be my user assigned managed identity, but it's id is "df244a11-xxxx-xxxx-xxxx-xxxxxxxxxxxx".

    So, I still don't know where "ba7da4b7-xxxx-xxxx-xxxx-xxxxxxxxxxxx@682e6fb4-xxxx-xxxx-xxxx-xxxxxxxxxxxx" comes from.

    However, my immediate problem is resolved.

    Thanks to all who viewed


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.