locked
Cannot Crete Client ODBC Connections to named instances RRS feed

  • Question

  • Hi,

    For years my SQL Server has been up and running fine. It's 2005 Ent. with about 10 instances on it. Within the last week we have noticed the following and can't work out why.

    We have always been able to create odbc connections to any named instance perfectly ok. Now all we can connect to is the default instance.

    If we turn the dynamic ports option off you can type whatever instance you like but only get connected to the default one.

    The SQL Browser is started and working fine.

    I think the problem is with the windows firewall.

    If I take a client (by the way all client machines XP, Vista or 7 have the same error) and turn the firewall off, connect to SQL\Instance1 everything works fine.

    Turn the firewall on again and i can connect to SQL\Instance1 still!! But cannot get to SQL\Instance2-10

    Microsoft SQL Server ODBC Driver Version 06.01.7600

    Running connectivity tests...

    Attempting connection
    [Microsoft][ODBC SQL Server Driver][Named Pipes]SQL Server does not exist or access denied.

    TESTS FAILED!

    Hope someone has a bright idea!

    Thanks in advance,

    Alex.


    Monday, August 17, 2009 8:14 AM

Answers

  • Hi Alex

    It sure sounds like a firewall issue.
    The default instanct will always listen on port 1433, so that is probably allowed in the firewall setting.

    If you are not using dynamic ports, then you should know what ports to allow through the firewall since you have to specifiy the port.
    If you are using dynamic ports, then check what port the instance is listening on and allow that through the firewall.
    You can check what port is used for an instance in the SQL Server Configuration Manager, then select properties for the TCP/IP protocol.

    Once you have the port you can try to connect using ip,port instead of instance name to determine if SQL Browser is causing this or not.
    Ie. in the servername text box for the DSN, type 157.xxx.xxx.xxx,1234

    //Michael


    This posting is provided "AS IS" with no warranties.
    Monday, August 17, 2009 10:40 AM
  • Do you have multiple IP address on your server, i.e multiple-home? Can you check if this blog post applies?
    http://blogs.msdn.com/sql_protocols/archive/2006/02/27/unable-to-connect-to-a-sql-server-named-instance-on-a-cluster.aspx

    We only see the issue if the client machine is Vista or higher.

    One clarification, by turning off firewall, you mean turning off firewall on client machine right? If you mean server, then everything you see is expected.

    More info about your server/client machine is helpful. Thanks.
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, August 18, 2009 6:43 PM

All replies

  • Hi Alex

    It sure sounds like a firewall issue.
    The default instanct will always listen on port 1433, so that is probably allowed in the firewall setting.

    If you are not using dynamic ports, then you should know what ports to allow through the firewall since you have to specifiy the port.
    If you are using dynamic ports, then check what port the instance is listening on and allow that through the firewall.
    You can check what port is used for an instance in the SQL Server Configuration Manager, then select properties for the TCP/IP protocol.

    Once you have the port you can try to connect using ip,port instead of instance name to determine if SQL Browser is causing this or not.
    Ie. in the servername text box for the DSN, type 157.xxx.xxx.xxx,1234

    //Michael


    This posting is provided "AS IS" with no warranties.
    Monday, August 17, 2009 10:40 AM
  • Do you have multiple IP address on your server, i.e multiple-home? Can you check if this blog post applies?
    http://blogs.msdn.com/sql_protocols/archive/2006/02/27/unable-to-connect-to-a-sql-server-named-instance-on-a-cluster.aspx

    We only see the issue if the client machine is Vista or higher.

    One clarification, by turning off firewall, you mean turning off firewall on client machine right? If you mean server, then everything you see is expected.

    More info about your server/client machine is helpful. Thanks.
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, August 18, 2009 6:43 PM