topic SSRS (on-prem) on Databricks SQL in Data Engineering

SSRS (on-prem) on Databricks SQL

-werners- — Tue, 24 Jan 2023 07:51:54 GMT

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.

Re: SSRS (on-prem) on Databricks SQL

-werners- — Tue, 24 Jan 2023 14:26:20 GMT

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.

Re: SSRS (on-prem) on Databricks SQL

Haider93 — Mon, 07 Aug 2023 07:08:18 GMT

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.

Re: SSRS (on-prem) on Databricks SQL

-werners- — Mon, 07 Aug 2023 07:17:28 GMT

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).

Re: SSRS (on-prem) on Databricks SQL

Haider93 — Tue, 08 Aug 2023 06:15:11 GMT

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.)

Re: SSRS (on-prem) on Databricks SQL

-werners- — Tue, 08 Aug 2023 07:21:17 GMT

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)

Re: SSRS (on-prem) on Databricks SQL

Haider93 — Mon, 14 Aug 2023 01:52:14 GMT

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 🙂