Good Morning Friends,

I hope i will get some answer or some directions here . I have been trying to find a solution for months. I have two servers TESTSQL(2008R2) PRODSQL(2008R2) . On these twos servers i have a package that loads data from source databases into the staging tables in the data warehouse. The source servers were 2005. The packages were running great with no problem. They have upgraded the source servers to 2008r2 and now the package on the PRODSQL(2008R2) is not working. I'm getting the connection manager connection error. we have checked all the possible places (like nameof the instance, firewall -- all the servers are internal servers). The package on TESTSQL is able to connect to the source servers and this is how we have been running the package for the time being. Please see the below error. Any documentatiuon or link will be of great help.

TITLE: Connection Manager

------------------------------

Test connection failed because of an error in initializing provider. Login timeout expired

A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.

Named Pipes Provider: Could not open a connection to SQL Server [53].

------------------------------

BUTTONS:

OK

------------------------------

1. Did you check whether the NamedPipe is Enabled ?
2. SQL Browser Service is running ?
Or
Did you try with TCP/IP instead of NamedPipe ?

Thanks Buddy. I tried it many times , restarted the services. No luck :-(

The Source and Destination in same server/Instance and different database ?

Or

1. The Source and Destination are  different server/Instance ? (If so) The Both servers under same Domain?

2. Can you able connect with same and destination using management studio successfully ?

3. Did you check the Remote connection enabled on sql servers ?

http://www.sqlmusings.com/2009/03/11/resolving-a-network-related-or-instance-specific-error-occurred-while-establishing-a-connection-to-sql-server/

Thanks Buddy.

From the test server i can connect to the source server and database. Its the same instance and the same database.

Yes both the servers are under the same domain.

Yes i can connect using SSMS from my desktop, but when i try to connect  using SSMS from the PRODSERVER i cannot and get the same error message.

Yes the remote connection is enabled.

• Edited by thinkingeye Wednesday, February 04, 2015 5:54 PM d

Are you using connection string? Can you please post here after removing server names....

I hope you are not connecting server via VPN. One thing that I will try is to compare connections on both servers as that is where you could find difference.

Thanks Vishal. No i'm noit using the connection string. Where should i look for the differences between the two servers in connecting to the Source Server? How will i know if i'm using the connection string?

So you saying that you get error message when you connect to Source server from production server.

Lets try one thing...Can you please connect to source server using ODBC data source and see if you can connect. Steps to create ODBC data source here.

Hi thinkingeye,

Based on my research, this 503 error usually means that the SQL Server computer can't be found or that the TCP port number is either not known, or is not the correct port number, or is blocked by a firewall.

According to your description, it seems that the issue is caused by you cannot ping the IP Address of the source server in the command prompt window of PRODSQL server or the source SQL Server is blocked by the firewall in the PRODSQL server.

For more details about how to fix this issue, please refer to the following blog:
http://social.technet.microsoft.com/wiki/contents/articles/2102.how-to-troubleshoot-connecting-to-the-sql-server-database-engine.aspx

Thanks,
Katherine Xiong