SSRS Connect to Databricks

[Ronis]

Hi ,

I need to connect databricks query from microsoft SSRS.

is it possible ?

How do you make the connection?

[Haider93]

Hi @Ronis, 
You can do it using Simba spark ODBC driver simple. Following are the steps if you face any issues on your way.

1. Firstly, install Simba Spark driver 64/32 bit into our local machine. [https://www.databricks.com/spark/odbc-drivers-download ]
2. Then open control panel -> Administrative tools -> ODBC DataSource and add ODBC instance into your local machine.
3. In the Data Source Name field, type a name for your DSN. In my case I have choose it eduschedargdevmanagedrgdbw01 as its my resource name in Azure and I want to be consistent across the platform.
4. Optionally, in the Description field, type relevant details about the DSN.
5. From the Spark Server Type drop-down list, select the appropriate server type for the version of Spark that you are running
6. If you are running Shark 0.8.1 or earlier, then select SharkServer.
7. If you are running Shark 0.9, or Spark 1.1 or later, then select SparkThriftServer.
8. To find the name of server on which Databrick is hosted. Open Databricks and click on compute and then All purpose cluster which is active.
9. Specify whether the connector uses the DataStax AOSS service when
   connecting to Spark, and provide the necessary connection information:
10. Host: This is the same host name you use for accessing Databricks via the user interface (example: xyz.azuredatabricks.net )
    Username: token
    Password: To create an authentication token in Databricks, click on the profile icon in the upper right corner of Databricks. Under ‘User Settings’, click ‘Access Tokens’. And then click ‘Generate New Token’. Paste the token into this password field.
    SSL: 1
    transportMode: http httpPath: This is the path to the cluster that will be used to execute the query. Click on your cluster in the Databricks UI. Then under ‘Configuration’, scroll down to ‘Advanced Options’. Then click on the tab ‘JDBC /ODBC’. There you will see a field called ‘HTTP Path’. AuthMech: 3 (3 corresponds to UserName and Password) UseSystemTrustStore: 1
11. Enable SSL 
12. Open Microsoft visual studio and connect with ODBC instance that you have added following the above 8 steps

[Tharun-Kumar]

@Haider93 

https://www.databricks.com/company/partners/technology This is the list of supported databricks technology partners. SSRS is not yet part of it. 

We do have official support for Power BI. You can use this document to mimic it for SSRS - https://docs.databricks.com/en/partners/bi/power-bi.html#connect-with-partner-connect

[Haider93]

Hi @Tharun-Kumar ,

I am able to establish the connection and query the data from Databricks delta tables in SSRS using ODBC connector. However, when I am deploying the dataset and report file to reporting server and trying to connect it failed. 

[Haider93]

Hi @Tharun-Kumar  I am able to establish connection successfully between data bricks and SSRS. I have installed Simba spark driver on Server hosting SSRS application and my machine which has visual studio which I am deploying to the server. I make sure that I am using the supported application aligned with my architecture of my server. In my case its 64 bit. Please feel free to touch base if you still face issue. haiderali_1993@hotmail.com

[euribe]

I am able to read databricks from SSRS using a personal token (corresponding to my account), but that's not good practice for an eventual production environment.

Is there a way to connect not using a personal access token?

Thanks!

[-werners-]

SSRS has limited auth methods.  It also is EOL so my answer is: No.
This is not a limitation of Databricks but SSRS.
You could define the connection as a linked server on sql server, that might open some extra (MS native) options.

PS. it is best not to post on an old topic, chances are small someone will answer (except for a crazy human like me).