sql-servervisual-studioreporting-servicesreport-designer

SSRS error "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."

I have a SQL Server Reporting Services(SSRS) report which works fine in Visual Studio 2015 report designer version 13.0,1100.286, but once deployed to a report server it keeps throwing the following error:

An error has occurred during report processing. (rsProcessingAborted) Cannot create a connection to data source 'DummyDataSource'. (rsErrorOpeningConnection) Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

When I attempt to test the connection of the data source in SQL Server Report Builder, I get the same error:

enter image description here

enter image description here

And here is how I set the credentials for my data source. Note that 'myUser' can connect to database 'MyDatabase' in SQL Server Management Studio, and in addition the report works fine in Visual Studio report designer with the same credentials:

enter image description here

I have tried setting the report timeout to 1800 (from initial setting of "Use the system default setting"), but that didn't solve the problem:

enter image description here

I also tried setting the timeout for the data set in the report to 30 and 60 seconds, also with no success. It seems it can't connect to the database at all, because it fails on "Test Connection", before I even attempt to run the report itself.

Any idea why that's happening?

Solution

  • So after much digging and trying everything I came upon while googling this, the answer was the following: recently our IT people added a ton of new IPs to the database server and when a report (on the report server) attempted to connect to the database, it was enumerating all those IPs and was trying to connect to all of them, which resulted in it failing to connect to the right one and hence, the error above.

    In order to address this, we added a new DNS entry, mapped solely to the IP address of the database and that finally fixed the issue.

    So to summarize, my old connection string (that was trying to connect to all IPs) was:

    Data Source=MyDatabase;Initial Catalog=DummyDataSource

    The new DNS entry is: sql.MyDatabase.CompanyName.com

    And finally, the new connection string that works fine is:

    Data Source=sql.MyDatabase.CompanyName.com;Initial Catalog=DummyDataSource