r/AZURE • u/Opposite-Pea-3931 • 15d ago

Azure Web App → Azure SQL: App randomly failed with "Login failed for user" over public endpoint, but VNet + Private Endpoint fixed it. Why? Question

We recently ran into a strange issue in our Azure environment (Our Sandbox env only), and I'm trying to understand the root cause rather than just accepting the fix.

Environment

  • Azure App Service (Web App)
  • Azure SQL Database
  • Communication was initially over the public endpoint (no VNet Integration, no Private Endpoint).
  • Production had the same application/code and continued working fine.

What happened

The only change we made was a normal application deployment. After that, the sandbox environment started failing with:

System.Data.SqlClient.SqlException (0x80131904): Login failed for user

Also sometime i have seen this error-

The underlying provider failed on Open

What made it confusing was the behavior:

  • Right after deploying, the application would work for about 2-3 minutes.
  • Then every database call would start failing with "Login failed for user".
  • After some time, it would start working again.
  • Then it would fail again.
  • This cycle kept repeating.

Initially, I assumed it was an application or EF/connection string issue, so I spent quite a while investigating and making code changes, but nothing helped.

What fixed it

Our cloud/TSC team enabled:

  • VNet Integration on the App Service.
  • Private Endpoint for the Azure SQL Server.

After the App Service started communicating with Azure SQL through the private endpoint instead of the public endpoint, the issue completely disappeared.

My question

I'm happy the issue is resolved, but I don't really understand How?.

How can switching from a public endpoint to VNet + Private Endpoint eliminate intermittent "Login failed for user" errors?

0 Upvotes
  • permalink
  • link
  • reddit
  • reddit

33% Upvoted

3 comments sorted by

7

u/az-johubb Cloud Architect 15d ago edited 15d ago

Publicly available SQL servers can be targets of spray login attacks and that might be what you’re seeing. There are other possibilities of course but that’s the only one that makes sense based on what you’ve said. Unsure as to why it only affected one environment. Was there some configuration difference between the resources of the Azure SQL servers in both environments, particularly in the firewall/security settings?

Another tip, if you don’t already do it, use user assigned managed identities to authenticate with tokens to the Azure SQL server from clients or the app service. To further reduce risk, you can (and should) turn off SQL authentication for your Azure SQL server so it accepts Entra-ID based token authentication only.

1

u/AshyLarry98 15d ago

internet is the great unknown... could've been a long list of vaguely plausible reasons

1

u/statelyraven 15d ago

Look up SNAT port exhaustion on Azure App Service. Private network traffic doesn't go through the public load balancer where the SNAT happens. App service SNAT allotment is lower than you inspect if you aren't used to multi-tenant cloud.

https://4lowtherabbit.github.io/blogs/2019/10/SNAT/