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

Question

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!

Answer 1

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

Answer 2

@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  

Answer 3

@Monalv-MSFT

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

2.Could you please share the screenshots of your OData Source and OData Source Connection?

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!

Answer 4

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.

Thanks

Answer 5

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