Power BI Desktop issues with company proxy when using Databricks connector

[betizad]

I am facing issues with Power BI Desktop when trying to connect to a Databricks cluster (using either AAD or token) and read tables.

When I setup the connection on my company latop with the company proxy. I get the following error:

Details: "ODBC: ERROR [HY000] [Microsoft][ThriftExtension] (14) Unexpected response from server during a HTTP connection: SSL_connect: error code: 0."

However doing the same from my private laptop works seamlessly. 

As a workaround I tried to create a ODB connection using the databricks driver on company laptop. It works, but then refresh is bound to work only through Gateway installed on the laptop or another gateway. Not directly from the Power BI service to Databricks. 

So the only issue I can think of is that: 

The driver for databricks connection in Power BI Desktop does not get the proxy settings.

I followed the guide/walkthrough here:

https://docs.microsoft.com/en-us/azure/databricks/integrations/bi/power-bi

[betizad]

Hi @v-kelly-msft 

I think I know where the problem might be. But I have no idea how this can be resolved setting up proxy with the internal Power BI Simba Spark ODBC Driver. A bit help is really appreciated.

I can setup the ODBC connection (Requires admin rights to install Simba Spark ODBC driver) with proxy settings in a new ODBC connection as below, and it works. This method also requires one new connection setup per Spark cluster. 

I used the configuration suggested here:

https://docs.microsoft.com/en-us/azure/databricks/scenarios/connect-databricks-excel-python-r

[v-kelly-msft]

Hi @betizad ,

Check the reference below:

https://docs.microsoft.com/en-us/azure/databricks/scenarios/connect-databricks-excel-python-r#set-up...

Follow the instructions,you would get a successful notification.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[betizad]

Thanks @v-kelly-msft . 

Yes this method works to make a connection. But it requires two more installations (Simba ODBC driver + Gateway for Powe BI service side refresh). It also requires generating tokens and does not work with Azure AD.

Still The proxy issue from Power BI side (to connect with internal driver) is standing. I'll try to get in touch with Microsoft support.

[betizad]

Hi @v-kelly-msft ,

Thanks for the reply. I have tried with both options, and the results is the same.

Eventualy we would like to use "import". 

Do you know which ODBC driver is used by Power BI and how can I set the proxy there?  

[v-kelly-msft]

Hi @betizad ,

Try to take the steps below:( import mode) 

Step 1: Get the JDBC server address

1. In Azure Databricks, go to Clusters and select the cluster you want to connect to.
2. On the cluster edit page, scroll down and select the JDBC/ODBC tab.
3. On the JDBC/ODBC tab, copy and save the Hostname and HTTP path.

Hostname: centralus.azuredatabricks.net

HHTP Path: sql/protocolv1/o/8159029426904320/0127-082543-soar12

Step 2: Configure and make the connection in Power BI Desktop

1. Launch Power BI Desktop, click Get Data in the toolbar, and click More…
2. In the Get Data dialog, search for and select the Spark connector.
3. Click Connect.
4. On the Spark dialog, configure your cluster connection. Get Server Hostname and HTTP Path from Step 2.

Server: https://<ServerHostname>:<PORT>/<HTTPPath> 

Server example: https://centralus.azuredatabricks.net:443/sql/protocolv1/o/8159029426904320/0127-082543-soar12

Protocol: Select HTTP.

Data Connectivity mode: Select Import, which lets you offload processing to Spark. This is ideal when you have a large volume of data or when you want near real-time analysis.

Click OK.

On the next dialog, enter the word token in the User name field and a personal access token in the Password field.

Click Connect

Below is the related reference you may go to :

https://social.msdn.microsoft.com/Forums/en-US/a9ad2ab0-78e5-4022-b9e9-580586078803/databricks-conne...

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[betizad]

Hi @v-kelly-msft 

Thanks very much for the detailed instructions. I tried the new alternative solution with the Spark connection. Unfortunately the end result is the same. I get the smae SSL_connect error code as above. 

Just for testing i disconnected the VPN on the company laptop, and it goes through. I can access the tables and continue with data retrieval from either Spark or Databricks connection (via token or AAD). 

Unfortunately this can't be a solution, as the devices will be in a zone that the proxy can not be disabled or disconnected. 

P.S. I dont know if this helps, but our company proxy is a deep inspection proxy and it even reissues certificates. 

[v-kelly-msft]

Hi @betizad ,

Pls refer to :

https://stackoverflow.com/questions/48050635/power-bi-spark-odbc-error-hy000-microsoftthriftextensio...

https://docs.microsoft.com/en-us/azure/databricks/integrations/bi/power-bi

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[betizad]

Thanks for the reply.

I followed the steps on https://docs.microsoft.com/en-us/azure/databricks/integrations/bi/power-bi  but I can't go passed step 7, due to the connectivity error.

As for the stackoverflow link, I don't see any solution. Could you maybe clarify more?

[v-kelly-msft]

Hi  @betizad ,

Are you using "import" or "direct query"?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[betizad]

@v-kelly-msft I have tried both options. The results are the same.