Sharepoint online file download from SSIS script task

Question

Hi there,

I'm trying to download a file from share point online using SSIS script task.
Referred the code available in below link by calling REST API.

working-with-folders-and-files-with-rest

However code fails with below error.

{"Cannot contact web site 'https://xxxxxx.sharepoint.com/' or the web site does not support SharePoint Online credentials. The response status code is 'Unauthorized'. The response headers are 'X-SharePointHealthScore=3, X-MSDAVEXT_Error=917656; Access+denied.+Before+opening+files+in+this+location%2c+you+must+first+browse+to+the+web+site+and+select+the+option+to+login+automatically., SPRequestGuid=a4d5a99f-70c6-b000-bc56-166d820343b7, request-id=a4d5a99f-70c6-b000-bc56-166d820343b7, MS-CV=n6nVpMZwALC8VhZtggNDtw.0, Strict-Transport-Security=max-age=31536000, SPRequestDuration=49, SPIisLatency=0, MicrosoftSharePointTeamServices=16.0.0.20927, X-Content-Type-Options=nosniff, X-MS-InvokeApp=1; RequireReadOnly, X-MSEdge-Ref=Ref A: 47B2B92132F6450EB1ADC7E2A9CF73E5 Ref B: SYD03EDGE0917 Ref C: 2021-02-10T09:02:27Z, Content-Length=0, Content-Type=text/plain; charset=utf-8, Date=Wed, 10 Feb 2021 09:02:27 GMT, P3P=CP=\"ALL IND DSP COR ADM CONo CUR CUSo IVAo IVDo PSA PSD TAI TELo OUR SAMo CNT COM INT NAV ONL PHY PRE PUR UNI\", X-Powered-By=ASP.NET'."}

just got relevant link about the error message.

https://www.koskila.net/how-to-fix-the-web-site-does-not-support-sharepoint-online-credentials-the-response-status-code-is-unauthorized-error/

I doubt share point admin team would allow legacyauth.

Is there any alternate approach to download file from share point online using SSIS.

Thank you.

Answer 1

Hi @Guganathan Thirumalai ,

You could try to allow access for Apps that don't use modern authentication under Sharepoint online admin center-> access control:

Most current Office mobile and desktop applications use modern authentication (which is an implementation of OAuth2), however there are third-party apps, older Office apps & Visual studio applications (SSDT-SSIS) that uses other authentication methods like basic authentication and forms based authentication. Most of the times ADFS claims rules are setup to block non-modern authentication protocols which blocks all access to O365 except browser-based applications.

Reference：https://techcommunity.microsoft.com/t5/sql-server-support/odata-source-connection-manager-to-pwa-project-web-app-project/ba-p/318986

---

If an Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.