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

Uzi 7117 1 Reputation point
2021-02-20T21:47:22.763+00:00

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

SharePoint
SharePoint
A group of Microsoft Products and technologies used for sharing and managing content, knowledge, and applications.
9,559 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,449 questions

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Monalv-MSFT 5,891 Reputation points
    2021-02-22T06:37:59.067+00:00

    Hi @Uzi 7117 ,

    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.

  2. Steve Rely 1 Reputation point
    2021-02-24T10:00:42.593+00:00

    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

    0 comments
  3. Trevor Seward 11,681 Reputation points
    2021-02-24T15:50:57.41+00:00

    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.

    0 comments
  4. Steve Rely 1 Reputation point
    2021-02-24T16:13:06.517+00:00

    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

    0 comments