3

I am attempting to call an Azure AD protected WebApi from Excel VSTO WorkBook. This is the same webApi we use for our SPA applications but now want to also consume this same Api directly from Excel.

I have searched for quite a while and cannot find any guidance on how this should work.

Does anyone know if this is possible and if so how it should be accomplished?

RBZ
  • 2,034
  • 17
  • 34

1 Answers1

3

I ran into this yesterday and got it working, thanks this thread.

It sounds like this scenario was unsupported for a time, but it definitely works now. Here's what needs to be done:

The service that is being accessed from Excel, it should send back a HTTP 401 response at some point to let the client (Excel) know that authentication is required (this should already be happening). Excel will then send another request to the service with the header Authorization: Bearer. It's important to note that nothing comes after Bearer, because once the authentication flow has been completed, Excel will start sending requests with a similar header, but there will be more information after the word Bearer, and those requests you should allow to continue through the request pipeline.

In order for the authentication flow to work correctly, the service response to the aforementioned request must contain the following header value:

WWW-Authenticate: Bearer authorization_uri="Your auth URI here"

The quotes SHOULD be included in the response. Since you are using Azure AD, your URI will look something like this:

https://login.microsoftonline.com/{tenantId}/oauth2/authorize

Replace {tenantId} with your tenant ID.

The one caveat that I've found for this to work, is that the URL to the domain of the service (e.g. http://my.domain.com) must match the App Uri Id in the App Registration. I'm hoping there's a way around this because I believe I've read in the past that this is not a good practice (though I can't find the source anymore). If there's another way to handle this, I'll come back and update my answer.

Brian Ball
  • 12,268
  • 3
  • 40
  • 51
  • Hi @brian-ball, , This is what exactly I was looking for, I'm passing the headers like this. `Source = Json.Document(Web.Contents(url,[Headers = [#"Content-Type"="application/json",#"WWW-Authenticate" = "Bearer authorization_uri https://login.microsoftonline.com/{tenantId}.onmicrosoft.com/oauth2/v2.0/token",#"Ocp-Apim-Subscription-Key" = "{SubKey}"]` How do I embed the body for the authorization url? Below is the body of authorization url. `client_id={ClientId}&client_secret={secret}&scope=api://{..}/Token.Read&grant_type=password&userName={user}.onmicrosoft.com&password={pass}` – Pandiarajan Jul 22 '21 at 16:56
  • 1
    I'm having a hard time following your code, but I'm seeing placeholders like {pass} and {secret}, You shouldn't be passing any sensitive information back to Excel. You need to send back the URL that Excel will then use to open a small web browser that the user will use to authenticate. Once the authentication workflow is done, Excel will have obtained a token that it will include in the header with the next request to your service. You should be able to take that token and redeem it with your OAuth provider to finish the authentication workflow. – Brian Ball Jul 23 '21 at 16:18
  • @BrianBall - thanks for this! Been struggling for awhile trying to figure out why I couldn't authenticate from Excel. Something to add for anyone else that lands here - for some reason Excel also requires that you authorize a scope called "user_impersonation." I had to add that to get it working. Did you ever figure out why the App ID URI needs to be the same as the domain? That seems problematic given that a site can be hosted under multiple domains. – DanO Jun 21 '23 at 16:16