Share via

OData Service to Sharepoint - Cannot acquire a managed connection from the run-time connection manager.

Vasantha Vasantha 16 Reputation points
2021-02-01T19:15:29.677+00:00

Hello,

I am trying to get the sharepoint list using oData Source service using Basic Authentication. The connection test okay and i can preview the data.

Connection String

https://XXXXXXXXXXXXXXXX/_vti_bin/listdata.svc/

But when i run the package, it will error with following error message

Information: 0x4004300A at LoadData, SSIS.Pipeline: Validation phase is beginning.
Error: 0xC020801F at LoadData, OData Source [23]: Cannot acquire a managed connection from the run-time connection manager.
Error: 0xC0047017 at LoadData, SSIS.Pipeline: OData Source failed validation and returned error code 0xC020801F.
Error: 0xC004700C at LoadData, SSIS.Pipeline: One or more component failed validation.
Error: 0xC0024107 at LoadData: There were errors during task validation.

Any help is greatly appreciated!

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,593 questions

9 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. rdkn 41 Reputation points
    2021-05-12T03:07:21.013+00:00

    Hello - I have done some digging myself, and although I'm still confirming the theory, most facts suggest that this is due to a planned obsoletion of TLS 1.0 and 1.1:
    https://learn.microsoft.com/en-us/microsoft-365/compliance/prepare-tls-1.2-in-office-365?view=o365-worldwide

    I am using the below article to enforce use of TLS 1.2:
    https://learn.microsoft.com/en-us/archive/blogs/dataaccesstechnologies/tls-issue-with-ssis-package-while-accessing-odata-source-like-dynamics-ax-online

    Will post back here once I'm certain it worked

    8 people found this answer helpful.
  2. Eric Chau 21 Reputation points
    2021-05-31T08:58:35.057+00:00

    @rdkn 's solution is exactly what I needed to get the SSIS packages running properly again.

    1. Originally the OData task was failing validation with the same cryptic error message.
    2. It started at around mid-april... similar to other posters above
    3. Our machines already have the lastest .NET framework installed
    4. Registry entries are what fixed our issues

    from the link @rdkn has provided

    Enforce TLS 1.2 on your machine through registry settings. In an elevated command prompt run the following commands: 

    reg add HKLM\SOFTWARE\Microsoft\.NETFramework\v4.0.30319 /v SchUseStrongCrypto /t REG_DWORD /d 1 /reg:64  
reg add HKLM\SOFTWARE\Microsoft\.NETFramework\v4.0.30319 /v SchUseStrongCrypto /t REG_DWORD /d 1 /reg:32
    4 people found this answer helpful.
  3. Vasantha Vasantha 16 Reputation points
    2021-02-02T16:30:09.107+00:00

    @Monalv-MSFT

    1.May I know if you set expression for the connection string in OData Source Connection?
    63163-connectionstrings.png

    2.Could you please share the screenshots of your OData Source and OData Source Connection?
    63143-odatasourceconnection.png

    3.Please refer to How to configure OData SSIS Connection for SharePoint Online.
    This is the exact document i used to configure the Odata SSIS connection

    Thanks for help!

  4. Vasantha Vasantha 16 Reputation points
    2021-02-04T17:50:01.983+00:00

    Hi, @Monalv-MSFT

    1.May I know if you set expression for the connection string in OData Source Connection?
    Service Document Url=https://XXXXXXX/_vti_bin/listdata.svc/;Include Atom Elements=Auto;Include Expanded Entities=False;Integrated Security=SSPI;Persist Security Info=False;Time Out=600;Schema Sample Size=25;Retry Count=5;Retry Sleep=100;Keep Alive=False;Max Received Message Size=4398046511104

    2.Please set DelayValidation as True. - Yes i tried that too.
    64143-capture.png

    Thanks

    0 comments
  5. Monalv-MSFT 5,901 Reputation points
    2021-02-05T06:48:35.243+00:00

    Hi @Vasantha Vasantha ,

    Error: 0xC020801F at LoadData, OData Source [23]: Cannot acquire a managed connection from the run-time connection manager.

    Please try to test the Url value in parameter in OData Connection Manager.

    Please check if you have permissions to access to OData Source.

    Best Regards,
    Mona

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.