The setup wizard hides five distinct failure modes behind one error
Databricks community threads about Lakeflow Connect's SQL Server connector share a pattern: the database setup step fails, the error message says almost nothing useful, and the engineer spends hours toggling permissions that may not be the problem at all.
The setup step is doing real work behind the scenes. It verifies that the utility objects script has been installed correctly, checks that change tracking or CDC is enabled at both the database and table level, confirms the ingestion user has the right grants, and validates that DDL support objects exist for schema evolution. Any one of these checks can fail, but the wizard reports a single generic failure.
Here are the five causes, ordered by how often they appear in practice:
- The user who ran the utility script was not a member of db_owner, so the stored procedures installed incompletely or silently skipped permission grants.
- CDC capture instance slots are full — SQL Server allows a maximum of two per table, and if both are occupied by non-Lakeflow instances, the connector cannot create its lakeflow_ prefixed instance.
- The ingestion user is missing SELECT on sys.change_tracking_tables, sys.change_tracking_databases, or the cdc schema.
- The utility script version is outdated — the current version is 1.5, and older versions use a different capture instance prefix (New_ instead of lakeflow_) that the latest gateway does not recognize.
- Change tracking or CDC is enabled at the database level but not on the specific tables you selected for ingestion.
Each of these requires a different fix. The rest of this post walks through how to confirm which one you hit.
db_owner is required for setup, not for ingestion — and that distinction breaks people
The Databricks documentation states clearly that the user running the utility objects script must be a member of db_owner. But it also says the ingestion user only needs specific SELECT grants. Engineers read this as "my ingestion user does not need db_owner" — which is correct — and then run the utility script as the ingestion user. The script does not hard-fail. It installs some objects, silently skips others, and the setup step later rejects the incomplete installation.
To verify, connect as a user with db_owner and run:
``sql
SELECT dbo.lakeflowUtilityVersion_1_5() AS UtilityVersion;
SELECT dbo.lakeflowDetectPlatform() AS Platform;
``
If either function does not exist, the script did not complete. To grant db_owner to your setup user on modern SQL Server (2012+):
``sql
USE [your_database];
ALTER ROLE db_owner ADD MEMBER [your_setup_user];
GO
``
On legacy instances or restricted environments, use sp_addrolemember instead:
``sql
USE [your_database];
EXEC sp_addrolemember 'db_owner', 'your_setup_user';
GO
``
After granting the role, re-run the entire utility_script.sql. Do not assume partial installation is recoverable — the script is designed to be idempotent, so a clean re-run is safer than trying to patch individual objects. Once the script completes, run the verification functions again. Both should return values without errors.
A common variation of this problem appears on Azure SQL Database, where the setup user needs membership in the ##MS_DatabaseConnector## server role rather than individual master database grants. If you are on Azure SQL Managed Instance, you need VIEW ANY DATABASE at the server level plus explicit master database access. The platform matters.
CDC capture instance slots fill up silently
SQL Server enforces a hard limit of two capture instances per table. Lakeflow Connect needs at least one available slot to create its lakeflow_ This failure is particularly frustrating because CDC appears to be enabled. The database-level check passes. The table-level check passes. But the connector cannot create its own capture instance, and the setup step fails. To check capture instance usage per table: `` If any table shows two capture instances and neither starts with lakeflow_, you have found the problem. Your options: remove one of the existing capture instances if it is no longer needed, or switch that table to change tracking instead of CDC. Change tracking works for any table with a primary key, uses less overhead on the source, and does not have the two-instance limit. If you are upgrading from an older utility script version, you may see capture instances prefixed with New_ instead of lakeflow_. Running the latest utility_script.sql (version 1.5) handles this migration automatically, but only if a capture instance slot is available for the new naming convention during the transition. Stop the gateway before upgrading — the script replaces DDL support objects, and a running pipeline will lose DDL audit events during the swap. The hybrid approach Databricks recommends — change tracking for tables with primary keys, CDC for tables without — sidesteps the slot problem entirely for most schemas. 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: `` 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: `` To check which tables actually have change tracking enabled: `` 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: `` 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: ```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._1 instance. If both slots are occupied by existing capture instances from other systems — an older replication setup, a homegrown CDC consumer, or a previous Lakeflow installation using the deprecated New_ prefix — the connector cannot proceed.
sql
SELECT
SCHEMA_NAME(t.schema_id) + '.' + t.name AS TableName,
ct.capture_instance,
ct.start_lsn,
ct.create_date
FROM cdc.change_tables ct
INNER JOIN sys.tables t ON ct.source_object_id = t.object_id;
``The ingestion user's permission surface is larger than it looks
sql
EXEC dbo.lakeflowFixPermissions
@User = 'your_ingestion_user',
@Tables = 'ALL';
``Change detection enabled at the database level means nothing without table-level activation
sql
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();
``sql
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;
``sql
EXEC dbo.lakeflowSetupChangeTracking
@Tables = 'Sales.Orders,Production.Products',
@User = 'your_ingestion_user',
@Retention = '2 DAYS';
``Run the verification queries before the wizard, not after it fails
Related integrations