We have built Power BI Report by connecting to SQL Server with Direct Query mode. When we published to Report Server, the report broke and it is not working.
We tried to change the datasources settings, but it is greyed out and not allowing us to do anything.
P.S: We know that Power BI supports Direct Query by connecting to SQL Server with October Report server version.
Please let us know if there is an bug with current release. Request your assistance.
I could publish a Power BI report with SQL Server Direct Query to Report Server. I worked fine on web portal. In your scenario, did you get any error when publishing or when opening report on portal? Please try to upload report directly from web portal to see whether it works. Also, does this problem occur to other Power BI reports with different data source?
Thanks for the response. It is failing with the below error in report server. Tried uploading the PBI file directly in the report server but no luck. The report is working fine in the power bi desktop. Everytime we open the report in Desktop, it is asking for us to validate or continue and then it starts rendering the visuals. I hope this isn't an issue.
And also we could see under data source settings, persist security info=False but when we tried to change it, the data source setiings option is greyed out in report server.
Please let me know your thoughts on this.
Are you using logged in user or a fixed user for the direct query?
If it is set "As the user viewing the report" you have to make sure that it works to pass the credentials.
Have you tried test the connection on the manage screen? And if so does it work there. If not then you might get a better error message there.
If you get this message:
The report server couldn't connect to the data source using the information you entered. Make sure you've entered the connection string and any credentials correctly.
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
Then it means you are not passing credentials correctly. I get this problem in Chrome on my computer but Edge and IE works.
We are using logged in user for the direct query. And once we publish it to report server, we don't have option to impersonate the user. The entire data sources tab under Manage Report is greyed out.