Wednesday, April 25, 2012 6:04 PM
I have a package that I am creating. I am connecting from Windows Server "Alpha" to a completely different SQL Server box "Beta". I am using the SQL server name and everything is working between these two. In the same package I would like to connect from Windows Server "Alpha" to another SQL Server box "Gama". I keep everything the same but when I change the name of the Server in the Connection Manager I am not able to see any of the databases in the drop down menu. When I click on Test Connection it gives the follwing error:
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 .
"Beta" and "Gama" have the same setup including Username and Password. The ports between all three of these machines are open. I used one of the windows tools to test the connectivity between "Alpha" and "Gama" and was able to validate this connection. Everyone I have spoken with say's that this should be working. Anyone ever have a similar problem or know of a solution? Thanks a lot for any insite.
Wednesday, April 25, 2012 6:07 PMModerator
Wednesday, April 25, 2012 6:26 PM
From the command prompt I tried using ping [Ip address] but could not connect. I also tried telnet [Ip Address] [Port Number] and it could not connect. These servers are actually hosted in another location the guys servicing them told me that the ports where open. I was able to use Data Sources (ODBC) Windows Administrative Tool to create a connection and view all the databases in "Gama" from "Alpha" but I have not able to do the same in SSIS using the connection manager.
P.S. I just tried connecting from SSMS on "Alpha" to "Gama" and it failed. I thought I might be doing it wrong and then I tried to connect to "Beta" and it worked. Here is the error message I got:
TITLE: Connect to Server
Cannot connect to DB.
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) (Microsoft SQL Server, Error: 53)
I am going to try this link and see if there is anything there. Let you all know in a few...
- Edited by Peréz Wednesday, April 25, 2012 6:42 PM update
Tuesday, May 01, 2012 7:03 PM
So I just wanted to update that I never figured this one out. What I did do is a work around. Since I had the connection from "Alpha" to "Beta". I went back and changed all my queries to use a linked sever from "Beta" to "Gama". The end.
Wednesday, May 02, 2012 12:08 AMModerator
Wednesday, May 02, 2012 12:03 PM
Are you using SQL Login? check this article:
Also check if named pipes is enabled:
Lastly, check the Firewall Settings. It could be blocking the connections.
Randy Aldrich Paulo
MCTS(BizTalk 2010/2006,WCF NET4.0), MCPD | My Blog
BizTalk Message Archiving - SQL and File
Automating/Silent Installation of BizTalk Deployment Framework using Powershell >
Sending IDOCs using SSIS
- Edited by Randy Aldrich Paulo Wednesday, May 02, 2012 12:04 PM
Thursday, May 03, 2012 5:00 PM
Thank you All very much for your support and suggestions. So as I mentioned above these servers are hosted in another location and the guys that are supposed to know how to set all this up said everything was good to go. But anyways...
So no that I looked into it the solution was to add an alias from Alpha to Gama. I thought that the alias in the SQL Server Configuration Manager was for the current server but instead they server as a reference to other servers. WOW! Now I know. And knowing... is half the battle. haha Again I wouldn't have figured this out with you all. So thanks Much!!!
- Marked As Answer by Peréz Thursday, May 03, 2012 5:00 PM