unable to connect to Databricks; (ODBC error)

[MarinaK]

When trying to connect to an (MS Azure) Databricks source, my Power BI Desktop keeps giving errors. The error is:

OLE DB or ODBC error: [DataSource.Error] ERROR [HY000] [Microsoft][Hardy] (35) Error from server: error code: '0' error message: '[Microsoft][Hardy] (134) File [file-id]: A retriable error occurred while attempting to download a result file from the cloud store but the retry limit had been exceeded. Error Detail: File [File ID]: An error had occurred while attempting to download the result file.The error is: [Microsoft][DSI] An error occurred while attempting to retrieve the error message for key 'DSSslErrorMessage' with message parameters ['unable to get certificate CRL'] and component ID 110: Could not open error message files - Check that "C:\Program Files (x86)\Microsoft Power BI Desktop\bin\ODBC Drivers\Simba Spark ODBC Driver\en-US\DSMessages.xml" or "C:\Program Files (x86)\Microsoft Power BI Desktop\bin\ODBC Drivers\Simba Spark ODBC Driver\DSMessages_en-US.xml" exists and are accessible.. Since the connection has been configured to consider all result file download as retriable errors, we will attempt to retry the download.'..

I've tried several things and I cannot get Power BI to load my full model.

What did I try:

• remove Power BI app, restart laptop and reïnstall
  • From store
  • 64-bits from store (Version: 2.136.1202.0 64-bit (september 2024))
  • 32-bits from store (Version: 2.136.1202.0 32-bit (september 2024))
• save as .pbix or .pbip > both fail to refresh
• refresh the model in Power BI service > that does work, the error only occurs in Power BI Desktop
• refresh the data in another model connecting to different databricks tables > this also fails
• start with a new file to load the data > this doesn't solve the issues
• Use the button 'Open in Power BI Desktop' from within Databricks (When selected one of the tables in the Catalog)
  This last option did seem to get the data into Power BI. But then I saw this created a direct query, where my collegues and I are used working with import mode. When switching to this import mode, the error reoccurs.
• Enable and disable the load on one or more tables in power query.
  This resulted in identifing  one table that causes the issues. When enabling the load of all tables but this one; everythings okay. When enabling the load of only this table; the error occurs. Unfortunately, this is the fact table. I do miss out on the core of the report without the fact table 😉

This fact table contains 607,035 records and contains:

• 5 fields containing an string (hashed key) foreign key connecting to the dimension tables
• 1 date field joined to a separate date table
• 2 columns with details (decimals; 15,2 and 18,6).

In the databrick details on this tables, it says it's 6.5MiB.

Up until today I was able to load data into the model. I think the last time I did load data into a model connected to databricks, was in the first week of September. One of the topics I read on the forum mentioned issues in Power BI on september 19th, but it also said these issues were solved. Could it be possible I've hit a specific combination of factors that causes this issue (or a similar issue) to arise?

Is there anyone that has an idea on how I can fix this?

Other topics read:
Simba Spark ODBC Driver error in PBI Desktop (Apri... - Microsoft Fabric Community
Unable to connect to Databricks from Power BI Desk... - Page 3 - Microsoft Fabric Community

[rajendraongole1 · Accepted solution]

Hi @MarinaK -The error you're encountering is related to SSL certificate validation when Power BI Desktop attempts to connect to Azure Databricks. By ensuring that the necessary driver files are present and accessible, checking network and firewall settings, updating drivers, and verifying SSL configurations, you can address the issue. If the problem persists, contacting Microsoft Support may be necessary for further assistance.

https://powerbi.microsoft.com/en-us/support/

Troubleshooting log files path:

Navigate to %localappdata%\Microsoft\Power BI Desktop\Tracing. share this information to msft

C:\Program Files (x86)\Microsoft Power BI Desktop\bin\ODBC Drivers\Simba Spark ODBC Driver\en-US\DSMessages.xml
C:\Program Files (x86)\Microsoft Power BI Desktop\bin\ODBC Drivers\Simba Spark ODBC Driver\DSMessages_en-US.xml

Download the latest Simba Spark ODBC Driver directly from ODBC Drivers Download – Databricks or the official provider.Install the driver manually to ensure all necessary files are correctly placed.

Hope the above information helps.

[MarinaK]

Thank you for the response!
The IT department took it under investigiation, it turns out to be an issue in the firewall. 
So if anybody else might run into a similar issue; maybe this can be of help.

[MaZoSR]

Hi, would you happen to know what they changed to make this work? I'm experiencing the same issue. Thanks.

[MarinaK]

Hi, No sorry, can't help you with the technical details... only thing I know is they told me "we changed something in the firewall rules"