cancel
Showing results for 
Search instead for 
Did you mean: 
Data Engineering
cancel
Showing results for 
Search instead for 
Did you mean: 

SSRS (on-prem) on Databricks SQL

-werners-
Esteemed Contributor III

Has anybody succeeded in querying Databricks SQL with an on-prem SSRS (so an on-prem Report Server and Report Builder)?

I manage to create a connection that works (according to the connection test anyway), but the moment I try to create a dataset on top of this connection, I get the error message saying the connection does not exist even though it is clearly there.

Connecting with DBeaver, PowerBI etc is no problem btw.

1 ACCEPTED SOLUTION

Accepted Solutions

-werners-
Esteemed Contributor III

OK, so I figured it out:

  • make sure you know the architecture (32/64 bit) of the report server, as your connection has to have the same value.
  • define the odbc connection on the report server AND also with the exact same name and properties locally on your laptop/desktop. This was my problem.

View solution in original post

6 REPLIES 6

-werners-
Esteemed Contributor III

OK, so I figured it out:

  • make sure you know the architecture (32/64 bit) of the report server, as your connection has to have the same value.
  • define the odbc connection on the report server AND also with the exact same name and properties locally on your laptop/desktop. This was my problem.

Haider93
New Contributor III

Hi @-werners- , 

I am able to build connection between Microsoft visual studio and data bricks using Simba Spark ODBC driver. I can query delta tables sitting in Databricks from Microsoft Visual studio (SSRS). However, when I am deploying the report to reporting service the report deploy successfully but upon opening the SSRS I can see the following error. "

  • An error has occurred during report processing. (rsProcessingAborted)
    • The execution failed for the shared data set 'sdtst_CG'. (rsDataSetExecutionError)
      • Cannot create a connection to data source ' Data source for shared dataset'. (rsErrorOpeningConnection)
        • ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified"

I have setup ODBC on server side and test the connection its successful but the connection is not flowing to SSRS for some reason. Can you please guide me on that. 

-werners-
Esteemed Contributor III

This seems like the problem I had.  Can you check if the connection serverside/local is EXACTLY the same?
bitness, name, properties etc.
In the web client of SSRS you can also check the connection, so like that you can make sure SSRS itself can connect (without using the local client).

Haider93
New Contributor III

Hi weners,
Thanks for your prompt response. I am still facing the issue. I have check the web client of SSRS and the connection is not successful. I think I am making mistake in connection string. Can you please help? I am using Dsn=eduschedargdevmanagedrgdbw01 (which is name of my user dsn located on my server where the SSRS is hosted.)

 

-werners-
Esteemed Contributor III

That is the correct connectionstring, however, I use a system dsn instead of user dsn.

Can you also check the credentials?  I use the first option (as the user viewing the report)

Haider93
New Contributor III

Hi @-werners-

I use the system dsn and uninstall 32 bit driver left with only 64 bit to get aligned with server architecture and its working perfectly fine. Thanks for your prompt response and help! Appreciate it  🙂 

Welcome to Databricks Community: Lets learn, network and celebrate together

Join our fast-growing data practitioner and expert community of 80K+ members, ready to discover, help and collaborate together while making meaningful connections. 

Click here to register and join today! 

Engage in exciting technical discussions, join a group with your peers and meet our Featured Members.