I’ve seen this following scenario come across a few times. When trying to run a Scheduled Data Refresh, you may see an error similar to the following:
“Refreshing the data failed”
Within the Power BI Admin Center, if you go to System Health, you will see an error listed there.
Hovering over the message, we see something similar to:
Failed to find a match for the data source (connection string: provider=SQLOLEDB.1;integrated security=SSPI;persist security info=True;data source=section31;use procedure for prepare=1;auto translate=True;packet size=4096;workstation id=SECTION31;use encryption for data=False;tag with column collation when possible=False;initial catalog=ReportServer) for the user 'firstname.lastname@example.org'. The user is unauthorized or, the corresponding data source is not created. Check the user's permission to the data source or create a data source for the connection string. Error code: 4004
When we go to the Data Sources in the Admin Center, we see one Data Source listed.
This data source is configured to use the .NET Provider for SQL Server.
When we do the Scheduled Data Refresh operation, we use the connection string within the workbook, to match against a configured Data Source. In the example above, the workbook was using the SQL Server OLEDB Provider that ships with Windows. Our data source was configured for the .NET Provider, so we couldn’t find a match and therefore failed.
To resolve this, we can another Data Source that uses the SQLOLEDB Provider.
Once that data source is configured, the refresh will start working at that point, as it finds a successful match.
Adam W. Saxton | Microsoft SQL Server Escalation Services