Databricks Excel ODBC driver bug

[ns_casper]

Hello!

I might have experienced a bug with the ODBC driver. 

We have an issue where given certain priviledges in databricks, the ODBC driver is unable to show any schemas/tables.
When we click the 'expand' button on any catalog in the list (of which we have 6) no schemas are visible. It simply loads and finishes 'succesfully', similar to when it 'works'. My assumption is that the result from what ever API seems to be empty.. or something. the driver might be crashing or something, without Excel registering. 

The priviledge in question seems to be if the user has access to any federated catalog with `USE CATALOG`, without access to any of the underlying schemas. (I.e no `USE SCHEMA` and no `SELECT`). 

Anyway, we can add the priviledge in question; it breaks. remove it. it works again.

We are not sure how to proceed, but have removed access to this catalog for the time being, however it changes the structure of our security setup somewhat. If this is a known bug and a fix is on it's way, i'd love to hear it. if we are doing something wrong, likewise.

[ns_casper]

Hi Kaniz. 

We are utilizing the latest driver, and we are explicitly not providing USE SCHEMA on this entry.

Our permission architecture separates the type of access you have, and the domain in which data relies. This means that certain users will have access to all Foreign Catalogs, however might not have access to any schemas in it, as that data resides in a domain you do not have access to. This is essentially in following the Medallion Architecture. This is not a problem anywhere else, and results in the fact that our normal data-users can utilize Excel and spark without issue, however our data scientists cannot, as they have access to foreign catalogs. 

[ns_casper]

@Retired_mod have you had time to look at my message?

[KTheJoker]

Hey @ns_casper when you list catalog/schemas in Excel, it fires off queries to Databricks SQL. Can you see those queries in the Query History? Are there any errors there?

I suspect there is some issue with the underlying catalog that's preventing the results of those queries from being handed back, and the ODBC driver is "swallowing" the error.

[jbibs]

Following this post - we are also faced with the same issue. 

@KTheJoker- when I'm connecting and trying to expand a catalog, I do see the query fire off in the SQL Warehouse query history but in Excel nothing is returned. I can see the schemas/tables/views when connecting from Power BI and Tableau but not Excel. 

Is there any other configuration on the Databricks side to be done in order for it to work for Excel? Another team owns the Databricks config/setup but if I can suggest a solution for them to solve this, that would be great. 

Thanks!