question

Uzi7117-1247 avatar image
0 Votes"
Uzi7117-1247 asked Monalv-msft edited

SharePoint Online-Reading/Access Lists-Using SSIS -OData Connection- Error 401 Unauthorized

Hi

I am trying to connect with Lists data on Sharepoint online. I am working in SSIS -Visual Studio 2017 Odata Connector
I have installed SDK-2013 as well and followed all the instruction on blogs on this topic. But I am getting error , TITLE: OData Connection Manager Editor
Test connection failed. The remote server returned an error: (401) Unauthorized. (System) Thank you so much for help

70293-odataconnection.png


office-sharepoint-onlinesql-server-integration-services
odataconnection.png (38.1 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello @Uzi7117-1247

Your issue is more related to SQL, I will add tag "sql-server-integration-service".

Thank you for your support and understanding.

Have a nice day!

Thanks,
Echo Du

1 Vote 1 ·
Monalv-msft avatar image
0 Votes"
Monalv-msft answered Monalv-msft edited

Hi @Uzi7117-1247 ,

The remote server returned an error: (401) Unauthorized.

Please check if the service document location is correct and if the username and password are correct in Odata Connection Manager.

May I know if you can use Windows Authentication?

Please refer to the following links:
Tutorial: Using the OData Source
SSIS ODATA Connection issue
Using the OData Source in SQL Server Integration Services

Best regards,
Mona


If the 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.



· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Mona, thank you for reply. Service document location and username/pw is correct. As I mentioned that I can access this location via browser with same credentials/location address. I tried using windows authentication and got the same error. I also spent a lot of time on the documents /blogs already on this topic. Apparently everything is fine but I get 401 unauthorized error . Thanks

0 Votes 0 ·

Hi @Uzi7117-1247,

Please check if the user to execute ssis package has full permission to access to the share point link.

Best Regards,
Mona

0 Votes 0 ·

Hi mona,

I'm running the ssis package in visual studio on my local PC. I think is then running under my account?

0 Votes 0 ·
SteveRely-2822 avatar image
0 Votes"
SteveRely-2822 answered

Hi,

I'm trying to do the same and get also the unauthorized error.
I can access the sharepoint url with no problem from the webbrowser. Credentials & link are correct

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

trevorseward avatar image
0 Votes"
trevorseward answered

Due to use of Modern Authentication, this will fail against SPO. I'm not aware of an SSIS connector that supports modern authentication or Client ID/Certificate or Secret.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SteveRely-2822 avatar image
0 Votes"
SteveRely-2822 answered

Solved the problem on our side.

Indeed, issue is Modern Authentication isn't supported for SSDT-SSIS and apparently SharePoint blocks those kinds of old application access by default.

If you block or limit access from unmanaged devices, we recommend also blocking access from apps that don't use modern authentication. Some third-party apps and versions of Office prior to Office 2013 don't use modern authentication and can't enforce device-based restrictions. This means they allow users to bypass conditional access policies that you configure in Azure. In the new SharePoint admin center, on the Access control page, select Apps that don't use modern authentication, select Block access, and then select Save.

You have to change this setting in de SharePoint Admin portal

Hope this will work for you.

Some references
- https://techcommunity.microsoft.com/t5/sql-server-support/odata-source-connection-manager-to-pwa-project-web-app-project/ba-p/318986
- https://docs.microsoft.com/en-us/sharepoint/control-access-from-unmanaged-devices


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.