Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Problems with Connection Manager

Answered Problems with Connection Manager

  • Wednesday, April 25, 2012 6:04 PM
     
     
    Sign In to Vote
    0

    Hi All,

    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 [53].

    "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.

    Pérez

     

All Replies

  • Wednesday, April 25, 2012 6:07 PM
    Moderator
     
     
    Sign In to Vote
    0

    Doesn't sound like an ssis problem...

    Can you ping the server? Can you connect to the server with SSMS?

    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

     
  • Wednesday, April 25, 2012 6:26 PM
     
     
    Sign In to Vote
    0

    Hi Joost,

    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érez

    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.

    ------------------------------
    ADDITIONAL INFORMATION:

    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)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=53&LinkId=20476

    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
     
     
    Sign In to Vote
    0

    Hi All,

    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.

    Pérez

     
  • Wednesday, May 02, 2012 12:08 AM
    Moderator
     
     
    Sign In to Vote
    0
    Not a nice story, but it is a connectivity issue on the Gamma end.  If you want to follow this up, you might want to post in the Data Connectivity forum for SQL.

    Todd McDermid's Blog Talk to me now on

     
  • Wednesday, May 02, 2012 12:03 PM
     
     
    Sign In to Vote
    0

    Are you using SQL Login? check this article:

    http://randypaulo.wordpress.com/2011/08/19/sql-login-not-working-in-sql-2008r22005/

    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


     
  • Thursday, May 03, 2012 5:00 PM
     
     Answered
    Sign In to Vote
    0

    Hi All,

    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!!!

    Pérez

    • Marked As Answer by Peréz Thursday, May 03, 2012 5:00 PM
    •