Unable to connect to a SQL Server named instance on a cluster

Unable to connect to a SQL Server named instance on a cluster

Rate This
  • Comments 65

We have been seeing several occurrences where users failed to connect to SQL Server named instance on cluster. The error messages are usually as follow:

 

For SNAC:

C:\>osql -E -S cluster\inst

[SQL Native Client]SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].

[SQL Native Client]Login timeout expired

For MDAC:

C:\>osql -E -S cluster\inst

[DBNETLIB]Specified SQL server not found.

[DBNETLIB]ConnectionOpen (Connect()).

 

In all occurrences, customers already tried some basic steps for solving similar issues, such as, enabling TCP protocol, enabling remote connection, put SQL Server named instance into firewall exception list, etc. After we tracked down to the issues, we found that it's a combination of the specifics of Windows Cluster and the way we discover SQL Server named instance. When connecting to SQL Server named instances, our client components rely on SQL Browser to discover the server and its parameters. The discovery process is:

 

The client sends a UDP packet to SQL Browser on the target machine. When the named instance is on a windows cluster, the packet is sent to the cluster IP (or more specifically, the IP address corresponding to the virtual SQL Server). However, SQL Browser is not cluster-aware and listens on IP ANY. When SQL Browser receives the UDP request packet, it sends a response UDP packet back the client. The destination IP address is the client's IP address, however, the source IP address is changed. It's now the IP address for the NIC card on the physical machine, rather than the virtual SQL Server IP address. The source IP address of the response UDP packet is determined by Windows OS, based on the routing table. Because both virtual SQL Server IP address and the IP address attached to physical NIC are usually on the same subnet (thus belong to same route), physical IP address is selected preferably. Depends on the security settings on the client and server machines, this response UDP packet may be dropped because the peer IP address is changed. We have been seeing that the response packet is dropped by Firewall and/or IPSec.

 

Windows Firewall does not drop the packet. However, a third-party firewall may drop the packet. In addition, IPSec may also drop the packet if IPSec policy is enabled on the client and it can not establish a trust connection between the client and server. (Important update: If your client is a Vista machine, you will see this issue. A workaround is to specify tcp port or pipe name in your connection string directly.)

 

We decided not to fix this minor issue because it is determined by the nature of UDP protocol. A UDP socket can response to multiple senders and the socket layer never knows which one it is actually replying to. We may consider letting SQL Browser listen on individual IPs but the cost will be high. A workaround is to specify TCP port number in the connection string in which case we bypass the discovery process.

 

Please refer to the following links for additional information. The articles talk about issue for SQL Server 2000, but it also applies to SQL Server 2005 as the fundamentals did not change.

http://support.microsoft.com/?kbid=888228

http://support.microsoft.com/default.aspx?scid=kb;[LN];318432

 

*********Important update 2: regarding SQL Server 2008. ***************

We had a fix for this issue in SQL Server 2008. Unfortunately, the fix is still partial. We identify another issue which invalidate the fix on X64 machine. Other than that, the issue is fixed if the server is SQL Server 2008 on Vista/Windows Server 2008 on X86/IA64. We don't have to do anything on the client side for these scenario. Note: the issue still applies to all version of SQL Server 2005.

 

Update3: (Mar/2009)

If you upgrade your OS to Vista SP2 or Windows Server 2008 SP2, and your SQL Server 2008 is SP1, the partial issue on X64 is fixed. Meanwhile, we identified another related issue which affect the ability to enumrate SQL instances on Vista/Windows 2008 on network . The fix is also in SQL Server 2008 SP1.

 

 

 

Xinwei Hong, SQL Server Protocols
Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights

Leave a Comment
  • Please add 1 and 6 and type the answer here:
  • Post
  • Hi Yogesh,

     Rather than leaving comments on this blog with questions, please post questions on the MSDN SQL Server Data Access Forum: http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/threads. When you start a thread there, please include the full, exact error message that appears when you fail to connect.

  • Hi i have the same issue sometime ago and i just start Sql Server Browser in services

    hope it works for anotherones of you

  • Hi,

    My installattion is on my Local Machine.

    I reinstalled SQL Server 2005 and am unable to connect to the server.

    I am using Windows authentication Mode. My computer Name is NINJA.

    So I used the server name as NINJA\SQLEXPRESS.

    It is giving Error 26.

    TITLE: Connect to Server

  • The problem for us was the binding order on the NIC's as we had recently enabled new ones. Correct the binding order in network connections then reboot.

  • hi! i have just installed SQL Server 2005 Management Studio. When I open the program, I am first prompted to type the server name, along with the authentication and the database name. I use my PC name as the server name. I do not know what instance name to put. Does anyone here know how I could connect to server so I can create a database? Help please. Thanks.

  • It is a very interested article. Thanks.

    Would not be an easier solution to add an alias on the client side so Connection Strings do not change and not need to specify IP address?

    Your comments are strongly appreciated.

  • Noravia,

    Using alias could be a workaround. The drawback of alias is that it has to be defined on all client machines(if you have multiple clients) and you need to update it when the server side configuration changes. Also, it's easy to forget to delete the alias when the server is uninstalled.

  • On one PC I could not find any firewall software running and I had to flatten and re-load the operating system.  This fixed my problem.

    On another PC I was able to alter the firewall software to allow UDP on all ports both incoming and outgoing for my application.  This solved the same error message 26 on that PC.

    I do not know whether on a connection string it is possible to use ODBC.  I only had the Error 26 when connecting from Visual Studio and C# applications.  And only on specific PCs.  MS Access linked tables always worked from all PCs to the SQL Cluster.  Also SQL Server Management Studio was always able to connect without altering the firewall settings.  If it is possible to use ODBC in a visual studio application connection string, please let me know.

    THANK YOU.

  • Please try this link see if it solve your issue:

    http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/793fa3e2-3852-4450-b37e-83ec673388dd/

  • Thank you.

    I have the same issue when connect SQL 2005 name instance from SQL 2008. Using the IP address solve the issue.

    Great post.

    Jenie

  • Iam using EnumAvailableServers(Boolean) API of SMO to enumerate sql instances but it is returning zero list sql 2008 sp1 cluster on windows 2008 sp2.  But this API is working absolutely fine for non cluster sql 2008 sp1 on win2008 sp2...

    What happened to cluster?????? Is this bug didnt fix in cluster???

  • Prath,

    We are not aware of such an issue, and I don't expect such an issue. But we will try repro locally to see if that's true. Also, please note that the enumeration is not guaranteed to return all servers in the network.

    Thanks,

    Xinwei

  • Microsoft SQL Server 2005 - 9.00.4035.00 (X64) Enterprise Edition (64-bit)

    Windows NT 5.2 (Build 3790: Service Pack 2)

    2 Node Cluster which has 2 Instances of SQL Server 2005 AND 2 Instance of SQL Server 2008

    BROWSER.EXE is 2007.100.2531.0

    When instance is failed over to 1 node port resolution fails, I am able to connect if i specify the

    port number in the connection string. When the instances are on the other node it works fine.

    In the Application log i see this error : The SQLBrowser service was unable to process a client request

    When we start browser service from console we get the below output

    SQLBrowser: starting up in console mode

    SQLBrowser: starting up SSRP redirection service

    SQLBrowser is successfully listening on 0.0.0.0[1434]

    [5172]: Waiting for next request...

    [1752]: Waiting for next request...

    [6096]: Waiting for next request...

    [5792]: Waiting for next request...

    [5172]: Received request...

    [1752]: Received request...

    [5172]: Waiting for next request...

    [1752]: Waiting for next request...

    SQLBrowser: starting up OLAP redirection service

    [5172]: Received request...

    CLNT_UCAST_INST w/refresh from 192.168.10.11[3370]

    CLNT_UCAST_INST from 192.168.10.75[3370]

    [5172]: Waiting for next request...

    [1752]: Received request...

    CLNT_UCAST_INST w/refresh from 192.168.10.11[3371]

    CLNT_UCAST_INST from 192.168.10.11[3371]

    [1752]: Waiting for next request...

    Windows Firewall and Antivirus has been stopped and disabled. Restarting browser did not help.

    Any idea if i am hitting the same issue that has been explained above. On the other node even

    if windows firewall and antivirus is running i dont face this issue.

  • Thanks for the information in this Blog. It has allready helped.

    Description of our enviroment.

    A central phone provider has 300 customers that will recieve phone traffic, in a closed MPLS enviroment. But some of the service is User management that is done on a central MS-SQL cluster.

    The cluster is behind a firewall. The firewall will make NAT of the Cluster VIP IP , and make it into a RIPE addres. The customer will use a Computer, and access the application with a SQL client connection. The client computer has it's IP address changed in the Customers firewall. The traffic is between to different domains, with no trust.

    When reading this blog, we made a standalone SQL server, with a default DB installation. And it works. Thanks :-)

    NOW - I need to make a cluster SQL database due to high availibility SLA. The SQL Cluster database need to have not one but 3 different DB installed. How the hell do I do this?

    Suggestions accepted with smiles.

  • Tyge,

    Your configuration looks complicated. But, if standalone SQL Server works, cluster should also works. You can use IP address directly to isolate if it's a SQL Browser related issue. Please post question in our forum:

    http://social.msdn.microsoft.com/forums/en-US/sqldataaccess/threads/

    Thanks.

Page 4 of 5 (65 items) 12345