Paginated Report Error in the Service: data source was found but the credentials were incorrect

[AlexisOlson]

I'm having trouble when publishing a .rdl file to the service. It works fine in the Power BI Report Builder desktop app but displays this error when I try to open it in the online workspace (after a few seconds of the loading circle and the report parameters briefly visible in the background).

Could not connect to the data source.

Data for this report could not be fetched. The data source was found, but the credentials or identity used to connect to the data source were incorrect. Please update the credentials for this report before trying again.

The .rdl file has a couple of SQL query datasets and one stored procedure dataset from the SQL Server and a couple of DAX query datasets from a Power BI dataset (which lives in the same workspace and is ultimately sourced from a few Excel files saved in SharePoint). This is what the lineage looks like:

The FS Footnotes Model report works properly in the service and, again, the Financial Statement SOI report works properly in the desktop app but not in the service.

This is what the data settings look like:

This gateway is used extensively and successfully in other reporting and the Power BI dataset doesn't need a gateway since its data source is in the cloud (SharePoint). I have plenty of other paginated reports that do work in the service but I think all of them are built off of Power BI dataset(s) instead of a mix of SQL Server and a Power BI dataset.

What is going on and how can I resolve this error?

[v-angzheng-msft]

Hi, @AlexisOlson 

Is it possible that this is because the report comes from multiple data sources? That multiple data sources have multiple different credentials? Or different data sources have different connection mode?

I find some information about connection patterns here.

https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-directquery-datasets-azure-analysis-s...

Hope you  can find a solution so that we can learn from it.

Best Regards,
Community Support Team _ Zeon Zheng

[AlexisOlson]

I don't think it has to do with multiple credentials or connection modes since it works fine locally and also works when the stored procedure is replaced by the text that defines the stored procedure. I think I need to create a minimal working example to isolate exactly the sticking point though.

Edit: I tried using the same stored procedure as the source for a Power BI report and got an error when I attempted to refresh the dataset. It looks like the issue is still the execute permission. Gotta bug the DB admins again.

Data source error:
{"error":
    {"code":"DM_GWPipeline_Gateway_MashupDataAccessError","pbi.error":
        {"code":"DM_GWPipeline_Gateway_MashupDataAccessError","parameters":{},"details":
            [{"code":"DM_ErrorDetailNameCode_UnderlyingErrorCode","detail":{"type":1,"value":"-2147467259"}},
             {"code":"DM_ErrorDetailNameCode_UnderlyingErrorMessage","detail":{"type":1,
                "value":"Microsoft SQL: The EXECUTE permission was denied on the object 'pr_FC_SOITemplate_Institutional', database 'CWS', schema 'dbo'."}},
             {"code":"DM_ErrorDetailNameCode_UnderlyingHResult","detail":{"type":1,"value":"-2147467259"}},
             {"code":"Microsoft.Data.Mashup.ValueError.Class","detail":{"type":1,"value":"14"}},
             {"code":"Microsoft.Data.Mashup.ValueError.DataSourceKind","detail":{"type":1,"value":"SQL"}},
             {"code":"Microsoft.Data.Mashup.ValueError.DataSourcePath","detail":{"type":1,"value":"sqlcluster;CWS"}},
             {"code":"Microsoft.Data.Mashup.ValueError.ErrorCode","detail":{"type":1,"value":"-2146232060"}},
             {"code":"Microsoft.Data.Mashup.ValueError.Message","detail":{"type":1,
                "value":"The EXECUTE permission was denied on the object 'pr_FC_SOITemplate_Institutional', database 'CWS', schema 'dbo'."}},
             {"code":"Microsoft.Data.Mashup.ValueError.Number","detail":{"type":1,"value":"229"}},
             {"code":"Microsoft.Data.Mashup.ValueError.Reason","detail":{"type":1,"value":"DataSource.Error"}}],
             "exceptionCulprit":1}}}
Table: Query1.
Cluster URI: WABI-WEST-US-redirect.analysis.windows.net
Activity ID: 797ff6e4-6af8-43bf-ba0b-e1d9805b35e4
Request ID: 22850e85-4cf2-bdcf-adfa-018510ada49f
Time: 2021-07-16 16:15:52Z

[AlexisOlson]

I finally figured out at least part of the "why" question myself after trying a bunch of different things.

It turns out it was indeed a permissions issue. The gateway account had read permissions but not permission to execute a stored procedure on the SQL server. I discovered this by swapping out the stored procedure with an equivalent text query in the Report Builder. This eliminated the problem for me.

Adding execute permission on the database for the gateway account did not resolve the issue. I will update if I ever figure out how to get it to work with a stored procedure instead of a text query. This part of the question remains unresolved.

[pragya27]

Hello Alexis,

I have been encountering exactly this same issue.

Could you please let me know if you have found any solution for this?

Thanks

Regards,

Pragya S

[Luka]

To me it helped when I turned off check mark for : "Report viewers can only access this data source with their own Power BI identities"

Location of this setting: Workspace -> Report -> More options (3 dots) -> Manage -> Data source credentials -> edit credentials

Maybe helps someone.