Troubleshooting Log-Based CDC in Microsoft Fabric Copy Job

Troubleshooting Log-Based CDC in Microsoft Fabric Copy Job — Here's What Fixed It!

If you're working with CDC (Change Data Capture) in Microsoft Fabric's Copy Job to load data into a Lakehouse from SQL Server, you might hit this frustrating error:

"Log-based CDC is not enabled on the table"

…even when you've confirmed CDC is enabled on both the database AND the table, and the CDC article is clearly visible. I was stuck on this with SQL Server 2019.

Root Cause & Fix:

The issue had nothing to do with SQL Server configuration or permissions — it was about the connection type in Fabric.

When I created the connection to my SQL Server, it was set up as a Cloud connection using OAuth 2.0 (my personal AD account). Even though my account had full access to SQL Server, Fabric's log-based CDC does NOT work with Cloud connections via OAuth 2.0.

The fix? I was granted the Connection Creator role on the On-Premises Data Gateway. Once I recreated the connection through the gateway and used it in the Copy Job — no more error! Log-based CDC worked perfectly. 🎉

💡 Key Takeaways:
👉 Log-based CDC in Fabric Copy Job does NOT work with Cloud (OAuth 2.0) connections
👉 You must use an On-Premises/Cloud Data Gateway connection for CDC to work
👉 Make sure you have the Connection Creator/Admin role on the gateway
👉 Even with full SQL Server permissions on your AD account, the connection type is what matters
👉 CDC-based Copy Jobs with Merge (Preview) in Fabric are a game changer for incremental loads!

This one took a while to figure out — hope it saves someone else the headache! 🙌

#MicrosoftFabric #DataEngineering #CDC #Lakehouse #SQLServer #CopyJob #DataIntegration #Azure #FabricCommunity