Lakehouse: ODBC error when runnning queries (error to authenticate)

[alfBI]

Dear colleagues,

We are suffering an strange problem when executing a multiple queries against a table stored on a Fabric lakehouse using its SQL Endpoint.

Queries are executed by a third-party system through an ODBC.

ODBC (version 18) has been configured as described here using Service Principal as authentication method

https://learn.microsoft.com/en-us/fabric/data-warehouse/connectivity

Test showed a proper connection of the ODBC with the the proper username (Service Principal AppID) and password (Secret Value)

When the third party tool (configured to use this ODBC) starts to run the first queries it works fine, but after some minutes it always throw the following error message

2024.09.07 18:23:21.774 [FTL] [TID:  34] Connector has thrown unhandled exception. The processing will be terminated.
System.Data.Odbc.OdbcException (0x80131937): ERROR [CE269] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Error 12157 sending request to https://login.windows.net
ERROR [CE275] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Error requesting access token, HTTP status -1, expected 200
ERROR [FA004] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Failed to authenticate the user '55223675-3747-4be7-97e7-df102e7ded85' in Active Directory (Authentication option is 'ActiveDirectoryServicePrincipal').
Error code 0xA190; state 41360
ERROR [08001] [Microsoft][ODBC Driver 18 for SQL Server]TCP Provider: Timeout error [258]. 
ERROR [HYT00] [Microsoft][ODBC Driver 18 for SQL Server]Login timeout expired

It looks like at certain point the ODBC driver was no longer able to establish connections using the Service Principal for some unknown reason. We checked that the SP is fine and valid but for some reason the attempts to use it fails after some time.

I would like to monitor the connections created on the Lakehouse (throught the SQL EndPoint) to better understand what is going on. That is relatively easy on a SQL Server on Premise but I haven't found any clue about how to do that on Fabric Lakehouse (if possible)?

Questions are:

1) Has anyone experience a similar issue (ODBC Service Principal authnetication failure after having executed succesfully some queries?)

2) Any idea about how monitor the connections established to a Fabric Lakehouse?

Thanks,

[Anonymous]

Hi @alfBI 

1) Has anyone experience a similar issue (ODBC Service Principal authnetication failure after having executed succesfully some queries?)

I haven't tested this. I searched on the forum and internally but haven't found any similar issues submitted recently. Does this error happen on a specific query or does it happen on different queries intermittently? Does the execution of query take a long time before the failure? If you retry that query, will it succeed? Below is a known issue but not sure if it's related to the error you met: Known issue - SQL queries fail intermittently

2) Any idea about how monitor the connections established to a Fabric Lakehouse? 

You could try the methods introduced in the following documentation. 

Monitoring in Fabric Data Warehouse overview - Microsoft Fabric | Microsoft Learn

Query Insights provides historical query data for completed, failed, canceled queries along with aggregated insights to help you tune your query performance. For more information, see Query insights in Fabric data warehousing.

Users can get insights about their live connections, sessions, and requests by querying a set of dynamic management views (DMVs) with T-SQL. For more information, see Monitor connections, sessions, and requests using DMVs.

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[alfBI]

Hi, 

Error appears after submitting multiple queries to a Lakesahouse SQL Endpoint using an ODBC. Time that does take error to appear might change and not relies on the amount of time required to execute the queries. The relevant factor is the amount of queries executed. Error only appears when many queries (even if they are simpler ones) are executed.

After reading this blog

https://techcommunity.microsoft.com/t5/sql-server-blog/all-about-sql-drivers-and-access-tokens/ba-p/...

I quickly recognized our problem when it describes " we get support escalations related to failures around expired access tokens when using Microsoft Entra ID (formerly Azure Active Directory) authentication. "

This is exactly what is going on here. Multiple queries are executed through an ODBC (using authentications based on Service Principals-tokes) which for some reason is trying to use a connection that is already closed.. This leads to the error message shown above

Would be possible to escalate the issue? It sounds a bug to me

ODBC: Connection pooling in ODBC is handled by the ODBC driver manager, which is what is responsible for loading the correct ODBC driver requested by the application. The ODBC driver manager signals an ODBC driver when a connection is going into and out of the connection pool, so the ODBC driver for SQL Server knows when to set the RESETCONNECTION status. At the same time, it also knows if it needs to obtain a new access token and open a new connection on this event.

[Anonymous]

Hi @alfBI 

You could escalate this issue by creating a support request. Go to Microsoft Fabric Support and Status | Microsoft Fabric to create one. Once you received any feedback or solution, please do share them with the community. This would help people who may experience similar issues. Thank you! 

Best Regards,
Jing