SSIS remote server connection issue (able to connect from SSMS) RRS feed

  • Question

  • Please see the below issue.

    We have 2 SQL Servers in our production. They are on different domains. Both of them have tcp-ip & np enabled. The SSIS packages are on Server2 and need to pull the data from Server1 to Server2.

    Server1 is configured to port 1433 while the Server2 is configured to use port 65333.

    I am able to connect from Server2 to Server1 through SSMS using the IP Address (using sql login) and can query thhe tables on Server1. However, I am not able to connect from Server2 to Server1 from my SSIS package and so cannot bring in the data. The SSIS package uses "Native OLEDB\SQL Server Native Client 10.0 provider" and the connection manager uses the same exact sql credential i used for connecting from SSMS. Below is the error message, when i test the connection from package.

    Named Pipes Provider: Could not open a connection to SQL Server [5] . login timeout expired. An error has occurred while establishing a connection to the server. When connecting to SQL Server , this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. 

    I am assuming that it is not a firewall / port issue since i am able to connect tru SSMS. Or, do i need to add specific port for Integration Service to Firewall exceptions?

    What else could be the issue here? Any ideas? Thanks in advance!

    Wednesday, March 30, 2011 7:17 AM