SOLVED

SharePoint list as OData Source forbidden error

Copper Contributor

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 Replies

@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)

 

 

best response confirmed by jw9jwang (Copper Contributor)
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:

jw9jwang_0-1645092980074.png

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;

1 best response

Accepted Solutions
best response confirmed by jw9jwang (Copper Contributor)
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:

jw9jwang_0-1645092980074.png

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;

View solution in original post