SQL Server 2008R2 SSRS Reports Cannot Connect To Datasource: Cannot create a connection to data source

Question

We're attempting to deploy an existing SQL Server/SSRS configuration to a new set of hardware. SSRS is running on the same server as the SQL Server database we're trying to get reports from.

Whenever we attempt to test any of the reports, we receive the error:

An error has occurred during report processing. (rsProcessingAborted) Cannot create a connection to data source 'sqlConnection'. (rsErrorOpeningConnection) A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server

• The Windows user has full rights to the server.
• The logins all appear to exist.
• TCP is running.
• Named instances are fine.
• SQL Browser Service is running
• "sqlcmd -L" shows the database server listed

I'm at a complete loss.

Solution 1

Create a udl file, if it connects then the problem is the code / application, if it does not connect, then it's your firewall, connections string, dll library, service you are running IIS under not having the right permissions etc etc. Well the important thing here is probably the connection string. Do the following: create an empty text file and rename it "myconnection.udl". Now double click on the file and it will launch an applet. You can configuer the connection to your database and test it. (it will pick up registered connection libraries etc). If it gives OK, then open the udl file in notepad, you will see the correct connection string. Paste to your app connection settings. UDL files are generally misunderstood. They are simply a text file that holds the connection settings. They then call the connection dll. If the udl file works then you have a correct connection string 100%

Solution 2

We created a new report and deployed it and it just worked. After some investigation, the report that I had been asked to use as a test initially has a parameter that defaults to connect to a completely different SQL Server which the new environment cannot, and should not see. All the other reports are working.

My guess is that we actually corrected the problem early on when we modified the service credentials, but by then we had fallen into a loop of testing against what turned out to be a bugged report.

TLDR: There was a bug in the report we were testing against. We had already corrected the problem and didn't even realize it because, apparently, we're idiots.