Connecting Sharepoint Document Library to ADF via managed identity

Question

Connecting Sharepoint Document Library to ADF via managed identity

Anonymous

Hello there,

I'm trying to implement a solution migrate SharePoint Document library to Azure Blob Storage via Data Factory(only). I have come across multiple solutions which implement logic apps or either postman. I also came across one solution in Microsoft documents here:
https://learn.microsoft.com/en-us/azure/data-factory/connector-sharepoint-online-list
However, in this scenario, for web activity Body field expects details like client ID, client secret mentioned in plain text. And instead that to be done is there any alternative wherein we do not need these details to be exposed or can use managed identity in the web activity to fetch token.

Any help on this would be appreciated!

@KranthiPakala-MSFT I came across your solution to this and need a little tweak to that.

Anonymous

2021-04-28T20:41:25.273+00:00

@KranthiPakala-MSFT can you please help on this
KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2021-04-29T15:19:10.617+00:00

Hi anonymous user,

Welcome to Microsoft Q&A forum and thanks for reaching out.

I haven't tried with managed identity but let me double check if it is possible or not and will get back to you soon.

Thank you for your patience.
Anonymous

2021-04-29T15:31:11.403+00:00

Hello @KranthiPakala-MSFT ,
My main query here is, after following the above mentioned link, it expects the client credentials to be written in body part. I need a workaround to that specific part for my solution to be implemented in my case. So if you can help me with that specific part, would be really appreciated.
Keval Vankudre 1 Reputation point

2021-05-17T16:58:54.13+00:00

Hello @KranthiPakala-MSFT , Exploring more into this, I found a way to access filenames via Microsoft Graph. However, these details are in JSON format. So my query here is, how can we fetch the file details from this JSON format and further copy those files to azure. Can you please guide me through this. It would really be helpful. ![97199-json.png][1] [1]: /api/attachments/97199-json.png?platform=QnA

Accepted answer

0 additional answers

Your answer

Anonymous

2021-04-28T20:41:25.273+00:00

@KranthiPakala-MSFT can you please help on this
KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2021-04-29T15:19:10.617+00:00

Hi anonymous user,

Welcome to Microsoft Q&A forum and thanks for reaching out.

I haven't tried with managed identity but let me double check if it is possible or not and will get back to you soon.

Thank you for your patience.
Anonymous

2021-04-29T15:31:11.403+00:00

Hello @KranthiPakala-MSFT ,
My main query here is, after following the above mentioned link, it expects the client credentials to be written in body part. I need a workaround to that specific part for my solution to be implemented in my case. So if you can help me with that specific part, would be really appreciated.
Keval Vankudre 1 Reputation point

2021-05-17T16:58:54.13+00:00

Hello @KranthiPakala-MSFT , Exploring more into this, I found a way to access filenames via Microsoft Graph. However, these details are in JSON format. So my query here is, how can we fetch the file details from this JSON format and further copy those files to azure. Can you please guide me through this. It would really be helpful. ![97199-json.png][1] [1]: /api/attachments/97199-json.png?platform=QnA

Answer 1

KranthiPakala-MSFT 46,642 Microsoft Employee Moderator

Hi anonymous user,

Thanks for clarifying the ask. As per the current ADF SharePoint connector limitation, it uses service principal authentication to connect to SharePoint. This is limitation by design at the moment. Hence managed identity not an option

My main query here is, after following the above mentioned link, it expects the client credentials to be written in body part. I need a workaround to that specific part for my solution to be implemented in my case. So if you can help me with that specific part, would be really appreciated.

In order to avoid exposing client credentials in the body of the web activity while getting the access token, you can store the value of the client credentials in Azure Key Vault. Then have another web activity before GetBearerToken web activity to retrieve the client credentials from Key Vault using Get Secret API - GET {vaultBaseUrl}/secrets/{secret-name}/{secret-version}?api-version=7.1

And use output.value of that web activity in body of the GetBearerToken web activity using a dynamic expression as below. This way you can avoid exposing the client credentials in your ADF web activity.

Hope this info helps. Do let us know if you have further query.

----------

Please don’t forget to Accept Answer and Up-Vote wherever the information provided helps you, this can be beneficial to other community members.

Anonymous

2021-04-30T21:59:47.84+00:00

Hello @KranthiPakala-MSFT ,

Above solution works perfectly fine.
Further my query is, I want to copy whole folder from SharePoint Document Library as it is to Azure Delta Lake Gen 2. And the SPO library contains mixed file formats of which I will need only .xlsx files. I tried the document link steps as above, but it copies file as BLOB. How do I copy excel files as it is from SharePoint.

Thanks a lot for all the support.
Anonymous

2021-05-03T14:17:49.847+00:00

Hello @KranthiPakala-MSFT ,

Can you please guide me through this.
KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2021-05-03T14:23:07.783+00:00

Hi anonymous user

Thanks for getting back and sorry for the delay. As per my understanding you would want to copy the whole folder (but just the xlsx files in it) from SharePoint Document Library to ADLS Gen2 (So you want to keep the folder structure as is with just the xlsx files) , please confirm so that I can double check on it.

Also could you please clarify on this statement "I tried the document link steps as above, but it copies file as BLOB. How do I copy excel files as it is from SharePoint." ?

Thanks
Anonymous

2021-05-03T14:26:13.987+00:00

Hello @KranthiPakala-MSFT ,

Yes that is correct. I have multiple file types in SPO document library of which only .xlsx would be needed in ADLS. I read some documents but I'm unable to fetch a complete folder from SPO.
Anonymous

2021-05-03T14:30:36.893+00:00

Hello @KranthiPakala-MSFT ,

that highlighted means, I have followed stepns mentioned in the link :
https://learn.microsoft.com/en-us/azure/data-factory/connector-sharepoint-online-list.
And by following these steps i was able to copy a file from SPO to ADLS, however, the copied file was in BLOB format instead it should have been an excel file.
KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2021-05-03T14:34:24.037+00:00

Thanks for clarifying anonymous user. Let me do some analysis on this and will get back to you as soon as I have update.

Thank you for your patience.
Anonymous

2021-05-03T20:43:34.127+00:00

Hello @KranthiPakala-MSFT ,

In addition above steps, I was also looking into copying files with same names and preserve the naming convention. and for that I tried out using Get Metadata activity but that activity doesn't support http linked service. So what can be done in this case, so that I can fetch the file names first from Document library and preserve the file names as it is.
Anonymous

2021-05-04T16:57:32.76+00:00

Hello @KranthiPakala-MSFT ,

Can you please advice on this.
KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2021-05-04T17:05:22.563+00:00

Hi anonymous user, I'm looking into it and will get back to you as soon as I have my findings ready. sorry for the delay
Anonymous

2021-05-04T17:14:50.13+00:00

Hi @KranthiPakala-MSFT ,

One possible solution to this which I think is like, we get the names of all the files from the document library in SPO. Now I have implemented a solution on SPO side that i'll only have .xlsx files in Document Library. So by fetching the name list of files and then passing through a parameter in getfilebyserverrelativeurl method, can fetch files from Document Lib. So can you just say if this can be a feasible solution, and if yes, how can we fetch name list of files an iterate over that
KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2021-05-04T22:28:00.617+00:00

Hi anonymous user,

Yes, the above solution you mentioned is feasible but the tricky part is how to fetch the file name list.
I did try my best to find the relevant Sharepoint REST method which can provide the list of file names in a folder but no luck. I have only found this .NET API class references - https://learn.microsoft.com/previous-versions/office/sharepoint-csom/ee536712(v=office.15) but not the REST documents.

As my knowledge on Sharepoint is limited, I would recommend you to please reach out in sharepoint tech community - https://techcommunity.microsoft.com/t5/sharepoint/ct-p/SharePoint for further assistance on this ask. That forum is better suited for this ask and they have Sharepoint SME's who can better assist on this. Sorry for not being very helpful on this particular area.

Please do let me know if you have any further query related to ADF.

Thank you
Anonymous

2021-05-05T14:36:18.62+00:00

Hello @KranthiPakala-MSFT ,

Thanks a lot for your support.

I have posted my query in SharePoint community here: https://techcommunity.microsoft.com/t5/sharepoint/fetching-xlsx-csv-files-from-sharepoint-online-document-library/m-p/2326475
So it would be helpful if you can internally redirect to an SME so that issue can be resolved soon.

Further, after following the steps here: https://learn.microsoft.com/en-us/azure/data-factory/connector-sharepoint-online-list
The sink for copy activity copies file in the binary format. So my query here is, how do I preserve the .xlsx format of the file with same name.
KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2021-05-05T18:20:17.07+00:00

Hi anonymous user,

To copy file as-is, you will select "Binary" type dataset in your sink and it will copy the file as .xlsx format only. To preserve the file name, assuming you get the list of files name and pass them in source connector Base Url as a parameter, you can use the same parameter expression in your sink FilePath settings. This should help achieve your requirement.
Anonymous

2021-05-06T14:36:47.967+00:00

Hello @KranthiPakala-MSFT , Based on the document to copy SharePoint file, I had configured in the same way and it worked as well. But file format is not preserved. It is stored as block Blob. Below is the sink config: ![94495-image.png][1] I really the appreciate the support. [1]: /api/attachments/94495-image.png?platform=QnA
KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2021-05-11T18:34:57.773+00:00

Hi anonymous user,

Sorry for the delay. You will have to configure the file name (for eg: Sample.xlsx) in sink File path settings. For eg in your Base URL if you are passing the filename hardcoded (/sites/site2/Shared Documents/TestBook.xlsx.) then you can configure the same name in sink, in case if you have passing it dynamically using parameters, then you can pass the same dynamic parameter value in sink file path settings by declaring a dataset variable for file name.
Keval Vankudre 1 Reputation point

2021-05-14T12:56:48.1+00:00

Hello @KranthiPakala-MSFT ,

I was looking around for solutions to find this particular problem and got into Microsoft Graph. So can you please let me know how can we integrate Microsoft Graph into Data Factory ?

Thanks in advance.
Keval Vankudre 1 Reputation point

2021-05-17T17:00:27.43+00:00

Hello @KranthiPakala-MSFT , Exploring more into this, I found a way to access filenames via Microsoft Graph. However, these details are in JSON format. So my query here is, how can we fetch the file details from this JSON format and further copy those files to azure. Can you please guide me through this. ![97217-json.png][1] [1]: /api/attachments/97217-json.png?platform=QnA
KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2021-05-18T05:03:40.923+00:00

Hey anonymous user,

Thanks for getting back. If you are using Microsoft Graph API to get the list of file names, then you could try using a web activity to get the file name list and then pass the output to Foreach activity to iterate through each file name and then inside foreach activity, have existing flow --> "WebActivity to get the KeyVault secret --> WebActivity to Get Access Token --> Copy Activity" You can pass the @item.name value in the Base URI as well as sink file path settings file name.

Let us know how it goes.

Thanks
Keval Vankudre 1 Reputation point

2021-05-18T13:21:43.367+00:00

Hello @KranthiPakala-MSFT ,

Thanks for the help.
I will update as soon as I have tried out this thing.
KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2021-05-18T17:35:46.653+00:00

Sure, do let us know how it goes.

Thank you
Keval Vankudre 1 Reputation point

2021-05-18T18:57:38.517+00:00

Hello @KranthiPakala-MSFT ,

After trying out the suggested solution, it throws an error as 'Access token required'. I did try couple of other ways as well. Also, all the access also have been granted.

Can you please help me out
Keval Vankudre 1 Reputation point

2021-05-19T17:16:07.913+00:00

Hello @KranthiPakala-MSFT , I have moved ahead with the above error, however, I now have another error which says, "error":{"code":"accessDenied","message":"Access denied", I have used the access token in "@activity('GetBearerToken').output.access_token" format. And after which it gives above error. I have all the required access in Azure for Graph. ![97939-image.png][1] So can you please help me on this Thanks in advance. [1]: /api/attachments/97939-image.png?platform=QnA
KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2021-05-19T18:30:23.147+00:00

Hi anonymous user, It will be hard to tell where exactly the issue is without seeing the configuration or the bearer token is expired, but definitely something wrong with Authorization configuration. Have you tried with the same Authorization header in POSTMAN to see if it is working there? Also how are getting the bearer token? Did you hardcode it or passing it dynamically? If you can share few screenshots of your configuration that would help to identify the issue.
In case if you are using the web activity to get the bearer token, please make sure to pass it as below format:
Keval Vankudre 1 Reputation point

2021-05-19T19:43:18.017+00:00

Hello @KranthiPakala-MSFT , Thank you for your response. I was used 3 web activities, 1 to get client creds, 2 to get access token and 3 to fetch graph query results. ('client_id={client-id} &scope=https%3A%2F%2Fgraph.microsoft.com%2F.default &grant_type=client_credentials &client_secret=',activity('GetClientCreds').output.value)* and for the 3rd web activity for graph connection: ![97968-graph3.png][3] and URL is : https://graph.microsoft.com/v1.0/sites/{site-id}/drives/{drive-id}/root/children?$select=name to select file names from all the details. This is how i'm trying to work on it. Do let me know if anymore details are required. [1]: /api/attachments/97958-graph1.png?platform=QnA [2]: /api/attachments/98021-graph2.png?platform=QnA [3]: /api/attachments/97968-graph3.png?platform=QnA
KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2021-05-19T20:20:30.893+00:00

Thanks for the response anonymous user . Looks like you are missing the concatination to form a proper bearer token. Please see below. Also please test the bearer token from POSTMAN to make sure you are using the correct one
Keval Vankudre 1 Reputation point

2021-05-20T16:13:31.26+00:00

Hello @KranthiPakala-MSFT , I did try out above mentioned steps however, it gives me error as mentioned below. ![98383-image.png][1] After looking more into it and going through MS Docs, to perform file actions Admin Consent is not required. So is this what is causing an issue? Microsoft Graph has following access to my registered app. ![98363-image.png][2] What might be the solution here? Thanks for your help [1]: /api/attachments/98383-image.png?platform=QnA [2]: /api/attachments/98363-image.png?platform=QnA

Share via

Connecting Sharepoint Document Library to ADF via managed identity

0 additional answers

Your answer