Connecting to SQL Warehouse in Custom App

[bferrell]

I've got a custom Dash app I've written and am attempting to deploy. It runs fine on my local machine (while accessing my DB SQL Warehouse), but when I try deploying to Databricks, it cannot connect to the data for some reason. I was basically following the guide in this video . Since he did not change his code in this video when he deployed, I assumed I didn't have to. Anyone else run into this issue? I've included a code snippet of my connection details - which again - run fine on local. 

from databricks import sdk, sql
cfg = sdk.config.Config()
with sql.connect(
    server_hostname = cfg.host,
    http_path = os.environ.get("HTTP_PATH"),
    credentials_provider = lambda: cfg.authenticate,
    ) as conn:
    with conn.cursor() as cursor:
        print("Executing SQL query...")
        cursor.execute(f'''
                        SELECT 
                            *
                            FROM {table_name}
                        ''')
        print("Query executed, fetching results...")

[Shua42]

Hi @bferrell ,

I would make sure that HTTP_PATH is being set in the YAML file, or update it to be hardcoded if you don't need to deploy across workspaces. If you aren't planning on deploying across different workspaces, I would just hardcode the http path in.

If that doesn't work, can you share more about the error message your getting, or the behavior that suggests the app can't access the data?

[bferrell]

Hi @Shua42, 

I tried hard-coding already and that didn't seem to fix it. I've attached a screenshot of the SQL Warehouse activity. Seems as though it doesn't allow for the authentication being used when I run the app through Databricks. The app deployment is giving it a different service principal's UUID. When I run it locally, it's using my e-mail as authentication as you can see with my username in the screenshot. Could this have something to do with how I set up my Databricks profile in my IDE? Any help is appreciated! 

[Isi]

hi @bferrell 

Hi! If I’m understanding this correctly, the issue seems to be that the Service Principal launching the query from the dashboard (specifically app-39l35m...) is not authorized to execute the query.

However, from the screenshot you shared, it’s clear that the connection itself is being established successfully, which suggests that your configuration is correct. So I’d say the problem is most likely that this Service Principal doesn’t have sufficient permissions to read from the table(s), and that’s why the query returns nothing.

On the other hand, your personal user account does have access, either directly or through a group, which is why it works for you locally. You should go to the Catalog / Schema / Table in the Data > Catalog section, and check the Permissions tab. Use the filter at the top-right to search for the Service Principal (app-39l35...) and verify if it has the necessary permissions.

If it turns out that permissions aren’t the issue, could you share the exact error message the query is returning?

Hope this helps! 🙂

Isi

[Ludwig_MC]

Thanks for the detailed breakdown, that really clears things up. I’ve run into similar permission quirks before where the app identity looked fine on the surface, but missing read access on specific tables caused silent failures. Definitely going to check the Catalog permissions like you suggested.

[bferrell]

Thanks for the feedback @Isi . I have to check with our administrator on the permissions side as I can't see all of them. But, it sounds like that may be the issue. 

[Isi]

We can wait till you check the permissions @bferrell @bferrell 🙂

Best regards,

Isi