Unable to connect to a SQL Server named instance on a cluster
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 clusterinst
[SQL Native Client]SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].
[SQL Native Client]Login timeout expired
For MDAC:
C:>osql -E -S clusterinst
[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.
https://support.microsoft.com/?kbid=888228
https://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
Comments
Anonymous
May 03, 2006
You said that this issue won't fix; so what do we do now (after search knowledge base for enabling TCP/IP port and some more)Anonymous
May 04, 2006
As I mentioned in the blog: "A workaround is to specify TCP port number in the connection string in which case we bypass the discovery process. "
Also, you can change your Firewall/IPSec settings so that this response UDP packet won't be filtered. E.g, put your client application under exception list of your firewall.
Thanks.
XinweiAnonymous
May 31, 2006
Would installing SQL2005 as a default instance (instead of a named instance) help avoid the problem? Since you can only have one named instance per virtual server (in a clustered environment), what is the advantage of a named instance over a default instance anyway?Anonymous
May 31, 2006
If it's default instance, client can connect the server using default port number (1433). So, it's not an issue unless you change the server TCP port.
For the other question, Yes. One named instance per virtual server. But, you can install multiple virtual servers on one physical machine, in which case, you would need named instances so that we can distinguish those instances on the physical machine.Anonymous
December 06, 2006
We do have the same phenomenon. When running osql on the cluster node, randomly the connection fails or succeeds. When doing this workaround to specify the port, than it works. Is this bug in SQL Browser still not fixed in the upcoming SQL2005 SP2 ?Anonymous
February 24, 2007
PingBack from http://chaespot.com/mssql/2007/02/24/sql-protocols-unable-to-connect-to-a-sql-server-named-instance-on-a/Anonymous
March 06, 2007
would this scenario also apply to multiple instances on a host, each bound to two VLANs on two NICS?Anonymous
March 07, 2007
Your scenario is different. Say you have IP1 on VLAN1 and IP2 on VLAN2. SQL Instance 1 listens on IP1, SQL Instance 2 listens on IP2. SQL Browser listens on IP ALL. You can only make a SQL connection to INST1 from VLAN1 and to INST2 from VLAN2. In this case, UDP packets input/output from the same IP address on the server, so no such issue. If you try to connect to INST1 from VLAN2 or INST2 from VLAN1, you will fail. But that's because of routing, not because of Firewall issue I described. Please let me know if your scenario is different than what I just assumed.Anonymous
March 08, 2007
thanks for the feedback - our setup is slightly more complicated : SQL 2005 - two instances listening on both vlans, and one sql 2000 instance. We were getting intermittent prelogin failures to the SQL 2005 instances, but usage is currently light so its hard to get accurate pictures. I changed the bindings on the SQL 2005 instances so that they listen on only one VLAN and we still get the issue. I'm planning to simplify further as the second VLAN was only required for internet access for service pack 1.. so I'll take that out of the equation, and hopefully that will cure the problem - otherwise I'll have to start looking at removing the sql2000 instance.Anonymous
March 09, 2007
The issue I mentioned in the blog only happens when you connect one IP but the return packet changed the server IP to another one. With two VLANs, networks are seperated by lower layer(link layer). IPs usually won't be messed up. Unless your client also attachs to both VLANs, you should not see the issue I mentioned. Also, if you do have this issue, you won't be able to connect your server at all (without the workaround). Since you see intermittent pre-login error, I believe your issue is different. What's the exact error message?Anonymous
April 26, 2007
removing the multiple vlans has cured the problem, thanks. exact error message was: The SQLBrowser service was unable to process a client request.Anonymous
May 13, 2007
Users often see this error message when connection to a SQL Server and don't know where to start to solveAnonymous
September 18, 2007
So is this a bug that will be corrected in a Service pack at some point? I am hoping it is not as I have the same issue in an active active cluster each with a named instance. Thanks, MikeAnonymous
September 18, 2007
You can consider this as a bug for us, but its origin is the nature of UDP. We hope to fix this in coming releases. For now, you can either use the workaround I mentioned, or define an exception between your server and client on your client Vista machine. Thanks.Anonymous
October 03, 2007
I'm trying to register a database engine (named instance of a cluster) in SQL Server Management Studio and I can't connect. I also can't connect if I drop down to osql. I've added exceptions to my firewall for ports and programs but still no luck. If I try to register or connect to the default instance of the cluster I can connect fine. It's only the named instance which I'm having problems with. It also only happens on one PC. I hate to refresh the PC if there's a workaround. Any ideas?Anonymous
October 03, 2007
Can you check my following blog about Connection Alias: http://blogs.msdn.com/sql_protocols/archive/2007/01/07/connection-alias.aspx Make sure you don't have an outdated entry there on the machine you have problem. Thanks.Anonymous
February 25, 2008
The comment has been removedAnonymous
February 25, 2008
Hi, Mark We have a fix for this in SQL Server 2008(will be released mid this year). If your server is SQL Server 2008 and the OS is Vista/Win2008, you will not see this issue anymore.Anonymous
February 25, 2008
If this has been a problem since SQL 2000, how come it's taken so long to get it fixed?Anonymous
March 25, 2008
I'm trying to connect to a clustered server (SQL 2000) with two named instances on two nodes in the cluster, and i have tried setting up sql alias entries for each, using both named pipes: \192.168.104.51pipe$$CACLMSDBINST1MSSQL$LMSINST1sqlquery and tcp 192.168.104.51,1461 Both of which i got from the server configuration. The IP address is the sql cluster ip address, not either named instance ip, which i believe is correct.. The other variable here is i am trying to connect through a VPN. Where should i start looking next as far as firewall rules on the other side of the vpn, etc?Anonymous
May 23, 2008
Does anyone see these type of login failures showing up on stand-alone servers with multiple instances of SQL 2005 and 2000 installed? The OS is win2000 sp2Anonymous
June 16, 2008
On a Windows Cluster it is possible to install a SQL Server Cluster, which will be a separate entityAnonymous
July 28, 2008
We have intermittent issues with connecting to named instances of Analysis Services on a cluster. So, does the initial situation described by Xinwei apply in this case? It seems that the situation described either always works or never works.Anonymous
July 29, 2008
dbrooks7, This blog does not applies your case. The discovery of Analysis Service is totally different. SQL Browser will load a dll which takes care of AS discovery. Thanks. XinweiAnonymous
August 28, 2008
Top things I always forget when logging into SQL Server:   1. SSL Self-Signed Cert Does Not WorkAnonymous
September 04, 2008
Have they come out with a Fix to this yet? I've prefer named instances over Default but i've run into this problem as well and opened ports, enabled remote connections, pinged, shared drives, folders... everything short of glueing the two servers together. It simply acts as if it doesn't want to connect to the named instance Have they come out with a Patch yet, or and upgrade/ fix?Anonymous
September 08, 2008
We had a fix in SQL Server 2008 for this issue. However, we found another issue and this issue makes the fixs does not work for x64 machine (server machine). So, if your server is SQL Server 2008 on Vista/Win2k8 on X86/IA64, you should have no problem connecting to your server even if you have firewall enabled on your client Vista/W2k8 machine. Thanks.Anonymous
September 24, 2008
I have also been stalled by Error:26 issue. The followings are my current installed applicaions and related info:
- OS: Vista Home Premium with SP1
- SQL Server 2005 SP1 and SQWL Server Management Studio Express edition.
- Web Developer 2008 Express. Symptom: When I tried to create a connection to SQL Server by clciking the mdf in Database Explorer of VWD, it displayed the Error 26. However, I can attach the mdfs in Management studio and access the file at all. Thank you in advance for any idea.
Anonymous
October 26, 2008
I am trying to connect from Server 2008x64 to Clustered SQL 2008x64 on Server 2008x64, (named instance) and I continually get the error 26. Have checked all the issues you list. When will the fix for Server 2008 x64 - Clustered SQL 2008 be available? Thanks,Anonymous
October 26, 2008
Nevermind. Firewall was "on", albeit showed up as red in control panel because wasn't configd with all of the recommened settings.Anonymous
November 09, 2008
I have been able to connect to Sql Express using the Data connection Wizard.But when I connect using code I have an error that says that remote connection is not possible error 40.In spite of doing all possible changes to the settings of SQL Config manager and firewall. Please help me fix this problem.Thanking you in advance.Anonymous
November 12, 2008
What's the connection string in your code? Do you run your code and Data connection Wizard under different account? Can you make sure the account under which you run your code has permission to at least open a fileshare to the target machine(if not local)? Thanks, XinweiAnonymous
November 26, 2008
we installed 2 virtual server on a cluster, each with 1 named instance but no DEFAULT instance. we set the port for both instances to 1433, do we still have to specify "server=ip,1433" in the connect string?Anonymous
December 04, 2008
If you have a clustered SQL Server 2000 or SQL Server 2005 you might have the following error message:Anonymous
December 22, 2008
The comment has been removedAnonymous
February 13, 2009
There have been a couple of issues that keep pestering the SQL Server community for those that use VistaAnonymous
April 20, 2009
We are about to move a very large number of databases from SQL2000 and SQL2005, on to a SQL2005 Cluster. As I am reading this article, named instance resolution on SQL2005 Clusters is going to break with Vista, and also break with any app or device that enforces strict UDP i.e. checks that the IP address of the reply matches the IP address of the original destination. This is an important test to apply because it screens off a lot of serious UDP attacks, including attacks that target SQL Server specifically. Xinwei, you are saying the problem originates in the UDP protocol but is it not the case that this could be fixed in the SQL Browser Service? Other services can listen on specific IPs and ports, is it that expensive to have the option to configure SQL Browser the same way? I also have another suggestion that you can implement which is much less expensive and makes the Browser service compliant with correct UDP checks. Have the Browser Service inspect the UDP packet header and check the destination address. If the destination address is an available direct attached interface and routable to the sender, have the Browser Service send the response packet with an origin address of this interface, via the interface. That will then pass strict UDP checking and contribute to everyone's security. As a bonus you could also have the Browser (optionally) discard packets that had a (forged) origin on a local interface or a (forged) destination that is not a local interface. We really need this fixed on 2005, not just 2008. To be honest we are avoiding Vista as it's a nightmare. But we still need our SQL2005 Cluster to work properly. Microsoft should make sure that all its supported products comply with security best practices. Sending UDP replies, with a different origin than they were originally sent to, does not comply. I hope you will be able to do something. Thanks very much to Xinwei and other contributors for all the excellent information here, it has helped us understand this problem.Anonymous
May 21, 2009
Please let me know in case there is any solution.Anonymous
May 28, 2009
I have 2-node SQL Server 2005 Cluster which configured as Active/Passive mode, let say A node and P node. I already install 1 CRM instance on this cluster before. Now I wanna to install a second 2-node cluster on these...Anonymous
June 03, 2009
Below is the error that SSIS is throwing when trying to connect to a NAMED SSAS instance on a windows CLUSTER A connection cannot be made to redirector. Ensure that 'SQL Browser' service is running.Anonymous
June 22, 2009
The comment has been removedAnonymous
July 08, 2009
The comment has been removedAnonymous
September 25, 2009
Linked Server from SQL Server 2005 (on cluster) and to SQL Server (web edition) 2008 - not on cluster. SQL 2008 has a serverdbInstanceName using SQL Server security (username & password) for a WebData database. I am trying enterprise manager from 2005 to connect to the 2005 db - and get SQL Network Interfaces, error: 28 - server doesn't support requested protocol Do I need to turn on a port? Is there a sequesnce to connect? e.g. 10.1.2.1dbInstanceAnonymous
September 25, 2009
Rx, It's error 26. Did you try the steps in the blog? http://www.bing.com/search?q=sql+protocol+error+26&form=QBLH&qs=n Thanks. XinweiAnonymous
October 26, 2009
I have strange issue where in I am unable to connect to SQL server over local LAN. However I do not have any issue a) if RDP to other server and try to connect from it. b) if I am on VPN then I do not have any issue. c) if I try to connect <IPAddress>,<Port> then I am able to connect over local LAN through desktop. Note:- IP Address of server has been changed recently and person who is in same subnet as me is able to connect to server without any issue. Any help on it would be appreciated.Anonymous
October 26, 2009
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.Anonymous
October 28, 2009
Hi i have the same issue sometime ago and i just start Sql Server Browser in services hope it works for anotherones of youAnonymous
November 11, 2009
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 NINJASQLEXPRESS. It is giving Error 26. TITLE: Connect to ServerAnonymous
November 25, 2009
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.Anonymous
December 21, 2009
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.Anonymous
January 05, 2010
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.Anonymous
January 06, 2010
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.Anonymous
January 12, 2010
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.Anonymous
January 12, 2010
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/Anonymous
January 26, 2010
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. JenieAnonymous
March 17, 2010
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???Anonymous
March 18, 2010
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, XinweiAnonymous
March 19, 2010
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.Anonymous
March 24, 2010
The comment has been removedAnonymous
March 25, 2010
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.Anonymous
April 13, 2010
Does this problem occur on when conecting from a client running Windows 2008 R2 to a Named Instance of SQL Server 2008 SP1 running on a Windows 2008 R2 Cluster? If so what is the fix?Anonymous
October 18, 2010
Might be it's a same old question for you but I can't understand where to start resolving this issue in my WPF application.Anonymous
December 22, 2010
If you use version 2008 SQLEXPRESS the port ist NOT the standard port 1433. If you use a port that is not 1433 you have to start SQL Browser service (manually) to let your sql server to be found in the network.Anonymous
November 09, 2011
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.Anonymous
July 25, 2012
I found this error when using SQL Management Studio 2008 and trying to connect to an SQL 2000 DB Instance on a SQL Cluster. If I use SQL Management Studio 2000 it works fine.Anonymous
June 01, 2015
The comment has been removed