Cant connect the datasource SQL Server Database in the report server

[Anonymous]

Hello Community,

I created a report in the power bi desktop version and now wanted to upload the report on the report server which all worked out(once i had saved the file as a report server). I however can't seem to connect to the SQL server database as my one and only data source and can therefore not set up an automatic refresh of the report.

Just some back info the report is based on an Import mods and I first created the report on the desktop version before saving the document as power bi desktop report sever document. I think the problem might be with the fact that I didn't originally create the report in the report server version of desktop and/or that there is something wrong with my connecting string(was automatically filled in and I cant edit it). On the desktop version I can refresh the data without any problem as I am connected to the SQL database but it simply doesn't work with the report server.I've attached pictures showing my error sign to give more context. I also tried the two authentication methods possible (standard authentication and Microsoft authentication and both didn't work)

I would be very grateful for any assistance the community could give me,as I need to present this tomorrow to my bosses and cant find a solution.

Kind regards,

Philip Sinyan 

[Geo2512]

Hi @Anonymous , just checking if you found any solution to this. I have a similar issue. I too have created a report on Power Bi Desktop and uploaded the report to Power Bi Report Server. My report has connections to 2 different SQL servers. I had both the SQL connections live and working on Power Bi report Server and had automatic refresh activated. Suddenly, from this morning, one of the SQL Servers would not connect to the Power Bi Report Server. I have tried all sorts of workarounds- but still not working! Very frustrating!

[josef78]

Hi,

if you created report in Power BI Desktop RS aka "optimized for Power BI Report Server" (not cloud only version), it is ok. It is only way how to create report for Report Server.

If you are connecting to Azure SQL Database, check if you have defined proper firewall rules. SQL server must be accessible from Power BI Report server, and Power BI Report Server can be probably in different network with different IP than your desktop.

Good way to check is install Power BI Desktop RS to same machine where you are running Power BI Report Server, and check connection PBI Desktop RS from this machine.

[Anonymous]

Hi @josef78 Thank you soo much for your reply. How can I see the IP Address of the report server ? also maybe for reference I don't have admin rights at my company so should I maybe contact the It admin at my office ? thank you again for the help 

[josef78]

If you have simple single server deployment, IP address of report server is same as IP address report server portal (you can use nslookup with URL of your server). 

But if you have more robust scale-out deploymet, you must ask to some administrator (because there can be some load balancer, or multiple report servers which not all them have role for refresh data)

[v-deddai1-msft]

Hi @Anonymous ,

The ip address of report server is the ip address of the server that holds the report server.

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

Best Regards,

Dedmon Dai

[v-deddai1-msft]

Hi @Anonymous ,

Based on my test, it works well when I configure refresh for report created by normal desktop. It seems to be error with sql server.

Please try to enable all protocols in sscm(restart the sql server):

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

Best Regards,

Dedmon Dai

[lbendlin]

So you recreated the report in the report server specific version of Power BI Desktop and you still have the same issue?

Where is that error message coming from? Power BI Desktop RS or the Report Server admin page for the dataset?

[Anonymous]

Hi @lbendlin thanx so much for your reply. I first created the report in the desktop version (so normal desktop not report server specific version of Power bi desktop), then i saved the document in the report in the server specific version of Power BI Desktop (save as report server) and uploaded it to the report server.

The error is coming the Report Server admin page for the dataset.

Do you think the problem lies in the fact that I didn't originally create the report in the report server specific version of Power BI Desktop?

Thank you again for the reply