Native CDC

Set up change data capture on Azure SQL Database and capture its changes with SQL Flow from the cdc change tables.

On Azure SQL, SQL Flow reads the change tables that the database's change data capture fills under the cdc schema. It is the only capture mode for an Azure SQL source, because Azure SQL exposes no raw transaction log.

How it works

Azure SQL's CDC capture reads the database's transaction log and writes each change of a CDC-enabled table to that table's change table, cdc.<capture_instance>_CT. The agent connects to the database over TLS and reads those change tables, so lag follows the CDC capture's scan interval.

SQL Flow holds no log and doesn't create capture instances. CDC's cleanup removes change rows older than its retention period. A pipeline can resume only while the change rows it needs are still there; after a longer pause, the table needs a reload.

Requirements

Setup

1. Connect to the database as a member of db_owner, and enable CDC on it.

   EXEC sys.sp_cdc_enable_db;

2. Create a capture instance for each selected table. Replace <schema_name> and <table_name>.

   EXEC sys.sp_cdc_enable_table
       @source_schema = N'<schema_name>',
       @source_name = N'<table_name>',
       @role_name = NULL,
       @supports_net_changes = 0;

3. Create the database user for SQL Flow and grant read access. For a Microsoft Entra identity, such as a managed identity or a service principal, create the user from the external provider. Replace <user_name> and <schema_name>. If a capture instance was created with a gating role (@role_name), also add the user to that role.

   CREATE USER [<user_name>] FROM EXTERNAL PROVIDER;
   -- or, for a SQL login: CREATE USER [<user_name>] WITH PASSWORD = N'<password>';
   GRANT SELECT ON SCHEMA::[<schema_name>] TO [<user_name>];
   GRANT SELECT ON SCHEMA::cdc TO [<user_name>];
   GRANT SELECT ON sys.system_internals_partition_columns TO [<user_name>];

4. Allow the capture agent's outbound address through the server's firewall.

5. In the pipeline wizard, choose Azure SQL on the Source step. Capture mode shows Native CDC.

6. Enter the server, the database, and the Authentication method:

   • SQL login, with a username and password.
   • Microsoft Entra managed identity, when the agent runs on an Azure resource. Leave Client ID (user-assigned identity) blank for the system-assigned identity.
   • Microsoft Entra service principal, with the Client ID and, in Signs in with, a Client secret or a Certificate file on the agent host.
   • Microsoft Entra workload identity, configured on the agent host.
   • Microsoft Entra password or Microsoft Entra integrated.

7. Select Test connection & list tables. Every start checks CDC on the database, the user's permissions, computed columns and text code pages, and names every unmet requirement at once, with the GRANT that fixes a missing permission. A selected table without a capture instance is named when the capture first reads it.

For unattended agents, prefer a managed identity or a service principal. Microsoft deprecates Entra password sign-in, and it is refused for accounts that require MFA.

Serverless databases

A serverless database pauses when idle and resumes on the first connection. While it resumes, it refuses connections with error 40613, 42108 or 42109. SQL Flow treats these as transient azure-paused faults, and the automatic restart retries the pipeline until the database is back.

Encrypted data

• TDE. Supported. CDC fills the change tables from the decrypted log.

• Always Encrypted columns. Refused. A selected table with an Always Encrypted column is refused with:

  always_encrypted_column: SQL Server capture refuses selected column <schema>.<table>.<column> because it is Always Encrypted; decryption and ciphertext pass-through are not supported.

• Connections. The agent always encrypts its connection and validates the server certificate. An Azure SQL connection has no encryption setting. See Azure SQL: Encrypted data for a connection that still carries the retired option trustServerCertificate.

Pros and cons

Pros

• The only way to capture from Azure SQL Database, and it needs no access to the host.
• Supports Microsoft Entra managed identities and service principals.
• Works on databases encrypted with TDE.
• Azure SQL does the log reading; SQL Flow only queries the change tables.

Cons

• Lag follows the CDC capture's scan interval.
• CDC's change tables use database storage and compute, which count against the database's resource limits.
• Tables without a primary key are refused.

Limitations

• Typed xml. A column of xml typed by an XML schema collection is refused. Untyped xml is supported.
• Primary keys. A table without a primary key is refused, even when it has a unique index.
• Computed columns. A capture instance that includes a computed column is refused with native_cdc_computed_column.
• Text code pages. A char, varchar or text column whose collation uses a different code page than the database's is refused with native_capture_text_codepage_mismatch. Use nchar, nvarchar or ntext, or matching code pages.
• CLR user-defined types. A column of a CLR user-defined type stops the capture by name when it first reads the table, with NativeCdcUnsupportedProviderType. hierarchyid, geography and geometry are supported.
• sql_variant. Captured, but SQL Server and Azure SQL targets refuse a sql_variant column, because CDC returns each value without its base type. The Prepare step names the column; deselect the table to continue.
• Column sets. CDC can't be enabled on a table with a column set (sys.sp_cdc_enable_table fails with error 22854). Drop the column set to capture the table; its sparse columns stay and are captured.
• TRUNCATE TABLE. Azure SQL refuses TRUNCATE TABLE on a table enabled for CDC.

Troubleshooting

CDC not enabled on the database

CDC enabled on database (EXEC sys.sp_cdc_enable_db)

Cause: CDC isn't enabled on the database.

Fix: as a member of db_owner, run EXEC sys.sp_cdc_enable_db;.

No capture instance for a tracked table

Native CDC capture is incomplete for tracked table '<table>': no capture instance exists. ...

Cause: a selected table has no capture instance.

Fix: run sys.sp_cdc_enable_table for the table, wait for the first capture scan, then reload the table.

CDC retention gap on a tracked table

Native CDC retention gap for tracked table '<table>' (capture instance '<name>'): ...

Cause: CDC's cleanup removed change rows the pipeline hadn't read, for example during a long pause.

Fix: reload the table, and raise the cleanup retention with sys.sp_cdc_change_job @job_type = N'cleanup', @retention = <minutes> so it covers your longest pause.

Column and database code pages differ

native_capture_text_codepage_mismatch: Native CDC refuses <table>.<column> because column collation <collation> uses code page <n> while database collation <collation> uses <n>; ...

Cause: CDC converts non-national text through the database code page, which can lose non-ASCII characters.

Fix: change the column to nvarchar (or nchar, ntext), or give it a collation with the database's code page.

The pipeline restarts with azure-paused

Cause: the serverless database was paused and is resuming.

Fix: none needed. The automatic restart continues the pipeline when the database is online. If the pipeline is parked after repeated restarts, check the database's state in the Azure portal, then resume the pipeline.

See also

• Azure SQL sources
• SQL Server Native CDC
• Database credentials and secret references
• Automatic restart