Re: Lakeflow Connect SQL Server gateway – intermit...

Oumeima · 2 weeks ago

Hi all,

We run a Lakeflow Connect SQL Server ingestion pipeline with the gateway in continuous mode, reading from an Azure SQL Database using an Entra ID service principal (OAuth2 client credentials). The setup works overall, but we see a constant low rate (~6/min) of failed logins on the SQL side:

```
Error: 18456, State: 132 (AAD failure)
Login failed for user '<token-identified principal>'
```

The strange part: it's intermittent — connections from the same gateway, using the same identity, succeed at the same time others fail. Ingestion keeps up, but the error rate on the database never stops.

What we've ruled out so far:

1. Wrong tenant/issuer: the token endpoint is pinned to the correct tenant; verified in the deployed connection. The SP is multi-tenant, and the DB's external user maps to the SP's OID in the resource tenant.
2. Token acquisition problems : Entra service principal sign-in logs show 100% successful issuance, no AADSTS errors, no Conditional Access blocks. So tokens are minted fine; SQL rejects some of them at presentation time.
3. Permissions / login mapping: most connections succeed with the same identity, so the external user itself is fine.

Our working theory: the gateway's connection pool sometimes opens new physical connections using a cached access token that is at/past expiry (Entra tokens live ~60–90 min), while other code paths use freshly refreshed tokens — hence the partial, steady failure rate. mssql-jdbc treats 18456 as non-transient, so those attempts aren't retried at driver level.

Questions:

1. Has anyone seen this pattern with the SQL Server managed connector? Did you find a fix or workaround?
2. Does the gateway proactively refresh the OAuth token before opening new pooled connections, or is there a known gap here?
3. Is there any connection/pipeline option to control token refresh or pool connection max lifetime for the gateway?
4. Any way to surface the state-132 sub-reason (e.g., "token expired" vs. "invalid signature") from the gateway event logs, short of Azure SQL auditing?

We've opened a support ticket in parallel, but curious whether others have hit this. Happy to share more (sanitized) event log details if useful.

Thanks!

emma_s · a week ago

Hi,

I just had a look at this to see if we had any reports of it internally but we don't have anything similar in terms of low level noise. Just when it breaks the pipeline entirely. I think your diagnosis is probbaly close to what's happening.

I think in this case you're absolutely right to go down the support ticket request, there may be some changes they can make to stop this or things they know.

The only thing that did come up in my research which may be worth a try is chaging the channel in the pipeline from preview to current (if it is set on current), when things are failing altogether, there were some signs that this helped. https://docs.databricks.com/aws/en/release-notes/dlt/ There has been some very recent improvement to how connection pools are working, recently so worth double checking this is still an issue. But otherwised definitely a support ticket question.

Once you get a solution would be good for you to come back here and post for others to benefit too.

Oumeima · a week ago

Hello emma,

Thanks for your reply!

Our pipelines are set on "current" already. You mean go from "Current" to "Preview" Right?

emma_s · a week ago

Hi,

The troubleshooting advice was actaully the other way round, it said from preview to current. But always worth trying the other channel, it's an easy test. Obviously just checkk it won't cause any other symptoms first (do a test run)

THanks,

Emma

Lakeflow Connect SQL Server gateway – intermittent Entra ID token auth failures (18456, state 132)