Speak 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 published), I found some errors when testing connectivity to the database through the Power BI Report Server, which I had installed a few minutes ago for this article, where when trying to return the data when I clicked the “Load data” button , in the “Data visualization” tab, presented the error as shown below:
Error message transcript:
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 I started to investigate the problem further. The first step is to review the Power BI Report Server logs, which are usually 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:
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 in “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, as it was empty:
One of the solutions 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. As an alternative, I opened the .NET Framework 2.0 machine.config, usually located in “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 Framework 2.0's machine.config and replaced those that were in Framework 4.0 (which were empty). After this change, I tried to run the “View Data” option on the Power BI Report Server again and the error continued to appear on the screen, but when analyzing the log, the message now changed:
The error message now indicates a permission problem when accessing the database “dirceuresende”, which the shared data set used. The connection problem was then solved and it was enough to create a user for this login in the target database and allow select access in the table used for the problem to be definitively solved:
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.
