When I recently was configuring an Azure AD application, I could not assign the delegated permissions for an Azure SQL Database. It did cost me a full day to find out the Azure Portal user interface has an unexpected user interaction when it comes to selecting APIs.
In this article, I will explain how you can find all APIs available for your application.
The problem
When we want to integrate an application with Azure AD, we need to register an app.
In my case I want to let a user access an Azure SQL Database using delegation. So, the database connection will be created impersonating the user account, not a generic service account.
To grant delegation permissions to an API we go to the Required permissions section of the Azure AD app:
When we’ve created a new app there is not much showing yet, other than the default Azure AD delegated permission to sign in as the user:
We click on the Add button and choose the Select an API option:
Here we get a list of APIs to choose from. But our Azure SQL Database is not listed.
There is a search function, but entering SQL, like my colleague Remco Ploeg did, did not list any results.
This is where I started to search online if other people encountered the same problem. One of the few related posts is by Michael Collier: Connect to Azure SQL Database by Using Azure AD Authentication.
In his post he mentions not having the API listed:
Before you can continue, you need to have followed the prerequisites steps stated at the top of this post. You especially need to be sure you have created an Azure AD contained database user. If you fail to do that, you will not see “Azure SQL Database” in the list (as specified below).
But following all his steps did not make the API appear.
After wasting more time not finding any other related posts, adjusting multiple settings, and recreating Azure AD apps, I suddenly found a lead that pointed me in the right direction
The solution
One of the apps I created was named AzureSqlAppTest. Because the list of registered apps is quite long, I wanted to use the search function and I entered the word SQL.
To my surprise no results. Entering AzureSQL did show my app.
The search function in the Azure Portal is acting only as a Begins With
filter!
Desperately, I tried this trick on the API list as well. Maybe the API search function does not show SQL related APIs because the name does not start with SQL.
Instead of SQL I entered Azure in the search box, and to my amazement: a whole new list of never-before-seen APIs showed up, including our Azure SQL Database:
There are many more APIs available in the Azure Portal, there is just no indication in the UI!
After re-reading Michael Colliers blog post, I found he did mention this at the end of one of the steps:
3. Add a new required permission and select Azure SQL Database as the API. You’ll want to search for “azure” to get “Azure SQL Database” to appear in the list.
Now we can configure our app, and everything will work as expected.
The conclusion
Just because the Azure Portal UI does not give any hint there are many more APIs hidden behind the search function, I wasted a lot of good time.
I hope this post will prevent you from wasting yours, and I hope the Portal UI will improve.
