Warning: Cannot Select - Change Tracking or Change Data Capture Must Be Enabled

Issue

Change data capture (CDC) is enabled on the database. However, it's not possible to include some tables in my connection schema. While hovering over the checkbox next to the table name, the following error appears:

Cannot select - Change Tracking or Change Data Capture must be enabled.

Environment

Connector: SQL Server

Resolution

To resolve this issue, do the following:

1. In SQL Server, identify the database role associated with the existing CDC instance using the following query:

   SELECT capture_instance, role_name FROM cdc.change_tables;
   

2. Assign this role to the Fivetran authorizing user using the following query, replacing all placeholders with the relevant values:

   USE [database];
   ALTER ROLE [role_name] ADD MEMBER [database_user_name];
   

Alternatively, you can re-create the CDC instance. For more information, see Step 5 of our SQL Server setup guide.

Cause

This issue occurs when the authorizing user is not a member of the role associated with the CDC instance. Without this role, we lack permission to access CDC metadata and change tables. As a result, it's not possible to include these tables in your connection schema.