Sql-server – Reporting Services will not connect to SQL Server

connectivitysql serverssrs

I have set up a new SQL Server 2014 default instance. In SQL Server 2014 Configuration manager I see my 32 and 64 bit SQL Native Client 11.0 configurations and all protocols, pipes, tcp/ip,shared memory are enabled for them all. When I try to make a new data source to my SQL Server 2012 instance, everything is fine. When I try to make a connection to my SQL Server 2008 R2 server, I get the generic:

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)

I have used Microsoft SQL Server driver with connection strings:

  1. data source = "cha-sql-01"; initial catalog = Paymentbanc
  2. data source = 'cha-sql-01'; initial catalog = Paymentbanc
  3. data source = "cha-sql-01"; initial catalog = Paymentbanc;
  4. data source = 'cha-sql-01'; initial catalog = Paymentbanc;

I checked the SQL Server 2008 R2 server and all protocols were enabled. The browser was off, so I turned on the browser, made sure that sqlcmd -L saw the server, which it did, then tried and got the same results.

I then tried to connect via IP address:

  1. data source =x.x.x.x,1433;Initial Catalog=xxxxx;User ID=xxxxx;Password=xxxxxxx;
  2. Also tried a domain account

Next, I thought the issue was that SQL Server 11.0 would not be backwards compatible, so I loaded the SQL Client 10.0 client on the reporting server. I went into ODBC sources, added the client 10.0 and configured it for my SQL Server 2008 R2 server.

In ODBC administrator, I can use both SQL Server and SQL Server Native Client 10.0 to connect to the server. So, what am I missing to get the reporting service SQL Server client to connect? No errors other than the teradata & SQLPDW

This is a brand new server on Windows 2012 R2 running SQL Server Standard Edition 2014 SP1.

Best Answer

Community Wiki answer to document the solution added by the question author in a comment:

The old SQL Server was listening on a port other than 1433.

Once I used the IP connection string with the right port, all was well.

Related Question