Forum Discussion
Intermittent connectivity issues connecting to lakehouse SQL Analytics endpoint
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.com) 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.
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
5 Replies
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.
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
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.
