How to surface azure active directory service principal related errors

[prdpsvs]

Environment

• Python: 3.11.9
• pyodbc: 5.1.0
• OS: Ubuntu 22.04 LTS
• DB: Microsoft Fabric Data Warehouse
• driver: ODBC Driver 18 for SQL Server

Issue

I am using Azure Service Principal Authentication with pyodbc. what is the best way to surface issue when user provides incorrect client id or secret. Right now, any error during connection is throwing ('HYT00', '[HYT00] [Microsoft][ODBC Driver 18 for SQL Server]Login timeout expired (0) (SQLDriverConnect)')

I tried to capture errors from azure identity SDK using from azure.core.exceptions import ClientAuthenticationError, HttpResponseError
but no luck. Any thoughts?

[v-chojas]

The driver returns several diag recs; do you see those in the ODBC trace?

[prdpsvs]

I would like to capture these at the connection failure time programmatically and appropriately surface user-friendly messages. Any specific options to figure out?

[v-chojas]

I am asking if you see all the diag recs in the ODBC trace.

[prdpsvs]

Where do I see these diag recs in trace file. I attached a sample trace file. If you can share documentation to identify diag recs, it would be useful.
SQL.LOG dbt.txt

Also, please let me know how retrieve these diag recs in case of connection failure in pyodbc and how to co-relate the errors based on diag recs?

[v-chojas]

Look for SQLGetDiagRec function calls.

[prdpsvs]

Yes, I see SQLGetDiagRec function calls in the ODBC trace. How can I use SQLGetDiagRec to capture exact error/intent with pyodbc?
Is there a way to capture exact issue during connection failures and return something more descriptive/error code, it would help the user debug what the actual issue is.

Connection errors should be more helpful - below scenarios return the same error message - >Database Error
('HYT00', '[HYT00] [Microsoft][ODBC Driver 18 for SQL Server]Login timeout expired (0) (SQLDriverConnect)')

1. Remove a single character from a valid secret:
2. Remove a single character from a valid client_id:
3. Invalid username / password

The expected behavior in above scenarios should return a different code/information that can map to a specific issue.

[v-chojas]

If you test connection using isql you will see all the diag recs.

I don't think those scenarios should result in different errors as they are all authentication failures, and for security reasons they are normally treated the same.

[prdpsvs]

I understand that they are all authentication failures but due to different reasons though. Does pyodbc return/capture different signals/error sub codes for each of these scenarios? At this time, all I can capture is HYT00 as error code and "Login timeout expired (0)" as error message. From end user perspective, it's difficult to pinpoint the exact error messages. Above scenarios are just few examples but there are many more scenarios resulting same error code and message by pyodbc today.

[prdpsvs]

@v-chojas , any inputs on this?