Connecting Excel to OAuth API on Azure
Oct 30
We have implemented authentication on our multi tenant SaaS application through Azure AD (which implements OAuth 2.0). The API's are accessed through an Angular SPA and can also be accessed by other clients (such as registered REST clients, with a dedicated client ID). In case it's relevant: we use the authorization code flow.
On the same application we have an OData API through which we want to provide data access to our users' applications such as Excel. Since we are a multi tenant application this connection needs to be authenticated just as when accessing the "normal" Web API's, such that our data layer can filter for data owned by that tenant.
Even though we only use Microsoft services (through Azure) it doesn't seem evident how Microsoft Excel can connect to the OData feed with the correct authentication method. I have found one article that explains using a Power Query editor with a custom connection definition. I would not consider this approach as it's not a robust solution for typical end users. In addition to this custom configuration approach, I have also read about commercial third party libraries that take over the connection. However for my SaaS customers I can't propose this as a general solution.
I have also found another article that uses an Azure function as a proxy API to get the data. This seems like a robust solution for end users, however I am not sure how this can be done securely and correctly authenticate the API for the correct user (the example in the article is dedicated to 1 tenant).
Q: Is there a robust (out-of-the-box) configuration for end users to access OAuth authenticated OData feed/API's from Excel? If not, what are some secure alternatives I should consider?
azure1 answer
Accepted answer · original discussion
Jul 2
You can do OAuth 2.0 authentication on AzureAD from your client application such as Excel, Word, Powerpoint and Publisher. This works for opening entire files (Office 2016 or above) or import data from a webpage (Office 2019 / M365-apps).
The way you set up OAuth authentication will differ a bit from the regular and well documenten OAuth flow as you use it in your REST clients.
Instead of sending a header that forwards the client to https://login.microsoftonline.com/{tenant}/oauth2/v2.0/authorize you need to return the http response code 401 (unauthorized) and add the next headers:
WWW-Authenticate: Bearer resource="https://management.azure.com/" client_id="{the client id of your registered app in Azure AD}", trusted_issuers="00000001-0000-0000-c000-000000000000@*", token_types="app_asserted_user_v1 service_asserted_app_v1", authorization_uri="https://login.microsoftonline.com/{tenant}/oauth2/v2.0/authorize",Basic Realm=""'
In Azure AD in your app registration:
- In API permissions:
- Azure Active Directory Graph > User.Read In
- Expose an API:
- Add a scope: https://{fqdn of your api}/email (email or other attribute you use for authentication)
- Authorized client application > Add a client application: d3590ed6-52b3-4102-aeff-aad2292ab01c (the ID of MS Office)
Note: all this is not documented at all. I found out about the headers by sniffing the traffic between Outlook and O365.
Btw: If you serve files using the webdav protocol: opening a file in Excel from a webpage works well with javascript:
location.href = "ms-excel:ofe|https://{yourAPI}/your_output.xlsx"
0 question comments
Use comments to ask for clarification. Post a solution as an answer.
No question comments on this page.