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:
- Verify that CDC is enabled on the table. To learn more, see How Can I Check Whether I Have Enabled CDC?
- If CDC is not enabled on the table, re-create the CDC instance to include the new table. For more information, see Step 6 of our SQL Server setup guide.
- 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;
- 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];
Cause
This issue may occur for either of the following reasons:
- CDC is not enabled on the applicable table.
- 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.