False positive INGESTION_GATEWAY_SQL_SERVER_AGENT_… – Databricks Community

Hi @vsrekha213 

This is most likely a permissions problem rather than the Agent itself. The gateway checks Agent/capture-job status while logged in as the connector’s database user. If that login can’t see server-level state or the msdb job tables, the check reads as “not running” even though the Agent is up. Your checks (msdb.dbo.sysjobactivity etc.) were probably run as an admin. Run them as the connector login, then fix the grants by upgrading the utility objects script to 1.7 and re-running lakeflowFixPermissions.

1. Connect as the exact login used in the Unity Catalog connection and run:

SELECT servicename, status_desc FROM sys.dm_server_services;   — needs VIEW SERVER STATE

SELECT j.name, a.start_execution_date, a.stop_execution_date

FROM msdb.dbo.sysjobs j

JOIN msdb.dbo.sysjobactivity a ON a.job_id = j.job_id

WHERE j.name LIKE ‘cdc.%’;

If either one fails with a permission error or returns no rows for that login, that’s your false positive.

2. Fix the grants. Version 1.7 of the utility objects script fixed lakeflowFixPermissions so it now grants the required server-scoped permissions from master. Older installs could miss them, and that’s consistent with seeing this on both db1 and db2. As a db_owner on each ingested database, re-run the latest utility_script.sql and then:

SELECT dbo.lakeflowUtilityVersion();  — should return 1.7

EXEC dbo.lakeflowFixPermissions @User=”your_connector_user”, @Tables=”ALL”;

EXEC dbo.lakeflowSetupChangeDataCapture @Tables=”dbo.tab1″, @User=”your_connector_user”;

3. Re-run step 1 as the connector login to confirm the Agent shows as Running, then restart the gateway pipeline. The error is non-retryable, so the gateway needs a fresh start after the grants are fixed.

If step 1 already succeeds as the connector login and the gateway still fails, it’s a connector bug on MI. In that case, open a support ticket with the gateway driver logs (stdout) and the pipeline ID.

Docs: https://docs.databricks.com/aws/en/ingestion/lakeflow-connect/sql-server-utility-reference

Privileges: https://docs.databricks.com/aws/en/ingestion/lakeflow-connect/sql-server-privileges