Export to file for paginated report error- integrated security in the connection string

[bml123]

Hi,

I have created a flow that runs a paginated report and export into excel spreadsheet and saves to one-drive.

Save a paginated report to OneDrive for Business or SharePoint Online - Power BI | Microsoft Docs

But I am getting the below error at 'Export to file for paginated report' stage.

"You have specified integrated security in the connection string for the data source. Please remove it from the connection string and try uploading the report again."

The report is using SQL database and it's running fine on its own but failing in the flow with the above error.

Please can someone help with this.

[bml123]

Hi @v-rzhou-msft 

I have parameters in the paginated report.  I want to run the whole report without passing any values to the parameters.  I have tried all options but still getting the same error.  Any pointers?

[srduval]

The problem is with the pagainted report. The API is unable to authenticate the export using windows or microsoft account authentication. It appears to need to be mapped to a gateway or SQL authentication. 

@v-rzhou-msft is there anything you can do to get a resolution to this? passing a value through the identities placeholder doesn't work either. I still get the same error

[bml123]

hi @sdruval,

I am using SQL authentication but still not working.

[srduval · Accepted solution]

@bml123 

 Try changing the datasource settings and unchecking the "Report viewers can only access this data source with their own Power BI identities using DirectQuery." box in the "manage" menu option page. if you are using a SQL authentication, change authentication to be basic, then click sign in. 

[bml123]

Hi sdruval,

Thank you so much for this. You made my day. 

It's working now. Just can't believe that simple untick makes it working.

How did you know this? Thanks alot for this information.

Thank you very much. It saved us lot of time with this working again.

[srduval]

Just happened to stumble upon it. I've been trying to figure out why it's been doing this intermittently for awhile now, and never thought to look at the datasource settings until now. I have no idea why this isn't published in any of the microsoft documentation about using the API for either custom connection or the action item they provide.

[bml123]

That's a great finding. Thank you so much for sharing this.  You are a super star. 

[srduval]

Beware, however that this appears to be a global change within your org, I don't have another report writer on my side to confirm, but it appears when you turn that setting off, the report will access the SQL database as the report writer, and since the change is made in service, it applies to all reports that use that same SQL database. So report 1 that you are exporting via the API needs this security option turned off, however report 2 that is not part of the API export will now access the SQL database as the report writer (assuming you use microsoft authentication).

The users username is realized in the report rendering, just not the backend SQL. Meaning if you add a column and use the paginated report username function, it will show the logged in user, but if you put the username() function into your SQL script, it will return with the report writers username.  

[srduval]

I'll have to try it on my end and see what happens. I know the only time i've gotten this to work is if I assign the report to use a gateway. When it uses the gateway it runs as SQL authentication, so I just assumed SQL Auth in the actuall connection string would work.

[v-rzhou-msft]

Hi @bml123 ,

Did you specify parameters for the paginated report?

Find detailed descriptions of the parameters in the connector reference for the Power BI REST API.

I think Power Automate will trigger a Rest API call to export paginated reports from Power BI Service.

You may refer to offical blogs as below to learn more details.

For reference:

Export paginated report to file

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.