I gave it another whirl today and was able to figure it out. There’s not really any documentation for this, so here is mine.
If you haven’t set up AzureAD authentication in Kinetic, go set up that first using the Kinetic documentation.
Go grab a resource (ie BAQ) from the Rest Help page at /Apps/resthelp. For the purpose of this documentation, I’m going to use API v1 so I don’t have to deal with adding API keys to excel. I’ll let v2 be a challenge for another day.
This is where I was stuck. Previously if I tried to use an organizational account, I would get the following error: “The WWW-Authenticate header doesn’t contain a valid authorization URI. Header value: 'Basic realm='Epicor ICE”
Now when you attempt to sign in from Excel, you should be met with the Microsoft account selector. Awesome.
However, after you authenticate, you will likely get an error “access_denied: AADSTS650057: Invalid resource. The client has requested access to a resource which is not listed in the requested permissions in the client’s application registration. Client app ID: a672d62c-fc7b-4e81-a576-e60dc46e951d(Microsoft Power Query for Excel). Resource value from request: your_server. Resource app ID: your_id. List of valid resources from app registration:”
To fix this, add the Client app ID for Microsoft Power Query for Excel (a672d62c-fc7b-4e81-a576-e60dc46e951d) to the Authorized Client Applications under Expose an API on your app registration. Allow access to the user_impersonation scope. Wait a few minutes for the changes to take effect.
How does this relate, if at all, to @josecgomez 's comment on this thread saying:
Have you effectively mitigated what Jose is saying here? Or, have you allowed “a bumm of the street with access to excel and azure to be able to generate a valid token?”
I’m pretty sure you have mitigated that risk because you’re using Azure to authenticate the user and then authorize the user to connect… does that look right @Mark_Wonsil ?
I’m hoping my post didn’t come off as judgmental at all or saying “this is unsafe” I was just trying to understand what Jose was saying and what you put in place here and if what you put in place effectively handles the security concern that Jose had. Cause anyone with basic auth creds they phished or guessed or whatever could also get access. This has MFA and paired with API keys is a good duo for limited access. And since it’s azure you could probably even get more granular with other tools about conditional access etc.
Again, incredible contribution to the forum! Thanks for sharing this.
I’m not sure if its “unsafe” but I believe you are authorizing any excel “client” to use this Epicor Azure app. Is that an issue in it of itself? Probably not because you’d have to have a matching Epicor external identity to be a problem (maybe)
But it does mean that technically anyone who uses excel could throw “YOUR” app url in there and it would be “allowed” to use that app to authenticate assuming they can find a matching identity on the other side.
