SQL Server Reporting Services (SSRS) - Error Querying Data: There was a problem retrieving data from the Report Server Web service

Views: 642
Reading Time: 4 minutes

Hey guys!

During the creation of my article SQL Server Reporting Services (SSRS) - Which reports are most accessed? And which are not being used? (which I just posted), I found some errors while testing database connectivity through the Power BI Report Server, which I had installed a few minutes ago for this article, where when trying to return data when I clicked the “Load Data” button , in the tab “Data visualization”, presented the error as shown below:

Error message transcript:

An error has occurred.

Could not process data set.
There was a problem retrieving data from the Report Server web service.

I tested connectivity from my data source and it was working normally:

When I was testing for Report Builder, no problem either:

So he began to investigate the problem further. The first step is to analyze the Power BI Report Server logs, which are typically located in the “C: \ Program Files \ Microsoft Power BI Report Server \ PBIRS \ LogFiles” directory:

Analyzing the log file at the approximate time when I tried to view the data and the error was generated, I identified the error message below:

extensionfactory! ReportServer_0-3! 16c4! 12 / 22 / 2018-10: 46: 03 :: and ERROR: Exception caught instantiating ORACLE report server extension: System.Reflection.TargetInvocation of an exception has been thrown. -> System.ArgumentException: Unable to find the requested .Net Framework Data Provider. It may not be installed.
at System.Data.Common.DbProviderFactories.GetFactory (String providerInvariantName)
at Microsoft.ReportingServices.DataExtensions.OracleClientConnectionWrapper..ctor ()

It appeared to be some driver problem on the Reporting Services server itself. As the message talks about .Net Framework Data Provider, I went to confirm that the .NET Framework was installed and really was.

The next step was to parse the .NET Framework machine.config file to validate that the driver entries were correct. The file machine.config It is usually located at “C: \ Windows \ Microsoft.NET \ Framework \ v4.0.30319 \ Config” and when opening the file and checking the providers entries in System.Data, I identified that there was a problem there because it was empty:

One solution was to try to reinstall the Microsoft .NET Framework to see if the records would be generated in machine.config, but this solution would take too long. Alternatively, I opened the .NET Framework 2.0 machine.config, usually located at “C: \ Windows \ Microsoft.NET \ Framework \ v2.0.50727 \ CONFIG”, and went to check if the records were valid:

Beauty! Records are populated in the .NET Framework 2.0. I copied the System.Data records from the 2.0 Framework machine.config and replaces the ones that were in the 4.0 Framework (which were empty). After this change, I tried to run the “View Data” option again on the Power BI Report Server and the error continued to appear on the screen, but while parsing the log, the message now changed:

The error message now tells us a permission issue when accessing the “dirceuresende” database, which is the shared dataset used. The connection problem was then solved and it was enough to create a user for this login in the target database and to grant select access in the table used to solve the problem:

Don't know Power BI Report Server yet? Would you like to know more about this tool? Be sure to check out my article Video - Power BI vs. Reporting Services: Who's Better?.

That's it folks!
A big hug and even more!

Note: Everything shown in this article is for both Power BI Report Server and SQL Server Reporting Services (SSRS), as both use the same framework.