Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Can't connect to named instance (SP2) Expand / Collapse
Author
Message
Posted Thursday, November 4, 2010 3:32 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, December 22, 2011 4:54 AM
Points: 10, Visits: 179
Folks,

I Installed a new SQL Server 2008 cluster (active/passive) with SP2 and 1 named instance.

The database is running fine but when I want to connect from my client PC using SQL Server Management studio or OSQL (using this to caputure the error message) I get the following message:

[SQL Server Native Client 10.0]SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].
[SQL Server Native Client 10.0]Login timeout expired
[SQL Server Native Client 10.0]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.

The strange thing is, that If I use management server or OSQL to connect to the same server without the named instance, I get a connection to the named instance (probably cause it sees this instance as the default instance?, but correct me if I'm wrong here!) so I don't think it's a firewall/network issue.

Some info : Using port 1433 to connect (no dynamic ports). The SQL Server browser services is running (on both nodes) and the "Allow remote connections to this server" option is on. The client I use to connect to the server is a SQL Server 2008 client, also updated with SP2.

What am I doing wrong?

TIA

Dave
Post #1015748
Posted Thursday, November 4, 2010 4:07 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:00 AM
Points: 5,014, Visits: 10,513
Do you have a firewall between your pc and the cluster? If so, it's an old and well known issue.
SQL Browser is not cluster-aware. This means that it can receive request on UDP port 1434 on the virtual IP assigned to the cluster resource, but it can decide to send back the response from the physical IP address of the cluster node.
The firewall allows packets when it detects a datagram in response to a request, but it drops packets that are originated outside a "conversation". When SQLBrowser responds using the physical node IP address, the packet is not considered in response to the original UDP datagram, so it gets dropped.
To avoid the issue, add a rule to the firewall to allow UDP packets having the cluster node IP address as source and "ANY" as destination, port 1434.

Hope this helps
Gianluca


--
Gianluca Sartori

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #1015756
Posted Thursday, November 4, 2010 8:05 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, December 22, 2011 4:54 AM
Points: 10, Visits: 179
Thanks, yes, we have a firewall and yes there was a problem with the rules. We fill in sheets with the FROM and TO IPadresses and the ports that we need. The UDP 1434 was on there (also for the physical adresses) but the engineer that was working with it forgot or missed that entry....

The question that comes to mind with that is why I could connect to the database if didn't put the named instance in the connection string? Does it communicate differently? And what tool can I use best to see if the TCP and UDP ports of a machine are open?
Post #1015892
Posted Thursday, November 4, 2010 8:51 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:00 AM
Points: 5,014, Visits: 10,513
Dave D. (11/4/2010)
The question that comes to mind with that is why I could connect to the database if didn't put the named instance in the connection string? Does it communicate differently?

When you're connecting to the default instance, it uses TCP port 1433.
When you're connecting to a named instance, the port is unknown and has to be returned by SQLBrowser.

And what tool can I use best to see if the TCP and UDP ports of a machine are open?

You can use netstat to check listening ports on the server, but this is not sufficient.
To check if the server is available from the remote host, you can use portquery, that is shipped with a specific check for sql browser.


--
Gianluca Sartori

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #1015947
Posted Thursday, November 4, 2010 5:29 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, December 22, 2011 4:54 AM
Points: 10, Visits: 179
Gianluca Sartori (11/4/2010)
Dave D. (11/4/2010)
The question that comes to mind with that is why I could connect to the database if didn't put the named instance in the connection string? Does it communicate differently?

When you're connecting to the default instance, it uses TCP port 1433.
When you're connecting to a named instance, the port is unknown and has to be returned by SQLBrowser.

And what tool can I use best to see if the TCP and UDP ports of a machine are open?

You can use netstat to check listening ports on the server, but this is not sufficient.
To check if the server is available from the remote host, you can use portquery, that is shipped with a specific check for sql browser.


Thanks for the info!
Post #1016303
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse