SharePoint list as OData Source forbidden error

[jw9jwang]

I want to connect to SharePoint list as OData source in SSIS, But it always occurs an error and the error message as the following:

===================================

Test connection failed

===================================

The remote server returned an error: (403) Forbidden. (Microsoft.SqlServer.IntegrationServices.ODataConnectionManager)

------------------------------
Program Location:

at Microsoft.SqlServer.IntegrationServices.OData.ShimSharePointOnlineCredentials.GetAuthenticationCookie(Uri uri)
at Microsoft.SqlServer.IntegrationServices.OData.UI.ODataConnectionManagerForm.GetWebRequest()
at Microsoft.SqlServer.IntegrationServices.OData.UI.ODataConnectionManagerForm.TestConnectionMiddle(Object callback)

[#2]

@jw9jwang Hi, did you ever get a solution for this. I am facing a similar issue.

===================================

Test connection failed

===================================

The remote server returned an error: (403) Forbidden. (System)

------------------------------
Program Location:

at System.Net.HttpWebRequest.GetResponse()
at Microsoft.SqlServer.IntegrationServices.OData.UI.ODataConnectionManagerForm.TestConnectionMiddle(Object callback)

[#3 · Accepted solution]

hi @AmosK1855 

I am not sure have you encountered the same situation as me; 

I have solved by connect to  our tenant M365 admin  to enable legacyauthprotocol; Refer to the following picture which provide by Microsoft Engineer:

If unfortunately your M365 admin not cooperate well, You also call have a try with Graph API to write code to achieve this goal, which also need app registrations in AAD

FYI : https://docs.microsoft.com/en-us/graph/api/list-get?view=graph-rest-1.0&tabs=http

Hope it's can help u;