Setting AD Admin on Azure SQL - Permissions Issue

I have been working on automation for a particular client, and ran into an interesting issue with adding Azure AD administrators to an Azure SQL instance.  The purpose of this post is to chat a little more about how to debug this issue and ultimately fix it.

As part of my process, I generally create automation and test it using my own account and on my local machine.  Once I feel like I have something working, I move that to Azure automation.  In general, the service account that Azure Automation automatically has the same permissions as my own account (unless changed from the default).  This is true for Azure, but not true for some of the API access that may be inherently required.

The command that I am using to add a SQL Admin is Set-AzureRmSqlServerActiveDirectoryAdministrator.  Unfortunately, the MSDN docs do a pretty poor job of describing the minimum set of permissions required to run these commands.  In my automation tests, I received a cryptic "Access Denied".  At first I thought this had to do with Azure access, but that didn't make much sense.  Running the above command with the verbose flag and the debug flag yielded the following:

As you can see from the body of the response, I do not have sufficient privileges with the automation service account.  The Request is going out to the graph API and seems to be verifying that the display name actually exists in AAD before adding it.

Granting permissions to the service account is quite easy, can can be done via the Azure portal.  Navigate to AAD, click App Registrations, select the appropriate one and then click on Required Permissions.  As these permissions need to be done by the service account itself, click add and then select the Graph API.  I selected Read All Groups and Read Directory Data from the Application Permissions section.

After this, ensure to hit the Grant Permissions button at the top to make the changes permanent.  After these changes, I was finally able to add an Azure AD Admin to an Azure SQL server via script with an Azure Automation service account.