1

I need to copy the data of an Excel file on a Sharepoint to an Azure SQL DB. This needs to be done in Azure Data Factory. I found the solution below: https://learn.microsoft.com/en-gb/azure/data-factory/connector-sharepoint-online-list?tabs=data-factory#copy-file-from-sharepoint-online While this seems to work, the disadvantage is that the app registration needs to get the Sharepoint permission 'Sites.Read.All'. This is why I was looking for doing this with MS Graph, as MS Graph allows the permission 'Sites.Selected'. This is an important one, as I want to avoid giving permission to access the whole Sharepoint (preferably I would give only permission to access one folder, but I have not found this solution at all). However, I did not find any way to do this with MS Graph in ADF.

Anyone with some tips on how to do this? A solution with an Azure Functions or Logic app is not possible/allowed in our current architecture.

Thanks in advance.

Improve this question
3
  • It is unclear if you need help with how to implement Sites.Selected permission to an app or how to restrict the apps access to just one file. As for Site.Selected, you have to create a permission using Create Site Permission then add Sites.Selected on AAD app registration. Let me know if this is what you need and I will write an answer with some code. Commented Sep 9, 2021 at 3:41
  • As for file level access, I don't think MS Graph currently supports that for apps. Commented Sep 9, 2021 at 3:42
  • Sorry if my question was not clear. I am looking for the best way to copy data from a Sharepoint Excel to a sink with ADF. For that I found the possibility to do this with a web activity on this link: docs.microsoft.com/en-gb/azure/data-factory/… For this, I need to make an app registration with Sharepoint permissions. This only allows all site access. I also found the MS Graph (but not how to implement it). I am just looking what is the best way. If you have alternative solutions, they are also welcome Commented Sep 9, 2021 at 7:19

2 Answers 2

Reset to default
0

In your case, if you can call MS Graph API from your workflow, assuming you already have an app registration then, you need to create a site permission for your app using Create permission as an admin user to the site.

POST https://graph.microsoft.com/v1.0/sites/Site-ID/permissions
{
    "roles": [
        "read"
    ],
    "grantedToIdentities": [
        {
            "application": {
                "id": "your-app-client-id",
                "displayName": "Test App"
            }
        }
    ]
}

Then assign the application permission Site.Selected to your app registration on AAD Portal. This way your app will only have access to that specific site and not all sites in your tenat

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Is this also required when you gave the Sites.Read.All permission in AAD Portal? While this seems my preferred way, I did not find a way to use the MS Graph API to copy an Excel file in ADF. Do you know anything about this?
When you use the Site.Selected permission, you dont need Sites.Read.All if you just need the app to access the one site - best practice. One your app site permission is created and application permission added, you can easily use Graph Workbooks API or just download the file using [Files API[(learn.microsoft.com/en-us/graph/api/resources/…)
Great. If it solves your scenario. Please accept the answer to help other community users.
For the MSGraph solution. Does making an AppRegistration with the 'Sites.Read.All' permission suffice, or should there be granted some permissions in Sharepoint as well? Because I granted Site.Read.All permission to the app, but I get an InvalidAuthenticationToken error.
App registration is needed. Once you have Site.Selected added to your app and permission created for your app sung POST graph.microsoft.com/v1.0/sites/Site-ID/permissions, you don't need Sites.Read.All. Note that you need application permissions.
0

Inside copy activity under source tab you can consider using HTTP connector type dataset or REST connector type dataset, which helps you to make rest call with MSGraph API.

There are two types of the above mentioned connectors which can be used in Azure Data Factory

  1. Copy data from an HTTP endpoint by using Azure Data Factory or Azure Synapse Analytics | Docs
  2. Copy and transform data from and to a REST endpoint by using Azure Data Factory | Docs
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.