SSRS Trouble Connecting to a SQL Server in Report Builder

Question

We have a new SSRS 2019 Server. I navigate to the SSRS Report Manager web page, click New and select a Paginated Report which opens Report Builder. I can create a Data Source connecting to another MS SQL Server 2016 database successfully - I'm embedding the data source not using a shared one. I can create a dataset (select * from table) and if I go into the query designer and click the red exclamation point I get results as expected. I add a table to the report, add some of the fields from the query and when I run it I get the error:

Cannot create a connection to the data source.

In the lower left of Report Builder it tells me what report server I'm currently connected to and if I disconnect THEN run it again it works fine.

Any suggestions? I'm positive it's something to do with our new report server but I'm confused why I can connect and run the query in the query designer but it won't connect just running the report. Thanks much!

Answer 1

The connection is working from the machine you are designing on, but not from the Report Server. When Report Builder is connected to the server, it tries running the report using the server version of the connection which is why you get this behavior.

If you look closely, there are two different Test Connection buttons. One in the Connection Properties and one in the Data Source Properties. One of these works, but the other won't (if you are connected to the Report Server).

So it comes down to standard connection troubleshooting on the server: check firewalls, drivers, etc.