How to access onedrive files using Graph API? Currently there are two ways, those are delegated and application flows, and in this article I’ll briefly explain both.
Think of Azure Service Principal (app reg) like another user, except this one is a technical account. With that in mind let me explain the differences.
Delegated flow means that a real user gives an explicit consent for this technical user to access their drive (resource/service) temporarily. It can be either short-term (like a web app) within the current session, or long term (‘offline’ access) (like OneDrive app, or logic apps, etc.) across the sessions. You can call it user impersonation because it connects to resources using user’s permissions.
It’s called delegated, because at one point or another a user with the permissions to the resource must consent (delegate) their access to another identity via this well known web popup that lists requested (delegated) permissions.
So, in a delegated flow, there are always two users that participate. One is a real user who is consenting their access to resource and a technical user (service principal) which will get this temporary delegated access.
This temporary short term access that I’ve mentioned is done via delegated user’s Access Token (also called Bearer tokens). This token is valid for 30 minutes and must be refreshed afterwards. As long as the session is open you can freely refresh it at any time. If session is closed, the user will need to authenticate again for your app to generate new tokens.
Long term access, also called ‘offline’ access, once consented will additionally return something called Refresh Token. This token can be used to generate new Access Tokens at any time in the background without the need for user to re-authenticate to open a new session. This is how onedrive app works.
Longevity of the Refresh Token is usually 90 days, but it is automatically extending its lifetime beyond 90 days when you use it to generate access tokens. There is a maximum amount of time refresh token is valid even if you keep using it to refresh access tokens. You might have noticed that even onedrive might ask you to re-authenticate after a long period of time.
Both access token & refresh token longevity settings can be controlled via Azure AD settings.
An application flow means that app/principal (a technical user if you will) accesses services using its own identity without being consented by the user to his/her resources. This means that this identity has to have an access explicitly granted to resources that it will try to access.
For example, in Azure you can grant this principal RBAC permissions (reader) on a resource group. Hence service principal can call Azure apis to read this resource group properties without any user consenting his own permissions to the resource group. So, in an application flow there is no impersonation. A service principal is using its own permissions. So no longer user ‘Adam’ with Owner permissions, but ‘MyApp’ with Owner permissions.
For clarify I will just note that Access Tokens are also a thing here, it’s just how OAuth works. Except Access Tokens for application flow are in the context of the technical user instead of user who delegated the access. Refresh Tokens are not really a thing since you can just authenticate whenever you need using technical user credentials.
Let’s look at your accessing demo.xlsx from your onedrive using following URL
Think about it, the URL says
/me/. But ME in case of application flow means service principal. This technical user does not have onedrive license, nor its own onedrive space, so it doesn’t make sense to use that API.
If anything, a principal would use
/drives/<drive-id>/ endpoint to connect to a specific user onedrive.
Now the tricky part comes is. How will service princial have access to Adam’s onedrive but not Tom’s. If principal would have to access to both, that would be a security issue. Somehow it would have to be given access to Adam’s onedrive only. As far as I understand, currently you can’t give onedrive access to a service principal, only to users and user M365 groups.
Changing URL to
/drive/root:/ doesn’t fix the issue because without pointing to /users/
So, how to give app access to onedrive? Right now the only way to use use application flow on onedrive, an application permission scope must be assigned. The problem here is that there are only two available for drive endpoint right now, and those are
Files.ReadWrite.All. The problem with those is the
.All part because it effectively grants permission to all users and SharePoint drives in your organization, hence creating a security issue.
As far as I’m aware at this point in time there is no way to grant Azure Service Principal access to a user’s personal drive, but there is a way to do that with sharepoint drives. More below.
In my opinion you have two options, either
- Go delegated flow - Obtain and store delegated user’s Refresh Token and generate Access Tokens. Potentially you can leverage an Azure Logic Apps here since they do this process for you. Then call that Logic App HTTP endpoint from your app. Kind of nano-service approach if you will.
- Go application flow - Switch from OneDrive to SharePoint which recently gained a new graph permission called Site.Selected which allows granular access to one sharepoint site including its drive for application access. It should be noted that Microsoft Teams Team is also a sharepoint site so you can create your own personal team https://devblogs.microsoft.com/microsoft365dev/controlling-app-access-on-specific-sharepoint-site-collections/
Personally I recommend option 2 for multitude many reasons. Most notably, no need for content flow programming, no need to take care of refresh tokens.
Alternatively, since it looks like you don’t have SharePoint, please consider using logic apps with Option 1, as they have connectors to write directly to excel workbooks on OneDrive / SharePoint without needing to program all that API access. But for security reasons, I’d create a separate regular MFA enabled user & give him EDIT access to a file. Then use that user within Logic Apps, so that your personal refresh token isn’t stored in Azure resource group api connection object.
Lastly, I’d like to make a note on the design direction in general. If you are building a web app then you should always save all user info into some sort of database, be it a relational like Azure SQL, or a NoSQL one like Table Storage or Cosmos DB.
Export from database into excel on onedrive should be done asynchronously, and preferably in batches, because OneDrive and Excel are not good with concurrency. Otherwise you might see issues if your user traffic spikes.
I hope this explains the process and differences briefly and helps you undertand understand the topic better.