Intermittent connectivity issues connecting to lakehouse SQL Analytics endpoint

[gmangiante]

Hello-

I'm experiencing some strange behavior with a Python API I'm writing when attempting to query my lakehouse SQL analytics endpoint. Here are some particulars:

• Linux containerized app running in an Azure app service
• Python FastAPI framework, running under gunicorn
• Using SQLAlchemy, pyodbc and the msodbcsql18 driver for Linux
• Connecting to the lakehouse analytics endpoint via its assigned name at <endpoint>.datawarehouse.fabric.microsoft.com
• Authenticating via the app service's system managed identity (Authentication=ActiveDirectoryMsi), which has permissions on the workspace containing the lakehouse
• Issuing regular old SELECTs against lakehouse tables, nothing fancy

What I'm seeing is mostly intermittent 0x2746 error codes, with an occasional 0x68 and 0x20, coming back from my queries, at random intervals. I can make an API call 30 times and have it run fine and return results, and then I walk away for a few minutes, come back and issue the same call, and it fails with one of these - seems to be 0x2746 about 80% of the time. My understanding is that 0x2746 typically signifies a TLS negotiation issue.

A couple of things I've tried to address this:

• Adding Encrypt=Yes;TrustServerCertificate=Yes to the connection string parameters
• Changing from asynchronous calls via aioodbc to regular synchronous calls via pyodbc
• Modifying the container's openssl config according to Can't Connect to Server, TCP Provider: Error code 0x2746. · Issue #1453 · microsoft/msphpsql (github... and similar articles to reset the SECLEVEL

None of this has resulted in a more stable experience. I will note that I haven't been able to replicate the problem developing locally, using the exact same Dockerfile and the sole difference being that, in local development, I use a ClientSecretCredential to acquire a token and pack it to send as a SQL_COPT_SS_ACCESS_TOKEN ODBC parameter prior to connection, and it runs fine every time. When I'm running in the Azure App Service, I circumvent that process and just use ActiveDirectoryMsi authentication with no credentials. I guess my next step will be to try storing a ClientSecretCredential in a key vault and seeing if using that rather than managed identity makes a difference in the App Service, but I'd obviously prefer not to have to do that unless absolutely necessary.

Hoping someone has some ideas - please let me know if I can provide further details! Thanks so much.

[gmangiante]

Just a quick follow-up: I did go ahead and try authenticating via stored ClientSecretCredential from the App Service, and it hasn't improved reliability. I continue to have a completely stable experience developing locally, and a fairly unpredictable experience from the App Service. Additionally, there does seem to be a "waking up" aspect to the errors I'm seeing from the App Service - once it succeeds, typically after 3 or 4 tries, it will remain stable for a while, but if I leave it idle for too long, it goes back to the 0x2746 errors.

[Anonymous · Accepted solution]

Hi @gmangiante,

According to your description, it seems like more related azure app service release resource which connections used to optimize/reduce the source usage. 
Have you tried to add some simply query to request with low frequency as heartbeat to keep connections alive?

Regards,

Xiaoxin Sheng

[gmangiante]

Just to put a pin in this in case anyone else runs across it - I can confirm that the behavior I was seeing has disappeared by implementing a "heartbeat" query to the lakehouse a couple of times a minute. In my case, I chose to do it by starting a looped task via asyncio during my FastAPI lifespan startup. Also, keeping the heartbeat alive means that I don't have to worry about waiting for resources to spin up to answer queries if the lakehouse hasn't been touched in a while! A great solution.

[gmangiante]

Hi, Xiaoxin - thanks so much for the timely reply, and I think it's a great suggestion. Will give that a shot!

[Anonymous]

Hi @gmangiante,

I think I find the document that mentions this at the azure app service performance Q&A:

Application performance FAQs - Azure | Microsoft Learn

Azure Load Balancer has a default idle timeout setting of four minutes. This setting is generally a reasonable response time limit for a web request. so, App Service returns a timeout to the client if your application does not return a response within approximately 240 seconds (230 seconds on Windows app, 240 seconds on Linux app).　If your web app requires background processing, we recommend using Azure WebJobs. The Azure web app can call WebJobs and be notified when background processing is finished. You can choose from multiple methods for using WebJobs, including queues and triggers.

For this scenario, you can add the processing in code, use background processing in webJob or manually modify the timeout properties.

Regards,
Xiaoxin Sheng