Lakeflow Connect SQL Server: Why the Database Setup Step Keeps Failing

Even after the utility script runs successfully, the ingestion user can still lack permissions that cause the setup step to fail. The required grants span three categories: system catalog access, change detection access, and table-level SELECT.

For system catalog access, the ingestion user needs SELECT on sys.databases, sys.schemas, sys.tables, sys.columns, sys.key_constraints, sys.foreign_keys, sys.check_constraints, sys.default_constraints, sys.change_tracking_tables, sys.change_tracking_databases, sys.objects, sys.triggers, sys.indexes, and sys.index_columns. It also needs EXECUTE on sp_tables, sp_columns_100, sp_pkeys, and sp_statistics_100, plus VIEW DATABASE PERFORMANCE STATE.

For CDC specifically, the user needs SELECT on the entire cdc schema. On on-premises and RDS instances, it also needs VIEW SERVER STATE to query sys.dm_server_services, which Lakeflow uses to verify SQL Server Agent is running. If Agent is stopped, CDC changes do not get captured, and the connector will refuse to proceed.

The utility script's lakeflowSetupChangeTracking and lakeflowSetupChangeDataCapture procedures grant CDC and CT-specific permissions automatically, but only if the @User parameter is set. If you ran the procedures without specifying @User, the permissions were not granted. Run lakeflowFixPermissions to fill the gap:

EXEC dbo.lakeflowFixPermissions
    @User = 'your_ingestion_user',
    @Tables = 'ALL';

This grants system-level and table-level SELECT permissions. It is safe to run repeatedly — the procedure is idempotent. After running it, verify the setup step again. If you are targeting specific schemas rather than all tables, replace 'ALL' with 'SCHEMAS:Sales,HR' or a comma-separated table list to follow least-privilege principles.

This is the most common false confidence. An engineer enables change tracking at the database level and assumes tables inherit it. They do not. Change tracking must be explicitly enabled on each table, and CDC must be explicitly enabled with a capture instance per table.

To check database-level change tracking:

SELECT
    d.name AS DatabaseName,
    ctd.is_auto_cleanup_on,
    ctd.retention_period,
    ctd.retention_period_units_desc
FROM sys.change_tracking_databases ctd
INNER JOIN sys.databases d ON ctd.database_id = d.database_id
WHERE d.name = DB_NAME();

To check which tables actually have change tracking enabled:

SELECT
    SCHEMA_NAME(t.schema_id) + '.' + t.name AS TableName,
    ct.is_track_columns_updated_on,
    ct.begin_version,
    ct.cleanup_version
FROM sys.change_tracking_tables ct
INNER JOIN sys.tables t ON ct.object_id = t.object_id;

If your target tables are missing from this result set, the setup step will fail regardless of database-level configuration. The fix is straightforward — re-run the setup procedure with the correct table list:

EXEC dbo.lakeflowSetupChangeTracking
    @Tables = 'Sales.Orders,Production.Products',
    @User = 'your_ingestion_user',
    @Retention = '2 DAYS';

The @Retention parameter controls how long change tracking history is preserved before auto-cleanup removes it. Two days is the Databricks recommendation for most workloads, but if your ingestion pipeline runs infrequently or experiences extended downtime, increase it. If the retention window expires before the pipeline reads the changes, Lakeflow triggers a full refresh of that table — an expensive operation on large datasets that also resets downstream watermarks.

For CDC, check table-level enablement by querying cdc.change_tables. If a table has is_cdc_enabled = 1 at the database level but no entry in cdc.change_tables, the capture instance was never created. Run lakeflowSetupChangeDataCapture targeting those specific tables.

The setup wizard's error messages are improving with each Databricks runtime release, but as of early 2026 they still collapse multiple failure modes into generic messages. The most reliable approach is to run the full verification suite before attempting the setup step.

A pre-flight checklist in SQL:

-- 1. Confirm utility script version
SELECT dbo.lakeflowUtilityVersion_1_5() AS UtilityVersion;

-- 2. Confirm platform detection
SELECT dbo.lakeflowDetectPlatform() AS Platform;

-- 3. Confirm database-level CT
SELECT * FROM sys.change_tracking_databases
WHERE database_id = DB_ID();

-- 4. Confirm table-level CT
SELECT SCHEMA_NAME(t.schema_id) + '.' + t.name
FROM sys.change_tracking_tables ct
JOIN sys.tables t ON ct.object_id = t.object_id;

-- 5. Confirm CDC database-level
SELECT is_cdc_enabled FROM sys.databases
WHERE database_id = DB_ID();

-- 6. Confirm CDC capture instances
SELECT capture_instance FROM cdc.change_tables;

-- 7. Confirm ingestion user permissions
EXEC dbo.lakeflowFixPermissions
    @User = 'your_ingestion_user',
    @Tables = 'ALL';

If every query returns expected results and lakeflowFixPermissions completes without error, the setup step should pass. If it still fails, check the gateway cluster driver logs — download them from the pipeline details page and search for INGESTION_GATEWAY_CONNECTION_ERROR or INGESTION_GATEWAY_INTERNAL_ERROR. The former points to network-level issues (firewall rules, incorrect hostname or port), while the latter requires log-level inspection for stack traces.

For teams running multiple Lakeflow Connect pipelines across different SQL Server instances, MetricSign groups ingestion failures by root cause — distinguishing permission errors from connectivity timeouts from CDC configuration gaps — so the on-call engineer sees one incident per underlying problem rather than one alert per failed pipeline. This matters when a single Active Directory password rotation breaks ingestion across twelve databases simultaneously.