Unable to retrieve catalog, schema, tables using JDBC endpoint of SQL Warehouse

[fly_high_five]

Hi,

I am connecting to SQL Warehouse in UC using its JDBC endpoint via DBeaver. However, it doesn't list any catalogs, schemas and tables. I checked the permission of SQL WH by logging to ADB Workspace and queried the table (attached a dummy table example) using Warehouse compute. 

Since its unity catalog, so I tried same with All Purpose Cluster. With APC, I am able to see tables via ADB workspace as well as DBeaver.

Can someone please guide what's preventing access of Unity Catalog through JDBC endpoint of SQL WH using DBeaver?

 

@szymon_dybczak @WilliamRosa 

#warehouse #jdbc #dbeaver

[Commitchell]

Hi there,

I'm not familiar with DBeaver specifically, but I have experienced DBSQL Warehouses being much stricter when enforcing permissions than All-Purpose Clusters. Warehouses check explicitly if that identity has access to those assets, where All-Purpose Clusters may have several different configurations that makes them appear more permissive. You cannot rely on admin permissions when using a warehouse either.

I know it sounds silly as you can literally see the tables using the warehouse in the Databricks GUI, but I'd still double check that your user, or the service principal you are using for the JDBC connection, has the following permissions on the Catalog/Schemas that you wish to see:

GRANT USE CATALOG ON CATALOG <catalog> TO <principal>;
GRANT USE SCHEMA  ON SCHEMA  <catalog>.<schema> TO <principal>;

Let us know if you figure it out!

[fly_high_five]

Hi @Commitchell ,

I have checked the necessary permissions and I do have the necessary permissions. Still, I have been unable to see any schema, tables in DBeaver for my UC ADB while using Browser Based Interactive Login (Entra ID).

Just to validate that using my local machine is not preventing me to access data from Unity Catalog (due to org policy), I was able to fetch and retrieve table using Databricks SQL Connector for Python but unfortunately that can't be used as alternative to DBeaver. The issue still persists.

[Commitchell]

Out of curiosity, how are you using DBeaver (or what are you using it for)? Perhaps I can recommend a native alternative within Databricks.

I just downloaded DBeaver and configured the JDBC connection to DBSQL via Browser OAuth. I was able to successfully connect to a catalog, schema, and browse tables.

Looking at your screenshots, I notice that it's not just the Tables that are empty, your Catalog and Schemas aren't populating either. Here's mine with the 3-level hierarchy showing:

 This would indicate a permissions issue with the Catalog.

 

Silly question, I assume you tested the connection and the OAuth succeeded?

I did have to set the driver properties to get my OAuth Flow to work:

AuthMech = 11
EnableTokenCache = 0

 

 

[fly_high_five · Accepted solution]

Hi @Commitchell ,

Thanks for trying out DBeaver connection at your end. Yes, the OAuth is successful and I'm setting same driver properties as you.

Today, I got the issue resolved by updating the driver. At the time of posting my question, I was using JDBC driver 2.7 but today I updated to 3.1.1 and tried with 3.0.7 as well. Both driver are able to list catalogs, schemas and tables to some extent.

I ran into another issue in which I am unable to list all schemas inside catalogs which are having long names(d4001-centralus-azcld-mgmtcatalog - Catalog naming is enforced by organization so can't make it short). As a workaround, I am using below query to list all schemas and tables inside catalogs.

 

select * from system.information_schema.tables

 I don't think I can use UI to browse through long catalog names.

[Commitchell]

Strange! That sounds like it might be a DBeaver limitation. I'm glad you got it working and found a workaround!