Hi @Yonatan Shlain
Welcome to Microsoft Q&A platform and thanks for posting your question here.
It seems you're facing a complex issue with Azure Data Factory when trying to use a "copy-data" pipeline with a Microsoft 365 Table connector as the source and a storage account as the sink.
Step 1: Understanding the Error with System-Assigned Managed Identity
Your first attempt was to use a system-assigned managed identity for ADF to access the storage account. However, the pipeline failed because the Microsoft 365 connector does not support system-assigned managed identities.
Step 2: Attempting Access with Private Link and ADF Private Endpoint
You then tried to create a private endpoint for ADF to access the storage account. This approach is generally correct and should work if configured properly.
Step 3: Analyzing the Errors Received
The first error with the dfs endpoint suggests a permission issue on the Data Lake Storage Gen2 side, which could be related to the role assignment or the configuration of the private endpoint.
The second error with the blob endpoint indicates a forbidden error, which again points to a possible misconfiguration in permissions or the private endpoint setup.
Step 4: Verifying Permissions and Configuration
Ensure that the Managed Identity of ADF has been granted the "Storage Blob Data Contributor" role on the storage account.
Double-check the private endpoint configuration for both the dfs and blob services of the storage account to ensure they are correctly linked to ADF.
Step 5: Testing with Public Network Access Disabled
Since the same errors occurred with public network access disabled, it confirms that the issue lies within the private network configuration or permission setup.
Step 6: Comparing with a Working Scenario
You mentioned that a simple ADF copy-data pipeline from one storage account to another worked successfully. This suggests that the issue is specific to the combination of Microsoft 365 connector and the storage account.
Step 7: Possible Solutions
Consider using a user-assigned managed identity instead of a system-assigned one, as it offers more flexibility and is often recommended for complex scenarios.
Review the documentation for the Microsoft 365 Table connector and ensure all prerequisites and configurations are met.
Reference:
https://learn.microsoft.com/en-us/azure/data-factory/data-factory-service-identity#overview
https://learn.microsoft.com/en-us/azure/data-factory/connector-dynamics-crm-office-365?tabs=data-factory#supported-capabilities
Hope this helps. Do let us know if you any further queries.