question

JDSAH-3103 avatar image
0 Votes"
JDSAH-3103 asked CaseyYangMSFT-4714 commented

SSID - OData connection to SharePoint Online failing at Data Conversion/Validate stage

I am encountering sudden issues with a SSIS packages OData connection to our SharePoint online tenant, from just one particular server on our domain. (3 servers are configured to call, but only 1 is failing to acquire a connection)
It's attempting to connect to a listdata.svc source - i.e https://mycompany.sharepoint.com/sites/mysite/_vti_bin/listdata.svc/

Researching many articles and forums, and have ruled out issues with:

  • SSL/TLS configuration + reg keys for strong crypto/DefaultSecureProtocols.

  • incorrect/wrong credentials

  • Package config - e.g. DelayValidation flag - 32/64bit flags

  • SSISDB space allocation and availability.

  • Azure Security defaults settings + Conditional Access

  • MSOID cname record has been added


Evidence suggests it's not validating or even able to initiate the connection due to a failed validation.

Running SSIS package in Verbose mode, we're seeing the following dump below, and seems the SSIS pipeline is failing at the Data Conversion : Validate stage.

Similarly the errors on the GUI show OData - sharepoint failed validation and returned error code 0xC020801F


See below - failing validation within 1 second indicates it's not even attempting the external connection to SharePoint Online.

We have configured the same SSIS package on another server which connects without issue.
What is failing validation at this data conversion stage of the SSIS OData connection?
Or the issue lay elsewhere in the connection?


 [D:0]<<<CRingBuffer<TRingBufferRecord>>>> ( @ 6A135000 )
 [M:1]   Ring buffer entry:     (*pRecord)
 [D:2]      <<<CRingBufferLogging::RingBufferLoggingRecord>>> ( @ ACD27040 )
 [E:3]         Time Stamp: 2021-09-17 14:30:30.410    (szTimeStamp)
 [E:3]         Thread ID: 10684    (ThreadID)
 [E:3]         Event Name: OnPreValidate    (EventName)
 [E:3]         Source Name:     (SourceName)
 [E:3]         Source ID:     (SourceID)
 [E:3]         Execution ID:     (ExecutionGUID)
 [E:3]         Data Code: 0    (DataCode)
 [E:3]         Description:     (MessageText)
 [M:1]   Ring buffer entry:     (*pRecord)
 [D:2]      <<<CRingBufferLogging::RingBufferLoggingRecord>>> ( @ ACD279E8 )
 [E:3]         Time Stamp: 2021-09-17 14:30:30.435    (szTimeStamp)
 [E:3]         Thread ID: 10684    (ThreadID)
 [E:3]         Event Name: OnPreValidate    (EventName)
 [E:3]         Source Name:     (SourceName)
 [E:3]         Source ID:     (SourceID)
 [E:3]         Execution ID:     (ExecutionGUID)
 [E:3]         Data Code: 0    (DataCode)
 [E:3]         Description:     (MessageText)
 [M:1]   Ring buffer entry:     (*pRecord)
 [D:2]      <<<CRingBufferLogging::RingBufferLoggingRecord>>> ( @ ACD28390 )
 [E:3]         Time Stamp: 2021-09-17 14:30:30.475    (szTimeStamp)
 [E:3]         Thread ID: 10684    (ThreadID)
 [E:3]         Event Name: OnInformation    (EventName)
 [E:3]         Source Name:     (SourceName)
 [E:3]         Source ID:     (SourceID)
 [E:3]         Execution ID:     (ExecutionGUID)
 [E:3]         Data Code: 1074016266    (DataCode)
 [E:3]         Description: Validation phase is beginning.
     (MessageText)
 [M:1]   Ring buffer entry:     (*pRecord)
 [D:2]      <<<CRingBufferLogging::RingBufferLoggingRecord>>> ( @ ACD28D38 )
 [E:3]         Time Stamp: 2021-09-17 14:30:30.480    (szTimeStamp)
 [E:3]         Thread ID: 10684    (ThreadID)
 [E:3]         Event Name: OnProgress    (EventName)
 [E:3]         Source Name:     (SourceName)
 [E:3]         Source ID:     (SourceID)
 [E:3]         Execution ID:     (ExecutionGUID)
 [E:3]         Data Code: 0    (DataCode)
 [E:3]         Description: Validating    (MessageText)
 [M:1]   Ring buffer entry:     (*pRecord)
 [D:2]      <<<CRingBufferLogging::RingBufferLoggingRecord>>> ( @ ACD296E0 )
 [E:3]         Time Stamp: 2021-09-17 14:30:30.490    (szTimeStamp)
 [E:3]         Thread ID: 10684    (ThreadID)
 [E:3]         Event Name: OnPipelinePreComponentCall    (EventName)
 [E:3]         Source Name:     (SourceName)
 [E:3]         Source ID:     (SourceID)
 [E:3]         Execution ID:     (ExecutionGUID)
 [E:3]         Data Code: 0    (DataCode)
 **[E:3]         Description: Data flow engine will call a component method. : 2 : Data Conversion : AcquireConnections    (MessageText)**
 [M:1]   Ring buffer entry:     (*pRecord)
 [D:2]      <<<CRingBufferLogging::RingBufferLoggingRecord>>> ( @ ACD2A088 )
 [E:3]         Time Stamp: 2021-09-17 14:30:30.490    (szTimeStamp)
 [E:3]         Thread ID: 10684    (ThreadID)
 [E:3]         Event Name: OnPipelinePostComponentCall    (EventName)
 [E:3]         Source Name:     (SourceName)
 [E:3]         Source ID:     (SourceID)
 [E:3]         Execution ID:     (ExecutionGUID)
 [E:3]         Data Code: 0    (DataCode)
 **[E:3]         Description: Data flow engine has finished a call to a component method. : 2 : Data Conversion : AcquireConnections : 132763590304903084 :** 132763590304903084    (MessageText)
 [M:1]   Ring buffer entry:     (*pRecord)
 [D:2]      <<<CRingBufferLogging::RingBufferLoggingRecord>>> ( @ ACD2AA30 )
 [E:3]         Time Stamp: 2021-09-17 14:30:30.490    (szTimeStamp)
 [E:3]         Thread ID: 10684    (ThreadID)
 [E:3]         Event Name: OnPipelinePreComponentCall    (EventName)
 [E:3]         Source Name:     (SourceName)
 [E:3]         Source ID:     (SourceID)
 [E:3]         Execution ID:     (ExecutionGUID)
 [E:3]         Data Code: 0    (DataCode)
 **[E:3]         Description: Data flow engine will call a component method. : 2 : Data Conversion : Validate    (MessageText)**
 [M:1]   Ring buffer entry:     (*pRecord)
 [D:2]      <<<CRingBufferLogging::RingBufferLoggingRecord>>> ( @ ACD2B3D8 )
 [E:3]         Time Stamp: 2021-09-17 14:30:30.490    (szTimeStamp)
 [E:3]         Thread ID: 10684    (ThreadID)
 [E:3]         Event Name: OnPipelinePostComponentCall    (EventName)
 [E:3]         Source Name:     (SourceName)
 [E:3]         Source ID:     (SourceID)
 [E:3]         Execution ID:     (ExecutionGUID)
 [E:3]         Data Code: 0    (DataCode)
 **[E:3]         Description: Data flow engine has finished a call to a component method. : 2 : Data Conversion : Validate : 132763590304903084 : 132763590304903084**    (MessageText)
 [M:1]   Ring buffer entry:     (*pRecord)
 [D:2]      <<<CRingBufferLogging::RingBufferLoggingRecord>>> ( @ ACD2BD80 )
 [E:3]         Time Stamp: 2021-09-17 14:30:30.490    (szTimeStamp)
 [E:3]         Thread ID: 10684    (ThreadID)
 [E:3]         Event Name: User:PipelineComponentTime    (EventName)
 [E:3]         Source Name:     (SourceName)
 [E:3]         Source ID:     (SourceID)
 [E:3]         Execution ID:     (ExecutionGUID)
 [E:3]         Data Code: 0    (DataCode)
 **[E:3]         Description: The Data Conversion spent 0 milliseconds in Validate.    (MessageText)**
 [M:1]   Ring buffer entry:     (*pRecord)
 [D:2]      <<<CRingBufferLogging::RingBufferLoggingRecord>>> ( @ ACD2C728 )
 [E:3]         Time Stamp: 2021-09-17 14:30:30.490    (szTimeStamp)
 [E:3]         Thread ID: 10684    (ThreadID)
 [E:3]         Event Name: OnPipelinePreComponentCall    (EventName)
 [E:3]         Source Name:     (SourceName)
 [E:3]         Source ID:     (SourceID)
 [E:3]         Execution ID:     (ExecutionGUID)
 [E:3]         Data Code: 0    (DataCode)
 **[E:3]         Description: Data flow engine will call a component method. : 2 : Data Conversion : ReleaseConnections    (MessageText)**
 [M:1]   Ring buffer entry:     (*pRecord)
 [D:2]      <<<CRingBufferLogging::RingBufferLoggingRecord>>> ( @ ACD2D0D0 )
 [E:3]         Time Stamp: 2021-09-17 14:30:30.495    (szTimeStamp)
 [E:3]         Thread ID: 10684    (ThreadID)
 [E:3]         Event Name: OnPipelinePostComponentCall    (EventName)
 [E:3]         Source Name:     (SourceName)
 [E:3]         Source ID:     (SourceID)
 [E:3]         Execution ID:     (ExecutionGUID)
 [E:3]         Data Code: 0    (DataCode)
 **[E:3]         Description: Data flow engine has finished a call to a component method. : 2 : Data Conversion : ReleaseConnections : 132763590304903084 :** 132763590304903084    (MessageText)
 [M:1]   Ring buffer entry:     (*pRecord)
 [D:2]      <<<CRingBufferLogging::RingBufferLoggingRecord>>> ( @ ACD2DA78 )
 [E:3]         Time Stamp: 2021-09-17 14:30:30.495    (szTimeStamp)
 [E:3]         Thread ID: 10684    (ThreadID)
 [E:3]         Event Name: OnProgress    (EventName)
 [E:3]         Source Name:     (SourceName)
 [E:3]         Source ID:     (SourceID)
 [E:3]         Execution ID:     (ExecutionGUID)
 [E:3]         Data Code: 33    (DataCode)
 [E:3]         Description: Validating    (MessageText)
 [M:1]   Ring buffer entry:     (*pRecord)
 [D:2]      <<<CRingBufferLogging::RingBufferLoggingRecord>>> ( @ ACD2E420 )
 [E:3]         Time Stamp: 2021-09-17 14:30:30.495    (szTimeStamp)
 [E:3]         Thread ID: 10684    (ThreadID)
 [E:3]         Event Name: OnPipelinePreComponentCall    (EventName)
 [E:3]         Source Name:     (SourceName)
 [E:3]         Source ID:     (SourceID)
 [E:3]         Execution ID:     (ExecutionGUID)
 [E:3]         Data Code: 0    (DataCode)
 **[E:3]         Description: Data flow engine will call a component method. : 116 : OData - Sharepoint : AcquireConnections    (MessageText)**
 [M:1]   Ring buffer entry:     (*pRecord)
 [D:2]      <<<CRingBufferLogging::RingBufferLoggingRecord>>> ( @ ACD2EDC8 )
 [E:3]         Time Stamp: 2021-09-17 14:32:58.394    (szTimeStamp)
 [E:3]         Thread ID: 10684    (ThreadID)
 [E:3]         Event Name: OnError    (EventName)
 [E:3]         Source Name:     (SourceName)
 [E:3]         Source ID:     (SourceID)
 [E:3]         Execution ID:     (ExecutionGUID)
 [E:3]         Data Code: -1071611873    (DataCode)
 **[E:3]         Description: Cannot acquire a managed connection from the run-time connection manager.**
     (MessageText)
 [M:1]   Ring buffer entry:     (*pRecord)
 [D:2]      <<<CRingBufferLogging::RingBufferLoggingRecord>>> ( @ ACD2F770 )
 [E:3]         Time Stamp: 2021-09-17 14:32:58.394    (szTimeStamp)
 [E:3]         Thread ID: 10684    (ThreadID)
 [E:3]         Event Name: OnInformation    (EventName)
 [E:3]         Source Name:     (SourceName)
 [E:3]         Source ID:     (SourceID)
 [E:3]         Execution ID:     (ExecutionGUID)
 [E:3]         Data Code: 1073828099    (DataCode)
 [E:3]         Description: Event code, 0xC020801F, triggered generation of debug dump files in the folder "C:\Program Files\Microsoft SQL Server\140\Shared\ErrorDumps\".


Many thanks,

JD


















office-sharepoint-onlinesql-server-integration-servicessharepoint-dev
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.

1 Answer

CaseyYangMSFT-4714 avatar image
0 Votes"
CaseyYangMSFT-4714 answered CaseyYangMSFT-4714 commented

Hi @JDSAH-3103,

Here is a bolg about OData Service to Sharepoint error, you could try the following steps:

1.Log in to https://portal.azure.com/#home
2.Select “Azure Active Directory”
3.Click Properties > Manage Security Defaults
4.Turn off the Enable Security Defaults > and Choose “Other” and add some appropriate comments or choose any other relevant option.
5.Press save and wait for few minutes to sync.

And here is a similar issue for reference: OData Service to Sharepoint - Cannot acquire a managed connection from the run-time connection manager

Note: Microsoft is providing this information as a convenience to you. The sites are not controlled by Microsoft. Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. Please make sure that you completely understand the risk before retrieving any suggestions from the above link.


If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

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
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 @CaseyYangMSFT-4714

thanks for your answer

Those articles linked are some we've already seen unfortunately, and we've checked the suggestions given within, such as:

  • Azure Security Defaults

  • Credentials/permissions - the data source tests and previews without problems.

  • SchUseStrongCrypto reg keys applied to processing connection server.

Everything is configured as suggested, and still encounter problems as described.

Thanks,

0 Votes 0 ·

Hi @JDSAH-3103,

Due to I am not able to reproduce this issue, I would suggest you open a new service request in Microsoft 365 admin center.

0 Votes 0 ·