I'm Trying to created a shared Data source from a sql server that is not local. the host of SSRS is a sql server 2014 and the DB source is a sql server 2008 R2.
I'm getting the same error whatever I do, which is "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)"
I know everything is correct because even though I get this error when I run the report, in MSSQL report builder when I create a dataset and go to query designer and run the query it actually runs without any issues.
furthermore also in report builder when I try to do a connection embedded in my report and when I'm in the connection builder (connection properties) and I test the connection from there it gives me "the test connection suceeded" however when I click ok, go back to the datasource properties, and click "test Connection" it fails and gives me the error above.
If I try to test a shared connection on the server it will fail too.
I've tried connecting in many different ways (using windows athentication (my user is admin on the server), SQL server authenticationm, promting, etc) but they all failed.
I've been at this for a while, the server is also accesed by a remote PHP server so I doubt there is any real issue on the SQL server. I've tried deactivating all of the firewalls I saw but I doubt it's a firewall issue since I"m able to do all these connections.
the issue was the port 1433 that didn't work between the SSRS server and the datasource server. however it did work between my computer and the sql server.
what was happening is that when I tested the connections, the first test was a quick test and went through my computer to test the connection thus it was sucessfull. when it did the real test it went through the SSRS server and it failed there.
the same logic applies when I did my requests, when I tested the dataset it worked because in report builder it went directly to the database through my computer, when I tried to run the report it went through the SSRS server and failed there.
so there is some issue with the firewall even though it is turned of on both servers it's the connection between the two that isn't working.